Postgres Pro
Downloads
Home   >   mailing lists

Thread: Problem with libpsqlodbc

Problem with libpsqlodbc

From
Gilles DAROLD
Date:
Hi all,

I have sent some patches to the OpenLDAP dev team to have it work with
PostgreSQL and they should be applied asap, I also write a HOWTO.
This works but with a not usefull or silly workaround.

If we want to have PG fully compatible with OpenLDAP we need to resolve
a problem regarding the libpsqlodbc library.

In OpenLDAP they call the function SQLBindParameter as follow:

SQLBindParameter(sth,1,SQL_PARAM_OUTPUT,SQL_C_ULONG,SQL_INTEGER,0,0,&new_keyval,0,0);

And then the statement is executed with SQLExecDirect as follow:

rc=SQLExecDirect(sth,oc->create_proc,SQL_NTS);

What they expected is that the 'new_keyval' pointer may be modified at
execution time, with the new unique identifier returned by the query,
typically a sequence number.

It works fine with other database (mySQL, Oracle, etc.) but not with
PostgreSQL, so I suppose that the problem comes from the odbc library.

What's the problem ? Is the SQLBindParameter call is wrong ?
My knowledge is not enougth to find it, please help...

If someone could explain me what's wrong I should probably correct the
problem !

Regards,

Gilles DAROLD

Re: Problem with libpsqlodbc

From
Hiroshi Inoue
Date:
Gilles DAROLD wrote:
>
> Hi all,
>
> I have sent some patches to the OpenLDAP dev team to have it work with
> PostgreSQL and they should be applied asap, I also write a HOWTO.
> This works but with a not usefull or silly workaround.
>
> If we want to have PG fully compatible with OpenLDAP we need to resolve
> a problem regarding the libpsqlodbc library.
>

Did you build libpsqlidbc library on some unix platform ?

> In OpenLDAP they call the function SQLBindParameter as follow:
>
> SQLBindParameter(sth,1,SQL_PARAM_OUTPUT,SQL_C_ULONG,SQL_INTEGER,0,0,&new_keyval,0,0);
>
> And then the statement is executed with SQLExecDirect as follow:
>
> rc=SQLExecDirect(sth,oc->create_proc,SQL_NTS);
>

Are you executing a procedure using an escape sequence
  {[?=]call procedure-name[([parameter][,[parameter]]...)]}
?

regards,
Hiroshi Inoue

Re: Problem with libpsqlodbc

From
Gilles DAROLD
Date:
Hi,

Yes it is build on a Linux RH6.2 and I use procedure as follow:

create_proc => 'SELECT create_person(?)'
add_proc      => 'SELECT set_person_name(?,?)'

and the function are:

CREATE FUNCTION create_person(int4)
RETURNS int4 AS '
        INSERT INTO persons (id, name) VALUES (nextval(''persons_id_seq''), '' '');
        SELECT currval(''persons_id_seq'') AS keyval;
'
LANGUAGE 'sql';

CREATE FUNCTION set_person_name(int4, varchar)
RETURNS int4 AS '
        UPDATE persons SET name=text($2) WHERE $1=id;
        SELECT currval(''persons_id_seq'') AS keyval;
'
LANGUAGE 'sql';

Regards,

Gilles DAROLD

Hiroshi Inoue wrote:

> Gilles DAROLD wrote:
> >
> > Hi all,
> >
> > I have sent some patches to the OpenLDAP dev team to have it work with
> > PostgreSQL and they should be applied asap, I also write a HOWTO.
> > This works but with a not usefull or silly workaround.
> >
> > If we want to have PG fully compatible with OpenLDAP we need to resolve
> > a problem regarding the libpsqlodbc library.
> >
>
> Did you build libpsqlidbc library on some unix platform ?
>
> > In OpenLDAP they call the function SQLBindParameter as follow:
> >
> > SQLBindParameter(sth,1,SQL_PARAM_OUTPUT,SQL_C_ULONG,SQL_INTEGER,0,0,&new_keyval,0,0);
> >
> > And then the statement is executed with SQLExecDirect as follow:
> >
> > rc=SQLExecDirect(sth,oc->create_proc,SQL_NTS);
> >
>
> Are you executing a procedure using an escape sequence
>   {[?=]call procedure-name[([parameter][,[parameter]]...)]}
> ?
>
> regards,
> Hiroshi Inoue
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly

RE: Problem with libpsqlodbc

From
"Hiroshi Inoue"
Date:
> -----Original Message-----
> From: darold [mailto:darold]On Behalf Of Gilles DAROLD
>
> Hi,
>
> Yes it is build on a Linux RH6.2 and I use procedure as follow:
>
> create_proc => 'SELECT create_person(?)'
> add_proc      => 'SELECT set_person_name(?,?)'

Do they really work with other databases using
SQLBindParameter ?
SQLBindParameter(stmt, 1, ..) for above queries
means a binding to the first parameter "?".
Your functions seem to return the currval of a
sequence but doesn't change/set the first para-
meter(it's impossible in PostgreSQL). SELECT
doesn't return a parameter but returns a result
set in ODBC.

regards,
Hiroshi Inoue

Re: Problem with libpsqlodbc

From
Gilles DAROLD
Date:
Yes it work on other database but probably i'm missing something...

For oracle they do

create_proc => '{call create_person(?)}'
add_proc      => '{call set_person_name(?,?)}'

Is that the problem ? Should I use call ? I use select instead of call
because when I've tried it under pgsql call is not a keyword. So do I
definitively missed something ?

Sorry for asking that but could you give me a little sample modifying the
first parameter in a function as I could be less stupid and understand
a little ODBC ?

I request your help because I really don't know about ODBC and after
finding the solution for openldap I will probably never use ODBC  :-((

Regards,
Gilles Darold

Hiroshi Inoue wrote:

> > -----Original Message-----
> > From: darold [mailto:darold]On Behalf Of Gilles DAROLD
> >
> > Hi,
> >
> > Yes it is build on a Linux RH6.2 and I use procedure as follow:
> >
> > create_proc => 'SELECT create_person(?)'
> > add_proc      => 'SELECT set_person_name(?,?)'
>
> Do they really work with other databases using
> SQLBindParameter ?
> SQLBindParameter(stmt, 1, ..) for above queries
> means a binding to the first parameter "?".
> Your functions seem to return the currval of a
> sequence but doesn't change/set the first para-

> meter(it's impossible in PostgreSQL). SELECT
> doesn't return a parameter but returns a result
> set in ODBC.
>
> regards,
> Hiroshi Inoue
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Re: Problem with libpsqlodbc

From
Hiroshi Inoue
Date:
Gilles DAROLD wrote:
>
> Yes it work on other database but probably i'm missing something...

It's a limited spec of PostgreSQL that SELECT can call
function(procedure)s but SELECT returns a result set
not a parameter.

>
> For oracle they do
>
> create_proc => '{call create_person(?)}'
> add_proc      => '{call set_person_name(?,?)}'

It's an ODBC's spec to call a procedure.

>
> Is that the problem ? Should I use call ?

There seems to be 2 ways.

1) You could use the following.
    create_proc => '{?=call create_person(?)}'
    add_proc => '{?=call set_person_name(?, ?)}'
    (optional parameter marker ?= at the start of the syntax
     is needed if you need to accept the return value)

  But psqlodbc driver couldn't handle the syntax
  other than that of the latest snapshot.
  Could you try it ?

2) SELECT returns a result set.
  You could fetch and get the result using
  SQLFetch(), SQLGetData() etc.

regards,
Hiroshi Inoue