Re: SQLBulkOperations Segfault

[Jeremy Faith <jfaith(at)cemsys(dot)com>]

Hiroshi Inoue wrote:
> Jeremy Faith wrote:
> > Hi,
> >
> > I have been experimenting with SQLBulkOperations to see if it is 
> > faster at inserting rows than using individual insert statements.
> > But when I run the attached(bo.c) example program it segfaults(note, 
> > this program works correctly with an Oracle ODBC connection).
> >
> > The program requires the following table.
> >  create table customer
> >  (
> >    cust_num integer not null,
> >    first_name char(20),
> >    last_name char(20)
> >  );
> >
> > Also the odb_db_open function call uses a hard coded DSN=pg_cdc_w, 
> > username=username and password=password.
> >
> > I am using the latest versions of pgsql+postgres on Linux i.e.
> >  psqlodbc-08.03.0100
> >  postgresql-8.3.1
> >
> > I turned on the ODBC debug log and added some log output to the 
> > driver and found that the segfault is occurring in the 
> > results.c:SC_pos_add function.
> >
> > In particular the log shows
> >  POS ADD fi=(nil) ti=(nil)
> >
> > and the segfault occurs on this line:-
> >                        if (*used != SQL_IGNORE && fi[i]->updatable)
> > So fi is NULL but is being referenced.
> >
> > I did a bit more checking and found that parse_statement sets up 
> > stmt->ird->irdopts.fi i.e.
> >        if (SC_update_not_ready(stmt))
> >                parse_statement(s.stmt, TRUE);  /* not preferable */
> > so adding
> >  fi=stmt->ird->irdopts.fi;
> > after parse_statement gets past this segfault.
> >
> > But then another segfault occurs in the results.c:positioned_load 
> > function as stmt->load_stmt is NULL
> >  so strlen(stmt->load_stmt) segfaults.
> >
> > The code is complicated and I have not been able to determine how to 
> > fix this, it seems likely to me that even the fi fix may be covering 
> > up an earlier problem somewhere else in the code.
>
> Before calling SQLExecDirect(), please set the SQL_ATTR_CURSOR_TYPE
> to SQL_CURSOR_STATIC or SQL_CURSOR_KEYSET_DRIVER and also set the
> SQL_ATTR_CONCURRENCY to SQL_CONCUR_ROWVER.
That fixes it, thanks.

Perhaps SQLBulkOperations could check that these attributes are set 
correctly and return an ODBC error if not. It is not clear from any ODBC 
documentation that I have read that these need to be set this way to do 
an SQL_ADD.

I have done a speed test and have found that the SQLBulkOperations 
method takes about twice as long as using a prepared insert. So as it is 
faster and easier the prepared insert seems to be the way to go.

Using the prepared insert method, the old driver included with unixODBC 
is considerably faster than psqlodbcw, any idea why?
Is there any problem with using the old driver?

Regards,
Jeremy Faith