Hi List,
I'm seeing strange behavior here :
I create a database with 2 tables each with two fields. These fields are INT.
I run a select * on the first table, and use dbi_result_get_int to retreive
the values, and dbi_result_get_field_type to see the field types... no
problem, everything is as expected.
I run a select * on the second table, and use dbi_result_get_int to retreive
the values, and dbi_result_get_field_type to see the field types... no
problem, everything is as expected.
Now I run a select "select tableone.a,tabletwo.d from two as tabletwo, one as
tableone where tableone.a=tabletwo.c;"
Now the results say that "a" is of type string and the get int function
returns 0, while "d" is type int and get the int ok.
If I switch the order of the tables, i.e. "from one as tableone, two as
tabletwo", then the "a" column is ok, but the "d" column is seen as a string.
When I run the query through the sqlite3 interface, everything is ok.
sqlite3.5.9
libdbi 0.8.3
libdbidriver 0.8.3.1
Here is the code to see the problem :
#include <string>
struct ConnInfo {
std::string driverdir;
std::string drivername;
std::string dbname;
std::string timeout;
std::string dbdir;
};
class test {
public:
test();
virtual ~test();
static void runtest();
static int callback(void *NotUsed, int argc, char **argv, char
**azColName);
};
#include "dbi/dbi.h"
#include <sqlite3.h>
test::test() {
}
test::~test() {
}
void test::runtest() {
ConnInfo conninfo;
dbi_conn conn;
dbi_result result;
int retcode;
const char* errormsg;
sqlite3 *db;
char *zErrMsg = 0;
conninfo.driverdir = "/usr/local/lib/dbd";
printf("DBI_DRIVER_DIR = %s\n", conninfo.driverdir.c_str());
conninfo.drivername = "sqlite3";
printf("DBI_DRIVER_NAME = %s\n", conninfo.drivername.c_str());
conninfo.dbdir = "/home/kgs";
printf("DB_DIR = %s\n", conninfo.dbdir.c_str());
conninfo.dbname = "dbitest.db";
printf("DB_NAME = %s\n", conninfo.dbname.c_str());
conninfo.timeout = "0";
if ( atoi(conninfo.timeout.c_str()) < 1 ) conninfo.timeout = "0";
printf("DBI_SQLITE3_TIMEOUT = %s\n", conninfo.timeout.c_str());
if ( dbi_initialize(conninfo.driverdir.c_str()) < 1 ) {
printf("Unable to initialize libdbi!\n");
dbi_shutdown();
}
conn = dbi_conn_new(conninfo.drivername.c_str());
retcode = dbi_conn_set_option(conn, "dbname", conninfo.dbname.c_str());
if ( retcode != 0 ) {
printf("cannot set database option dbname!\n");
}
retcode = dbi_conn_set_option(conn, "sqlite3_dbdir",
conninfo.dbdir.c_str());
if ( retcode != 0 ) {
printf("cannot set database option sqlite3_dbdir!\n");
}
retcode = dbi_conn_set_option(conn, "sqlite3_timeout",
conninfo.timeout.c_str());
if ( retcode != 0 ) {
printf("cannot set database option sqlite3_timeout!\n");
}
retcode = dbi_conn_connect(conn);
if ( retcode != 0 ) {
printf("DBI return code = %d\n", retcode);
printf("cannot connect to database!\n");
}
// create database
result = dbi_conn_queryf(conn,"create table one (a INT,b INT);");
result = dbi_conn_queryf(conn,"create table two (c INT,d INT);");
// insert data
result = dbi_conn_queryf(conn,"insert into one values(1,1);");
result = dbi_conn_queryf(conn,"insert into one values(2,2);");
result = dbi_conn_queryf(conn,"insert into one values(3,3);");
result = dbi_conn_queryf(conn,"insert into one values(4,4);");
result = dbi_conn_queryf(conn,"insert into two values(1,1);");
result = dbi_conn_queryf(conn,"insert into two values(2,2);");
result = dbi_conn_queryf(conn,"insert into two values(3,3);");
result = dbi_conn_queryf(conn,"insert into two values(4,4);");
// single table select table one
printf("single table select table one\n");
result = dbi_conn_queryf(conn,"select tableone.a,tableone.b from one as
tableone;");
while ( dbi_result_next_row(result) ) {
unsigned short fieldtype =
dbi_result_get_field_type(result,"a");
printf("fieldtype = %d\n",fieldtype);
int theint = dbi_result_get_int(result, "a");
printf("theint = %d\n",theint);
fieldtype = dbi_result_get_field_type(result,"b");
printf("fieldtypeb = %d\n",fieldtype);
int theintb = dbi_result_get_int(result, "b");
printf("theintb = %d\n",theintb);
}
dbi_result_free(result);
// single table select table two
printf("single table select table two\n");
result = dbi_conn_queryf(conn,"select tabletwo.c from two as
tabletwo;");
if ( result == NULL ) {
dbi_conn_error(conn,&errormsg);
printf("errormsg = %s\n",errormsg);
}
while ( dbi_result_next_row(result) ) {
unsigned short fieldtype =
dbi_result_get_field_type(result,"c");
printf("fieldtype = %d\n",fieldtype);
int theint = dbi_result_get_int(result, "c");
printf("theint = %d\n",theint);
}
dbi_result_free(result);
// join select
printf("join select\n");
result = dbi_conn_queryf(conn,"select tableone.a,tabletwo.d from two as
tabletwo, one as tableone where tableone.a=tabletwo.c;");
if ( result == NULL ) {
dbi_conn_error(conn,&errormsg);
printf("errormsg = %s\n",errormsg);
}
while ( dbi_result_next_row(result) ) {
unsigned short fieldtype =
dbi_result_get_field_type(result,"a");
printf("fieldtype a = %d\n",fieldtype);
int theinta = dbi_result_get_int(result, "a");
printf("theinta = %d\n",theinta);
fieldtype = dbi_result_get_field_type(result,"d");
printf("fieldtype d = %d\n",fieldtype);
int theintd = dbi_result_get_int(result, "d");
printf("theintd = %d\n",theintd);
}
dbi_result_free(result);
dbi_conn_close(conn);
// try the join select with sqlite3 directly
retcode = sqlite3_open("/home/kgs/dbitest.db", &db);
if( retcode ){
fprintf(stderr, "Can't open database: %s\n",
sqlite3_errmsg(db));
sqlite3_close(db);
exit(1);
}
retcode = sqlite3_exec(db, "select tableone.a,tabletwo.d from two as
tabletwo, one as tableone where tableone.a=tabletwo.c;", callback, 0,
&zErrMsg);
if( retcode!=SQLITE_OK ){
fprintf(stderr, "SQL error: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
}
sqlite3_close(db);
}
int test::callback(void *NotUsed, int argc, char **argv, char **azColName){
int i;
for(i=0; i<argc; i++){
printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
}
printf("\n");
return 0;
}
int main() {
test::runtest();
}
-------------------------------------------------------------------------
Check out the new SourceForge.net Marketplace.
It's the best place to buy or sell services for
just about anything Open Source.
http://sourceforge.net/services/buy/index.php
_______________________________________________
libdbi-users mailing list
libdbi-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/libdbi-users
