sqlany_execute() failed but sqlany_error() didnt return an error.

[oneeyeman1]

Hi, ALL,

For some reason the following code:

[code]

if( !m_api.sqlany_execute( m_stmt ) )
{
 char buffer[SACAPI_ERROR_SIZE + 20] {};
char sqlstate[6];
int rc;
rc = m_api.sqlany_error( m_conn, buffer, sizeof( buffer ) );
break;
}

does not fill the "buffer" variable with the error message.

Could someone please suggest what could be the issue and how to perform the successful query execution.

Thank you.

[jack_schueler]

Works fine for me.

[ERROR] Execute failed: [-141] Table 'foo' not found.

I used print_error routine in the stmt_exec example.

Are you sure that sqlany_execute or sqlany_execute_immediate was the last thing executed?

void
print_error( a_sqlany_connection * sqlany_conn, char * str )
{
    char buffer[SACAPI_ERROR_SIZE];
    int  rc;
    rc = api.sqlany_error( sqlany_conn, buffer, sizeof(buffer));
    printf( "[ERROR] %s: [%d] %s\n", str, rc, buffer );
}
called here for example.                                                          
   ok = api.sqlany_execute( sqlany_stmt );
   if( !ok ) {
       print_error( sqlany_conn, (char *)"Execute failed" );
   }

[oneeyeman1]

@Jack_shueler,

Here is my scenario:

1. Run

CREATE TABLE IF NOT EXISTS abcattbl(abt_os integer NOT NULL, abt_tnam char(129) NOT NULL, abt_tid integer, abt_ownr char(129) NOT NULL, abd_fhgt smallint, abd_fwgt smallint, abd_fitl char(1), abd_funl char(1), abd_fstr smallint, abd_fchr smallint, abd_fptc smallint, abd_ffce char(18), abh_fhgt smallint, abh_fwgt smallint, abh_fitl char(1), abh_funl char(1), abh_fstr smallint, abh_fchr smallint, abh_fptc smallint, abh_ffce char(18), abl_fhgt smallint, abl_fwgt smallint, abl_fitl char(1), abl_funl char(1), abl_fstr smallint, abl_fchr smallint, abl_fptc smallint, abl_ffce char(18), abt_cmnt char(254), PRIMARY KEY( abt_tnam, abt_ownr ));

inside the transaction and commit it.

2. Call following procedure:

std::wstring qry2 = L"INSERT INTO abcattbl ON EXISTING SKIP VALUES( ?, ?, (SELECT t.table_id FROM SYS.SYSTABLE t, SYS.SYSUSER u WHERE t.creator = u.user_id AND u.user_name = ? AND t.table_name = ?), ?, 8, 400, 'N', 'N', 0, 1, 0, 'MS Sans Serif', 8, 400, 'N', 'N', 0, 1, 0, 'MS Sans Serif', 8, 400, 'N', 'N', 0, 1, 0, 'MS Sans Serif', '' );";

int osid = 0;

a_sqlany_bind_param param;
if( !result )
{
    if( !m_api.sqlany_describe_bind_param( m_stmt, 0, &param ) )
    {
        GetErrorMessage( errorMsg );
        result = 1;
    }
}
if( !result )
{
    param.value.buffer = (char *) &osid;
    param.value.is_null = nullptr;
// param.value.type = A_UVAL32;
    if( !m_api.sqlany_bind_param( m_stmt, 0, &param ) )
    {
        GetErrorMessage( errorMsg );
        result = 1;
    }
}

