Shibboleth Developers

[giacomo tenaglia <>]

Sorry for the late answer, I've had to study a bit Oracle and ODBC.

On Fri, Jan 11, 2008 at 01:40:08PM -0500, Scott Cantor wrote:
> > In Oracle it's not possible to insert strings longer than 4000
> > charachters. For doing this it's necessary to insert the string in a
> > temporary clob and use it inside the INSERT.
> 
> I don't understand the second sentence. But the way it *should* work is by
> feeding the data in a loop using a parametrized statement, but I was not
> able to get that to work on Unix *at all*. I had to fall back to using
> straight inserts. I was able to get the read data loops to work, so I left
> those in.

"Clob" is the Oracle data type for big charachter strings.

What I've undestood is that using big strings directly inside an INSERT
statement executed directly is not considered to be a best practice. The
suggested way to do it (and the only way to have it working in Oracle)
is to use SQLPrepare like this:

string q  = string("INSERT INTO table VALUES (?,?,?,?,?)";
SQLPrepare(stmt, (SQLCHAR*)q.c_str(), SQL_NTS);

then for each parameter use SQLBindParam to bind it to an application
variable, for example:

SQLBindParam(stmt, 1, SQL_C_CHAR, SQL_VARCHAR, 0, 0, scontext, 
strlen(scontext));

and at the end execute the statement:

SQLExecute(stmt);

I'm currently working in order to check if it works on Oracle.

> > So in order to insert in an Oracle ODBC cache the attributes received,
> > in my opinion there's the need for a code change, or the fork of the
> > plugin to another one Oracle-specific.
> 
> I think that the simplicity and reliability of the straight insert for
> drivers that support it demands that anything for Oracle be conditional.
> Probably a conditional macro set by configure.

I think that the prepare/bindparam/execute stuff is general enough to be
used in the general ODBC code. The problems arise because the datatypes
(and consequently the ParameterType argument of SQLBindParam) differ
from db to db, expecially those related to big text fields and to
timestamps/dates. But maybe it's not too difficult to generalize it or
to make it ODBC driver-dependant..

Anyway as soon as I have some working code I will post it to the list.
Maybe it will require to foresee the different INSERTs that can be made,
and organize the SQLBindParam in a different way for each one.

giacomo

-- 
giacomo tenaglia
Technical Student at CERN IT/DES-SIS
CNR Biblioteca d'Area di Bologna - http://biblio.bo.cnr.it
Phone +41 76 5003376 - 
sip: