Re: backsql: myodbc oddity

[Dmitry Kovalev <mitya@seismic.geol.msu.ru>]

Hello Peter!

Peter Duffy wrote:

> Hi,
>
> I'm currently trying to get adds working in a back-sql directory.

First of all, as I can understand, you are trying to do this on mySQL...

Well, back-sql currently does not support adds on mySQL,
because it expects new id as OUT parameter from a stored procedure, not as
dataset like from 'select last_insert_id()'...
This was done so because in general it is a lot trickier to insert or
modify data properly in one statement, than select it, so it is assumed
that these operations are better done through stored procedures.
Since mySQL does not support stored procedures (though they plan them),
back-sql/mySQL does not support adds.
But for some simple cases, when you can do adds/modifications with simple
queries, one can patch back-sql a little, and make it execute "select
last_insert_id()' explicitly to get new id...

I will add this as configurable option when I have a little time, meanwhile

you can do this yourself (feel free to submit any patches).


> At the
> moment, I'm hitting a strange problem where myodbc seems to be choking
> on the statement "insert into accounts (uid) values ('');\n select
> last_insert_id()": the message is "You have an error in your SQL syntax
> near '; select last_insert_id()' at line 1". (NB I'm just using "\n" to
> represent the newline in the ldap_oc_mappings.create_proc value.)
>
> The same concatenation of statements works fine when issued from the
> mysql console (it's based on the create_proc statement for inetOrgPerson
> given in the sample file testdb_metadata.sql.)

Sorry for confusion, this line has accidentally survived after my tests
(when I was figuring out a way to support adds in mySQL), and got to
release...


>
>
> Just before I start rummaging about in the myodbc code, I wondered if
> anyone else had hit anything like this.

As far as I know from these tests, myODBC simply expects _one_ query in the
SQLExecute, and
cannot handle two.
As said above, it is back-sql that needs rummaging about (a little :) in
this case. Add explicit SQLExecute/fetch for 'select last_insert_id()', or
just wait a little until I add this as configurable option :)

>
>
> Releases: openldap 2.0.0 (OK, I plan to go to the latest modification
> RSN: at the moment, batch-loading of the data and lookups are working
> fine, and I'm scared of disturbing something: apologies if this problem
> has already been fixed in the interim);

there are lots of other problems solved in latest (CVS) code, including
crashes on bad filters, etc. (see my recent post to -devel for summary),
and many improvements to frontend, so you should give it a try probably :)

WBW, Dmitry