if( !result )
{
    for( std::map<std::wstring, std::vector<TableDefinition> >::iterator it = pimpl.m_tableDefinitions.begin(); it != pimpl.m_tableDefinitions.end(); ++it )
    {
        for( std::vector<TableDefinition>::iterator it1 = (*it).second.begin(); it1 < (*it).second.end(); ++it1 )
        {
            sacapi_bool isNull;
            size_t len1, len2, len3, len4, len5, len6;
            if( !m_api.sqlany_describe_bind_param( m_stmt, 1, &param ) )
            {
                GetErrorMessage( errorMsg );
                result = 1;
                break;
            }
            if( !result )
            {
                len1 = (*it1).fullName.length();
                param.value.buffer = const_cast<char *>( sqlany_pimpl->m_myconv.to_bytes( (*it1).fullName.c_str() ).c_str() );
                param.value.length = &len1;
                param.value.is_null = &isNull;
                if( !m_api.sqlany_bind_param( m_stmt, 1, &param ) )
                {
                    GetErrorMessage( errorMsg );
                    result = 1;
                    break;
                }
            }
            if( !m_api.sqlany_describe_bind_param( m_stmt, 2, &param ) )
            {
                GetErrorMessage( errorMsg );
                result = 1;
                break;
            }
            if( !result )
            {
                len2 = (*it1).schemaName.length();
                param.value.buffer = const_cast<char *>( sqlany_pimpl->m_myconv.to_bytes( (*it1).schemaName.c_str() ).c_str() );
                param.value.length = &len2;
                param.value.is_null = &isNull;
                if( !m_api.sqlany_bind_param( m_stmt, 2, &param ) )
                {
                    GetErrorMessage( errorMsg );
                    result = 1;
                    break;
                }
            }
            if( !m_api.sqlany_describe_bind_param( m_stmt, 3, &param ) )
            {
                GetErrorMessage( errorMsg );
                result = 1;
                break;
            }
            if( !result )
            {
                len3 = (*it1).tableName.length();
                param.value.buffer = const_cast<char *>( sqlany_pimpl->m_myconv.to_bytes( (*it1).tableName.c_str() ).c_str() );
                param.value.length = &len3;
                param.value.is_null = &isNull;
                if( !m_api.sqlany_bind_param( m_stmt, 3, &param ) )
                {
                    GetErrorMessage( errorMsg );
                    result = 1;
                    break;
                }
            }
            if( !m_api.sqlany_describe_bind_param( m_stmt, 4, &param ) )
            {
                GetErrorMessage( errorMsg );
                result = 1;
                break;
            }
            if( !result )
            {
                len4 = (*it1).schemaName.length();
                param.value.buffer = const_cast<char *>( sqlany_pimpl->m_myconv.to_bytes( (*it1).schemaName.c_str() ).c_str() );
                param.value.length = &len4;
                param.value.is_null = &isNull;
                if( !m_api.sqlany_bind_param( m_stmt, 4, &param ) )
                {
                    GetErrorMessage( errorMsg );
                    result = 1;
                    break;
                }
            }
            if( !result )
            {
                auto i = m_api.sqlany_execute( m_stmt );
                if( !m_api.sqlany_execute( m_stmt ) )
                {
                    GetErrorMessage( errorMsg );
                    result = 1;
                    break;
                }
            }
        }
    }
}

sqlany_execute() returns 0, but calling GetErrorMessage(), which is:

int GetErrorMessage(std::vector<std::wstring> &errorMsg)
{
    char buffer[SACAPI_ERROR_SIZE + 20] {};
    char sqlstate[6];
    int rc;
    rc = m_api.sqlany_error( m_conn, buffer, SACAPI_ERROR_SIZE + 20 );
    m_api.sqlany_sqlstate( m_conn, sqlstate, sizeof( sqlstate ) );
    strcat( buffer, ": SQLSTATE: " );
    strcat( buffer, sqlstate );
    errorMsg.push_back( sqlany_pimpl->m_myconv.from_bytes( buffer ) );
    return true;
}

rc equal 0 and the buffer is empty.

Thank you

[jack_schueler]

When pasting code, use a code block. What you pasted is unreadable. I suggest you start debugging your code. - Schueler, not Shueler.

[chris_keating]

I do not see anything in your implementation (snipet) that should result in a SQL error. There is an implementation error in that while you did bind the parameters, you did not provide values for them. Take a look at the stmt_exec.cpp example making note of the code starting at line 218 which supplies the parameters values and then executes the statement. If you look a request log with your app (-zr all or equivalent with sa_server_option system proceedure), your implementation does not supply any host variables to the parameters -- and there is no error reported in that log either.

[oneeyeman1]

@chris_keating,

I reformatted the code for easier reading.

Also - I made it based on the preparing_statements.cpp as it is a query with parameters. But it should not matter.

For every single parameter I do set "param.value.buffer". Just like in both samples..

Thank you,

[oneeyeman1]

@Jack_shueler, I edit the reply and added the code tags. Please check it.