CODEWITHSUNDEEP
database-connectionfirebird
SoyonsPrecis
SoyonsPrecis

Reputation: 119

Firebird 2.5 C++ client: error with DATE data type

Environment: Firebird 2.5.4-64bit, on Windows 8.1.

I'm trying to write a C++ client using the C client API (using VS2013). I receive this error when preparing a SELECT statement on a table containing a DATE column (error when calling isc_dsql_prepare function)

Dynamic SQL Error -SQL error code = -804 -Data type unknown -Client SQL dialect 1 does not support reference to DATE datatype

From isql tool, I confirm the database is using SQL dialect 3: SQL> show sql dialect; Client SQL dialect is set to: 3 and database SQL dialect is: 3

On the client side, I am setting the SQL dialect to be 3 (at least I dont have error on attaching):

uint32_t sqlDialect = 3;
    const char* charset = "UTF8";
    char dpbBuffer[256], *dpb, *p;

    dpb = dpbBuffer;
    *dpb++ = isc_dpb_version1;
    *dpb++ = isc_dpb_user_name;
    *dpb++ = user.size();
    strcpy(dpb, user.c_str());
    dpb += user.size();
    *dpb++ = isc_dpb_password;
    *dpb++ = pwd.size();
    strcpy(dpb, pwd.c_str());
    dpb += pwd.size();
    *dpb++ = isc_dpb_lc_ctype;
    *dpb++ = 4;
    strcpy(dpb, charset);
    dpb += 4;
    *dpb++ = isc_dpb_sql_dialect;
    memcpy(dpb, &sqlDialect, 4);
    dpb += 4;

    short dpbLength = dpb - dpbBuffer;

    if (isc_attach_database(status, 0, url.c_str(), &_db, dpbLength, dpbBuffer))
    {
        isc_print_status(status);
    }

I have no issue with TIMESTAMP columns, hence I could set all my DATE columns to be of type TIMESTAMP, but I wish someone could explain the error message.

SOLUTION: Many thanks to Mark Rotteveel who pointed out the exact issue.

First, more information on how I prepare the statement:

std::string sql = "SELECT * FROM truc";
out_sqlda = (XSQLDA *)malloc(XSQLDA_LENGTH(NbColumn));
out_sqlda->version = SQLDA_VERSION1;
out_sqlda->sqln = NbColumn;
isc_stmt_handle stmt;
isc_dsql_allocate_statement(status, &_db, &stmt);
isc_dsql_prepare(status, &(TransactHandle), &stmt, 0, sql.c_str(), 1, out_sqlda);

I got abused by the Interbase API guide pdf which gives the following:

isc_dsql_prepare(
status_vector,
&trans, /* Set by previous isc_start_transaction() call. */
&stmt, /* Statement handle set by this function call. */
0, /* Specifies statement string is null-terminated. */
str, /* Statement string. */
SQLDA_VERSION1, /* XSQLDA version number. */
out_sqlda /* XSQLDA for storing column data. */
);

But the 6th parameter is NOT the SQLDA version, but the SQL dialect!!! Putting 3 solves the issue. Looking at the code of the .Net provider was very helpful as well.

Upvotes: 4

Views: 1415

Answers (1)

Mark Rotteveel
Mark Rotteveel

Reputation: 109015

As previously commented, some methods in the API take the SQL dialect version as the parameter (isc_dsql_prepare, isc_dsql_execute_immediate and isc_dsql_exec_immed2). If you call these with the parameter 1 instead of 3 your statement will be parsed as a dialect 1 statement.

As you note an example in the Interbase 6 API Guide (on page 122) says it is the SQLDA version. My guess is that in a previous version of Interbase (dialects were introduced in version 6) this parameter was the SQLDA version (see for example isc_describe which takes a da_version parameter before the XSQLDA parameter), and that they reused this parameter to keep the method parameter types and counts the same. The fact that some of these methods need the da_version parameter at all is kind of weird (the sqlda version is part of the XSQLDA).

Upvotes: 2

Related Questions