Hi, ALL,

I did successfully abe to run the ATTACH query from y previous thread.

But now I'm trying to do the following:

        std::wstring query1 = L"SELECT name FROM ";
        query1 += schema;
        query1 += L".sqlite_master WHERE type = 'table' OR type = 'view';";

Running this query fails with "Incorrect SQL".

As an example, the line becomes:

SELECT name FROM draft.sqlite_master WHERE type = 'table' OR type = 'view';

Could someone please explain what am I doing wrong?

TIA!!

Are you sure you're displaying the erroneous query properly, as that query works OK for me.

Your statement is too fat. You need to put each character on a diet.

@Keith,

I'm using following statement:

    if( ( res = sqlite3_prepare_v2( m_db, sqlite_pimpl->m_myconv.to_bytes( query1.c_str() ).c_str(), (int) query1.length(), &stmt, 0 ) ) == SQLITE_OK )

where m_myconv declared as:

std::wstring_convert<std::codecvt_utf8<wchar_t> > m_myconv;

I think that qualifies it as "putting on a diet".

Thank you.

What is the error that is returned by prepare?

"incorrect SQL" it most assuredly is not.

@Keith,

sqlite3_errcode() returns 1. And my program translated it as "Incorrect SQL". The prepare call is also return 1.

Thank you.

P.S.: As you probably guessed, my sequence was:

ATTACH <second_db_file>.db AS draft;
SELECT names FROM draft.sqlite_master...;
//sqlite3_errcode() if failed

While the first succeeded, the second did not.

Thank you.

Instead of doing your own interpretation, why do you not report the error message that is the result of the error?

Are you actually attaching the database? (prepare is insufficient, you need to step the VDBE program to completion also (until sqlite3_step return SQLITE_DONE). If something other than SQLITE_DONE is returned when you step the prepared ATTACH statement, then the attach was not done.

@Keith,

I'm running "sqlite3_exec()" for the "ATTACH..." command.

And since it was successful, I tried to get the table names from the attached DB.

Something like:

if( res = sqlite3_exec( m_db, "ATTACH...", ..... ) == SQLITE_OK )
{
    if( res = sqlite3_prepare_v2( m_db, "SELECT ...", ... ) == SQLITE_OK )
    {
    }
}

The first condition was successful (res value was SQLITE_OK). The second was not.

After executing the second "if()" statement, that res variable will have the value of 1.

An attached DB file name comes from the GUI and contain spaces. It looks like:

    c:\myprogram\my attached db.db

Thank you.

So, what is the error message (there is an API for that). Not what you think the error is, but the actual error that was detected?

As you say, result code 1 exists, and signifies a generic undefined problem. It is not helpful in this situation.

Would you be able to tell us the extended result code ?

https://www.sqlite.org/c3ref/extended_result_codes.html

Or the actual error message?

Hint: There is an API for that. See the API list here: https://sqlite.org/c3ref/funclist.html

Hint: sqlite3_errcode returns the "basic" error code. There are related APIs to retrieve the extended error code and the error message. Perhaps utilizing those those might solve your problem for you because we cannot see what you are doing as you have not provided actual code.

@Keith,

Adding sqlite3_errormsg() produced "not an error".

I will try to enable extended codes. Hold on...

Thank you.

== (the compare equal operator) has a higher precedence than = (the assignment apperator).

Therefor, if your code is as shown the if (res == 1) indicates no error and (res == 0) indicates an error.

@Keith,

Here is the actual code:

    int result = 0;
    char *err;
    sqlite3_stmt *stmt;
    std::wstring errorMessage;
    auto query = sqlite3_mprintf( "ATTACH %Q AS %w", catalog.c_str(), schema.c_str() );
    auto res = sqlite3_exec( m_db, query, NULL, NULL, &err );
    if( res != SQLITE_OK )
    {
        GetErrorMessage( res, errorMessage );
        errorMsg.push_back( errorMessage );
        result = 1;
        sqlite3_free( err );
    }
    else
    {
        sqlite3_free( query );
        std::wstring query1 = L"SELECT name FROM ";
        query1 += schema;
        query1 += L".sqlite_master WHERE type = 'table' OR type = 'view';";
        if( ( res = sqlite3_prepare_v2( m_db, sqlite_pimpl->m_myconv.to_bytes( query1.c_str() ).c_str(), (int) query1.length(), &stmt, 0 ) ) == SQLITE_OK )
        {
        }
        else
        {
        GetErrorMessage( res, errorMessage );
        errorMsg.push_back( errorMessage );
        result = 1;
        sqlite3_free( err );
        }
    }

void GetErrorMessage()
{
    auto msg = sqlite3_errmsg( m_db );
    auto error = sqlite3_extended_errcode( m_db );
    code = sqlite3_errcode( m_db );
}

With the help of enabling external error codes I finally got the meaningful message.

The value of msg is now "another row available". The error is 100.

Can you make any sense out of it?

Thank you.

When you sqlite3_step a statement, it may return SQLITE_DONE (meaning it is done), SQLITE_ROW (100) meaning that you need to process the row, or some other error code.

Basically you do:

rc = sqlite3_prepare(db, "sql statement here", &stmt);
if (rc != SQLITE_OK ) {
   /* Process the error and stop doing anything with this statement */
   BugOut();
}
while ((rc = sqlite3_step(stmt)) == SQLITE_ROW) {
   /* Process the row using sqlite3_column functions */
}
if (rc != SQLITE_DONE) {
   /* process the error from trying to run the statement */
   sqlite3_finalize(stmt);
   BugOut();
}
sqlite3_finalize(stmt);

See https://sqlite.org/cintro.html for beginners ...

@Keith,

But I'm not even doing sqlite3_step().

The failure occurs on the sqlite3_prepare_v2() call.

Thank you.

THen you are doing something wrong. sqlite3_prepare cannot return SQLITE_ROW.

Only sqlite3_step can return SQLITE_ROW.

@Keith,

Thank you for the help. It solved now.

It was my mistake and you help that helped me realize what happened.

You are probably trying to access the connection errorcode when no error has occurred, which means that the last non-zero result code is returned (read the fine manual).

You need to check the return codes for each call that you make that can return a status code. If and only if that indicates an error has occurred will the requests to obtain the last "non-zero status on the connection" for the errorcode, extended errorcode, and error message return information related to that non-zero return. Otherwise, it will give you information ab out the last non-zero return code that occurred on the connection.