Hello
> I will do more tests and report then.
Here is my tests. Follow...
First, libdbi specific functions apparently works well. (in linux,
gcc, mysql and pgsql)
My objective is to use libdbi and pgsql driver (but in future this can
be for others drivers too) capable to do a batch insert (like
http://www.postgresql.org/docs/8.2/interactive/libpq-copy.html)
The following patchs can be used to test COPY commands in Postgresql.
But don't work yet :((. It doesn't insert anything in table.
patch.txt is a new test for COPY INSERT with Postgresql
patch_pgsql.txt is a fix in resstatus about return PGRES_COPY_OUT and
PGRES_COPY_IN
Test 8: Run driver-specific function parameters:
libdbi: [query] CREATE TABLE batch ( fileindex int, jobid int, path varchar)
Ok.
libdbi: [query] COPY batch FROM STDIN
Ok COPY batch FROM STDIN.
PQputCopyData 1 2 test_path1
PQputCopyData returned: 1
PQputCopyData 2 3 test_path2
PQputCopyData returned: 1
PQputCopyEnd returned: 1
Error: PostgreSQL database support (using libpq)
libdbi: [query] SELECT * from batch
Got result, 0 rows, try to access rows
The main gain in use COPY commands is to grow performance in INSERT querys
Anybody has any idea? What I need to do for that works?
Thanks
Index: tests/test_dbi.c
===================================================================
RCS file: /cvsroot/libdbi-drivers/libdbi-drivers/tests/test_dbi.c,v
retrieving revision 1.50
diff -u -r1.50 test_dbi.c
--- tests/test_dbi.c 7 Mar 2008 20:51:27 -0000 1.50
+++ tests/test_dbi.c 29 Mar 2008 16:43:33 -0000
@@ -93,6 +93,7 @@
int test_drop_db(struct CONNINFO* ptr_cinfo, dbi_conn conn);
int test_error_messages(struct CONNINFO* ptr_cinfo, dbi_conn conn, int n);
int test_custom_function(struct CONNINFO* ptr_cinfo, dbi_conn conn);
+int test_custom_function_parameters(struct CONNINFO* ptr_cinfo, dbi_conn conn);
int my_dbi_initialize(const char *driverdir, dbi_inst *Inst);
void my_dbi_shutdown(dbi_inst Inst);
dbi_driver my_dbi_driver_list(dbi_driver Current, dbi_inst Inst);
@@ -190,6 +191,7 @@
exit(1);
}
+
/* Test: create database */
printf("\nTest %d: Create database %s using default encoding: \n",
testnumber++, cinfo.dbname);
@@ -232,6 +234,15 @@
exit(1);
}
+ /* Test: custom function parameters*/
+ printf("\n\nTest %d: Run driver-specific function parameters: \n",
testnumber++, cinfo.dbname);
+
+ if (test_custom_function_parameters(&cinfo, conn)) {
+ dbi_conn_close(conn);
+ my_dbi_shutdown(dbi_instance);
+ exit(1);
+ }
+
/* Test: insert row */
printf("\nTest %d: Insert row: \n", testnumber++);
@@ -640,6 +651,159 @@
}
/* returns 0 on success, 1 on error */
+int test_custom_function_parameters(struct CONNINFO* ptr_cinfo, dbi_conn conn)
{
+ dbi_conn_t *myconn = conn;
+
+ /* attempt to call a trivial function of the client library */
+ if (!strcmp(ptr_cinfo->drivername, "firebird")) {
+ fprintf(stderr, "\tnot yet implemented for this driver\n");
+ return 0;
+ }
+ else if (!strcmp(ptr_cinfo->drivername, "freetds")) {
+ fprintf(stderr, "\tnot yet implemented for this driver\n");
+ return 0;
+ }
+ else if (!strcmp(ptr_cinfo->drivername, "ingres")) {
+ fprintf(stderr, "\tnot yet implemented for this driver\n");
+ return 0;
+ }
+ else if (!strcmp(ptr_cinfo->drivername, "msql")) {
+ fprintf(stderr, "\tnot yet implemented for this driver\n");
+ return 0;
+ }
+ else if (!strcmp(ptr_cinfo->drivername, "mysql")) {
+ fprintf(stderr, "\tnot yet implemented for this driver\n");
+ return 0;
+ }
+ else if (!strcmp(ptr_cinfo->drivername, "oracle")) {
+ fprintf(stderr, "not yet implemented\n");
+ return 0;
+ }
+ else if (!strcmp(ptr_cinfo->drivername, "pgsql")) {
+ int res;
+ int count = 30;
+ const char *error;
+ const char *errmsg;
+ int (*custom_function_copy)(void*, const char*, int) = NULL;
+ int (*custom_function_end)(void*, const char*) = NULL;
+ dbi_result result;
+
+ const char *query = "COPY batch FROM STDIN";
+ const char *query_data1 = "1\t2\ttest_path1";
+ const char *query_data2 = "2\t3\ttest_path2";
+ const char *table_batch = "CREATE TABLE batch ( fileindex int, jobid int,
path varchar)";
+
+ // Start copy
+
+ if ((result = dbi_conn_query(conn, table_batch)) == NULL) {
+ dbi_conn_error(conn, &errmsg);
+ printf("\tAAH! Can't create table! Error message: %s\n", errmsg);
+ return 1;
+ } else {
+ printf("\tOk.\n");
+ }
+ dbi_result_free(result);
+
+ if ((result = dbi_conn_query(conn, query)) == NULL) {
+ dbi_conn_error(conn, &errmsg);
+ printf("\tAAH! Can't query! Error message: %s\n", errmsg);
+ return 1;
+ } else {
+ printf("\tOk %s.\n", query);
+ }
+ dbi_result_free(result);
+
+ // Start copy insert
+ // Insert data two times
+ if ((custom_function_copy =
dbi_driver_specific_function(dbi_conn_get_driver(conn), "PQputCopyData")) !=
NULL) {
+
+ printf("\tPQputCopyData %s\n", query_data1);
+ res = custom_function_copy(myconn->connection, query_data1,
strlen(query_data1));
+ printf("\tPQputCopyData returned: %d\n", res);
+
+ if (res <= 0) {
+ printf("\tD'uh! PQputCopyData error\n");
+ return 1;
+ }
+
+ printf("\tPQputCopyData %s\n", query_data2);
+ res = custom_function_copy(myconn->connection, query_data2,
strlen(query_data2));
+ printf("\tPQputCopyData returned: %d\n", res);
+
+ if (res <= 0) {
+ printf("\tD'uh! PQputCopyData error\n");
+ return 1;
+ }
+ }
+
+ // End data
+ if ((custom_function_end =
dbi_driver_specific_function(dbi_conn_get_driver(conn), "PQputCopyEnd")) !=
NULL) {
+ do {
+ res = custom_function_end(myconn->connection, error);
+ } while (res == 0 && --count > 0);
+ }
+
+ if (res <= 0) {
+ printf("\tD'uh! PQputCopyEnd error\n");
+ return 1;
+ }
+ printf("\tPQputCopyEnd returned: %d\n\tError: %s\n", res, error);
+
+ if ((result = dbi_conn_query(conn, "SELECT * from batch")) == NULL) {
+ dbi_conn_error(conn, &errmsg);
+ printf("\tAAH! Can't get read data! Error message: %s\n", errmsg);
+ return 1;
+ }
+
+ printf("\tGot result, %d rows, try to access rows\n",
dbi_result_get_numrows(result));
+
+ while (dbi_result_next_row(result)) {
+ const char *errmsg = NULL;
+ long the_long_one = 0;
+ long the_long_two = 0;
+ const char* the_string;
+
+ dbi_error_flag errflag;
+
+ /* first retrieve the values */
+ the_long_one = dbi_result_get_long(result, "id");
+ errflag = dbi_conn_error(dbi_result_get_conn(result), &errmsg);
+ if (errflag) {
+ printf("the_int_one errflag=%s\n", errmsg);
+ }
+
+ the_long_two = dbi_result_get_long(result, "jobid");
+ errflag = dbi_conn_error(dbi_result_get_conn(result), &errmsg);
+ if (errflag) {
+ printf("the_int_two errflag=%s\n", errmsg);
+ }
+
+ the_string = dbi_result_get_string(result, "path");
+ errflag = dbi_conn_error(dbi_result_get_conn(result), &errmsg);
+ if (errflag) {
+ printf("the_stringr errflag=%s\n", errmsg);
+ }
+
+ printf("\tResult: %d\t%d\t%s",the_long_one, the_long_two, the_string);
+ }
+
+ return 0;
+ }
+ else if (!strcmp(ptr_cinfo->drivername, "sqlite")) {
+ fprintf(stderr, "\tnot yet implemented for this driver\n");
+ return 0;
+ }
+ else if (!strcmp(ptr_cinfo->drivername, "sqlite3")) {
+ fprintf(stderr, "\tnot yet implemented for this driver\n");
+ return 0;
+ }
+ else {
+ printf("\tD'uh! Cannot run custom function\n");
+ return 1;
+ }
+}
+
+/* returns 0 on success, 1 on error */
int ask_for_conninfo(struct CONNINFO* ptr_cinfo) {
int numdrivers;
char interface[16];
Index: dbd_pgsql.c
===================================================================
RCS file: /cvsroot/libdbi-drivers/libdbi-drivers/drivers/pgsql/dbd_pgsql.c,v
retrieving revision 1.56
diff -u -r1.56 dbd_pgsql.c
--- dbd_pgsql.c 7 Mar 2008 20:51:27 -0000 1.56
+++ dbd_pgsql.c 29 Mar 2008 16:44:31 -0000
@@ -486,7 +486,7 @@
res = PQexec((PGconn *)conn->connection, statement);
if (res) resstatus = PQresultStatus(res);
- if (!res || ((resstatus != PGRES_COMMAND_OK) && (resstatus !=
PGRES_TUPLES_OK))) {
+ if (!res || ((resstatus != PGRES_COMMAND_OK) && (resstatus !=
PGRES_TUPLES_OK) && (resstatus != PGRES_COPY_OUT) && (resstatus !=
PGRES_COPY_IN))) {
PQclear(res);
return NULL;
}
-------------------------------------------------------------------------
Check out the new SourceForge.net Marketplace.
It's the best place to buy or sell services for
just about anything Open Source.
http://ad.doubleclick.net/clk;164216239;13503038;w?http://sf.net/marketplace
_______________________________________________
libdbi-users mailing list
libdbi-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/libdbi-users
