[C++] ODBC and accessing Generated Keys

[Luciano Resende <lu...@gmail.com>]

Hi

   I'm trying to work with Adriano Crestani in a C++ version of DAS and we
are trying to figure out how to get access to the generated keys after the
execution of a prepared statement. Would you guys, the C++ experts, be able
to give us some pointers/references or examples ?

-- 
Luciano Resende
http://people.apache.org/~lresende

Re: [C++] ODBC and accessing Generated Keys

[Luciano Resende <lu...@gmail.com>]

>Why can't you do the second select?  Is that just a performance/consistency
concern?

Yes, but if there is no other way, we would probably go this way....

-- 
Luciano Resende
http://people.apache.org/~lresende

On 1/3/07, Simon Laws <si...@googlemail.com> wrote:
>
> On 1/3/07, Adriano Crestani <ad...@gmail.com> wrote:
> >
> > Sorry Simon, yes, it's really what you are assuming. We are trying to
> get
> > the key values.
> >
> > I was checking again how the DAS Java get the auto-generated PKs and it
> > tries to get it right after the statement execution and not before as I
> > was
> > saying on my last message.
> >
> > I'd want to know a way to retrieve the auto-generated PKs whitout I read
> > from the database after the insertion statement. There is a way to set
> on
> > jdbc whether you want or not that the insertion statement store the
> > auto-generated PKs for later reading. Example using jdbc:
> >
> > PrepareStatement ps = connection.prepareStatement("INSERT INTO
> item(descr,
> > units) VALUES('teste', 5);", Statement.RETURN_GENERATED_KEYS);
> >           ps.execute();
> >           ResultSet rs = ps.getGeneratedKeys();
> >
> > The item table has an auto-generated PK called ID. This insertion
> > statement
> > when is executed auto-generates the item PK. I'd like to know a way to
> > retrieve this PK on odbc without using another statement like a "select"
> > statement just to read the PK.
> >
> > Adriano Crestani
> >
> >
> > On 1/2/07, Simon Laws <si...@googlemail.com> wrote:
> > >
> > > On 1/2/07, Adriano Crestani <ad...@gmail.com> wrote:
> > > >
> > > > We are trying to get the auto-gererated keys after the invocation of
> > the
> > > > SQLPrepare function of ODBC API. For example, the SQLPrepare is
> called
> > > > with
> > > > an insertion statement "INSERT INTO table1 values(?, ?, ?)". The
> odbc
> > > must
> > > >
> > > > send this statement to the DBMS, the DBMS compile it and waits for
> the
> > > > parameters and its execution.
> > > >
> > > > However there are some DBMSs that auto-generates the primary keys
> and
> > is
> > > > capable to retrieve the auto-generated PKs right after the statement
> > > > compilation and before its execution. It's exactly what we're trying
> > to
> > > > do,
> > > > to retrieve the auto-generated PKs before the statement execution.
> > > >
> > > > I wonder if it has something to do with the IPD(Implementation
> > Parameter
> > > > Descriptor) that I found in ODBC API: SQL_ATTR_AUTO_IPD.
> > > >
> > > > Adriano Crestani
> > > >
> > > > On 1/1/07, Jean-Sebastien Delfino <js...@apache.org> wrote:
> > > > >
> > > > > Luciano Resende wrote:
> > > > > > Hi
> > > > > >
> > > > > >   I'm trying to work with Adriano Crestani in a C++ version of
> DAS
> > > and
> > > > > we
> > > > > > are trying to figure out how to get access to the generated keys
> > > after
> > > > > > the
> > > > > > execution of a prepared statement. Would you guys, the C++
> > experts,
> > > be
> > > > > > able
> > > > > > to give us some pointers/references or examples ?
> > > > > >
> > > > >
> > > > > Luciano,
> > > > >
> > > > > I'll be happy to help but I'll need a little more context :)
> > generated
> > > > > keys? a prepared statement? hmm what about starting with an
> overview
> > > of
> > > > > what you're trying to do? which database? version? operating
> system?
> > > > > what kind of examples are you looking for?
> > > > >
> > > > > Thanks
> > > > >
> > > > > --
> > > > > Jean-Sebastien
> > > > >
> > > > >
> > > > >
> > ---------------------------------------------------------------------
> > > > > To unsubscribe, e-mail: tuscany-dev-unsubscribe@ws.apache.org
> > > > > For additional commands, e-mail: tuscany-dev-help@ws.apache.org
> > > > >
> > > > >
> > > >
> > > > Hi Adriano
> > >
> > > I have to admit that I'm not an ODBC expert but there are a couple of
> > > things
> > > that I don't understand in your question.
> > >
> > > > We are trying to get the auto-generated keys after the invocation of
> > the
> > > SQLPrepare function of ODBC API.
> > >
> > > 1/ By "auto-gererated keys" do you mean
> > >   a) Manually defined columns which auto increment or otherwise
> generate
> > > an
> > > automatic key
> > >   b) Magic DBMS columns that uniquely identify a row (and are likely
> to
> > be
> > > specific to the DBMS)
> > >
> > > I'm assuming you are doing 1a)
> > >
> > > 2/ By "trying to get the auto-gererated keys after the invocation of
> the
> > > SQLPrepare" do you mean
> > >   a) You are trying to get at the column meta data
> > >   b) You are trying to get at the key values
> > >
> > > I assuming 2a) here as I don't understand why you would be trying to
> get
> > > the
> > > key value before the execute. Maybe you can provide some more detail.
> > >
> > > Given 1a) then retrieving the meta data for the column (2a) should not
> > be
> > > any different than with any other column. I took a quick look at ODBC
> > API:
> > > SQL_ATTR_AUTO_IPD and, despite the usual paucity of ODBC
> documentation,
> > > this
> > > does seem to be to do with populating the IPD with column meta data
> > > automatically. It is optional though so your driver might not support
> > it.
> > > If
> > > you can't get the meta data through the prepared statement then you
> > would
> > > have to use another approach, for example, you might have to resort to
> > the
> > > catalog.  Anyhow, the best I can suggest at the moment it that you try
> > it
> > > and see what happens.
> > >
> > > As Jean-Sebastien suggested if you provide us with a little more info,
> > for
> > > example, a sample table description and some details about what you
> are
> > > trying to achieve we may be able to help a little more.
> > >
> > > Regards
> > >
> > > Simon
> > >
> > >
> >
> > Hi Adriano
>
> Ok so you are after the PK value after the execute. Browsing to other
> posters of this question it would seem to be difficult with ODBC. Why
> can't
> you do the second select?  Is that just a performance/consistency concern?
>
> Simon
>
>

Re: [C++] ODBC and accessing Generated Keys

[Simon Laws <si...@googlemail.com>]

On 1/3/07, Adriano Crestani <ad...@gmail.com> wrote:
>
> Sorry Simon, yes, it's really what you are assuming. We are trying to get
> the key values.
>
> I was checking again how the DAS Java get the auto-generated PKs and it
> tries to get it right after the statement execution and not before as I
> was
> saying on my last message.
>
> I'd want to know a way to retrieve the auto-generated PKs whitout I read
> from the database after the insertion statement. There is a way to set on
> jdbc whether you want or not that the insertion statement store the
> auto-generated PKs for later reading. Example using jdbc:
>
> PrepareStatement ps = connection.prepareStatement("INSERT INTO item(descr,
> units) VALUES('teste', 5);", Statement.RETURN_GENERATED_KEYS);
>           ps.execute();
>           ResultSet rs = ps.getGeneratedKeys();
>
> The item table has an auto-generated PK called ID. This insertion
> statement
> when is executed auto-generates the item PK. I'd like to know a way to
> retrieve this PK on odbc without using another statement like a "select"
> statement just to read the PK.
>
> Adriano Crestani
>
>
> On 1/2/07, Simon Laws <si...@googlemail.com> wrote:
> >
> > On 1/2/07, Adriano Crestani <ad...@gmail.com> wrote:
> > >
> > > We are trying to get the auto-gererated keys after the invocation of
> the
> > > SQLPrepare function of ODBC API. For example, the SQLPrepare is called
> > > with
> > > an insertion statement "INSERT INTO table1 values(?, ?, ?)". The odbc
> > must
> > >
> > > send this statement to the DBMS, the DBMS compile it and waits for the
> > > parameters and its execution.
> > >
> > > However there are some DBMSs that auto-generates the primary keys and
> is
> > > capable to retrieve the auto-generated PKs right after the statement
> > > compilation and before its execution. It's exactly what we're trying
> to
> > > do,
> > > to retrieve the auto-generated PKs before the statement execution.
> > >
> > > I wonder if it has something to do with the IPD(Implementation
> Parameter
> > > Descriptor) that I found in ODBC API: SQL_ATTR_AUTO_IPD.
> > >
> > > Adriano Crestani
> > >
> > > On 1/1/07, Jean-Sebastien Delfino <js...@apache.org> wrote:
> > > >
> > > > Luciano Resende wrote:
> > > > > Hi
> > > > >
> > > > >   I'm trying to work with Adriano Crestani in a C++ version of DAS
> > and
> > > > we
> > > > > are trying to figure out how to get access to the generated keys
> > after
> > > > > the
> > > > > execution of a prepared statement. Would you guys, the C++
> experts,
> > be
> > > > > able
> > > > > to give us some pointers/references or examples ?
> > > > >
> > > >
> > > > Luciano,
> > > >
> > > > I'll be happy to help but I'll need a little more context :)
> generated
> > > > keys? a prepared statement? hmm what about starting with an overview
> > of
> > > > what you're trying to do? which database? version? operating system?
> > > > what kind of examples are you looking for?
> > > >
> > > > Thanks
> > > >
> > > > --
> > > > Jean-Sebastien
> > > >
> > > >
> > > >
> ---------------------------------------------------------------------
> > > > To unsubscribe, e-mail: tuscany-dev-unsubscribe@ws.apache.org
> > > > For additional commands, e-mail: tuscany-dev-help@ws.apache.org
> > > >
> > > >
> > >
> > > Hi Adriano
> >
> > I have to admit that I'm not an ODBC expert but there are a couple of
> > things
> > that I don't understand in your question.
> >
> > > We are trying to get the auto-generated keys after the invocation of
> the
> > SQLPrepare function of ODBC API.
> >
> > 1/ By "auto-gererated keys" do you mean
> >   a) Manually defined columns which auto increment or otherwise generate
> > an
> > automatic key
> >   b) Magic DBMS columns that uniquely identify a row (and are likely to
> be
> > specific to the DBMS)
> >
> > I'm assuming you are doing 1a)
> >
> > 2/ By "trying to get the auto-gererated keys after the invocation of the
> > SQLPrepare" do you mean
> >   a) You are trying to get at the column meta data
> >   b) You are trying to get at the key values
> >
> > I assuming 2a) here as I don't understand why you would be trying to get
> > the
> > key value before the execute. Maybe you can provide some more detail.
> >
> > Given 1a) then retrieving the meta data for the column (2a) should not
> be
> > any different than with any other column. I took a quick look at ODBC
> API:
> > SQL_ATTR_AUTO_IPD and, despite the usual paucity of ODBC documentation,
> > this
> > does seem to be to do with populating the IPD with column meta data
> > automatically. It is optional though so your driver might not support
> it.
> > If
> > you can't get the meta data through the prepared statement then you
> would
> > have to use another approach, for example, you might have to resort to
> the
> > catalog.  Anyhow, the best I can suggest at the moment it that you try
> it
> > and see what happens.
> >
> > As Jean-Sebastien suggested if you provide us with a little more info,
> for
> > example, a sample table description and some details about what you are
> > trying to achieve we may be able to help a little more.
> >
> > Regards
> >
> > Simon
> >
> >
>
> Hi Adriano

Ok so you are after the PK value after the execute. Browsing to other
posters of this question it would seem to be difficult with ODBC. Why can't
you do the second select?  Is that just a performance/consistency concern?

Simon

Re: [C++] ODBC and accessing Generated Keys

[Adriano Crestani <ad...@gmail.com>]

Sorry Simon, yes, it's really what you are assuming. We are trying to get
the key values.

I was checking again how the DAS Java get the auto-generated PKs and it
tries to get it right after the statement execution and not before as I was
saying on my last message.

I'd want to know a way to retrieve the auto-generated PKs whitout I read
from the database after the insertion statement. There is a way to set on
jdbc whether you want or not that the insertion statement store the
auto-generated PKs for later reading. Example using jdbc:

PrepareStatement ps = connection.prepareStatement("INSERT INTO item(descr,
units) VALUES('teste', 5);", Statement.RETURN_GENERATED_KEYS);
          ps.execute();
          ResultSet rs = ps.getGeneratedKeys();

The item table has an auto-generated PK called ID. This insertion statement
when is executed auto-generates the item PK. I'd like to know a way to
retrieve this PK on odbc without using another statement like a "select"
statement just to read the PK.

Adriano Crestani


On 1/2/07, Simon Laws <si...@googlemail.com> wrote:
>
> On 1/2/07, Adriano Crestani <ad...@gmail.com> wrote:
> >
> > We are trying to get the auto-gererated keys after the invocation of the
> > SQLPrepare function of ODBC API. For example, the SQLPrepare is called
> > with
> > an insertion statement "INSERT INTO table1 values(?, ?, ?)". The odbc
> must
> >
> > send this statement to the DBMS, the DBMS compile it and waits for the
> > parameters and its execution.
> >
> > However there are some DBMSs that auto-generates the primary keys and is
> > capable to retrieve the auto-generated PKs right after the statement
> > compilation and before its execution. It's exactly what we're trying to
> > do,
> > to retrieve the auto-generated PKs before the statement execution.
> >
> > I wonder if it has something to do with the IPD(Implementation Parameter
> > Descriptor) that I found in ODBC API: SQL_ATTR_AUTO_IPD.
> >
> > Adriano Crestani
> >
> > On 1/1/07, Jean-Sebastien Delfino <js...@apache.org> wrote:
> > >
> > > Luciano Resende wrote:
> > > > Hi
> > > >
> > > >   I'm trying to work with Adriano Crestani in a C++ version of DAS
> and
> > > we
> > > > are trying to figure out how to get access to the generated keys
> after
> > > > the
> > > > execution of a prepared statement. Would you guys, the C++ experts,
> be
> > > > able
> > > > to give us some pointers/references or examples ?
> > > >
> > >
> > > Luciano,
> > >
> > > I'll be happy to help but I'll need a little more context :) generated
> > > keys? a prepared statement? hmm what about starting with an overview
> of
> > > what you're trying to do? which database? version? operating system?
> > > what kind of examples are you looking for?
> > >
> > > Thanks
> > >
> > > --
> > > Jean-Sebastien
> > >
> > >
> > > ---------------------------------------------------------------------
> > > To unsubscribe, e-mail: tuscany-dev-unsubscribe@ws.apache.org
> > > For additional commands, e-mail: tuscany-dev-help@ws.apache.org
> > >
> > >
> >
> > Hi Adriano
>
> I have to admit that I'm not an ODBC expert but there are a couple of
> things
> that I don't understand in your question.
>
> > We are trying to get the auto-generated keys after the invocation of the
> SQLPrepare function of ODBC API.
>
> 1/ By "auto-gererated keys" do you mean
>   a) Manually defined columns which auto increment or otherwise generate
> an
> automatic key
>   b) Magic DBMS columns that uniquely identify a row (and are likely to be
> specific to the DBMS)
>
> I'm assuming you are doing 1a)
>
> 2/ By "trying to get the auto-gererated keys after the invocation of the
> SQLPrepare" do you mean
>   a) You are trying to get at the column meta data
>   b) You are trying to get at the key values
>
> I assuming 2a) here as I don't understand why you would be trying to get
> the
> key value before the execute. Maybe you can provide some more detail.
>
> Given 1a) then retrieving the meta data for the column (2a) should not be
> any different than with any other column. I took a quick look at ODBC API:
> SQL_ATTR_AUTO_IPD and, despite the usual paucity of ODBC documentation,
> this
> does seem to be to do with populating the IPD with column meta data
> automatically. It is optional though so your driver might not support it.
> If
> you can't get the meta data through the prepared statement then you would
> have to use another approach, for example, you might have to resort to the
> catalog.  Anyhow, the best I can suggest at the moment it that you try it
> and see what happens.
>
> As Jean-Sebastien suggested if you provide us with a little more info, for
> example, a sample table description and some details about what you are
> trying to achieve we may be able to help a little more.
>
> Regards
>
> Simon
>
>

Re: [C++] ODBC and accessing Generated Keys

[Simon Laws <si...@googlemail.com>]

On 1/2/07, Adriano Crestani <ad...@gmail.com> wrote:
>
> We are trying to get the auto-gererated keys after the invocation of the
> SQLPrepare function of ODBC API. For example, the SQLPrepare is called
> with
> an insertion statement "INSERT INTO table1 values(?, ?, ?)". The odbc must
>
> send this statement to the DBMS, the DBMS compile it and waits for the
> parameters and its execution.
>
> However there are some DBMSs that auto-generates the primary keys and is
> capable to retrieve the auto-generated PKs right after the statement
> compilation and before its execution. It's exactly what we're trying to
> do,
> to retrieve the auto-generated PKs before the statement execution.
>
> I wonder if it has something to do with the IPD(Implementation Parameter
> Descriptor) that I found in ODBC API: SQL_ATTR_AUTO_IPD.
>
> Adriano Crestani
>
> On 1/1/07, Jean-Sebastien Delfino <js...@apache.org> wrote:
> >
> > Luciano Resende wrote:
> > > Hi
> > >
> > >   I'm trying to work with Adriano Crestani in a C++ version of DAS and
> > we
> > > are trying to figure out how to get access to the generated keys after
> > > the
> > > execution of a prepared statement. Would you guys, the C++ experts, be
> > > able
> > > to give us some pointers/references or examples ?
> > >
> >
> > Luciano,
> >
> > I'll be happy to help but I'll need a little more context :) generated
> > keys? a prepared statement? hmm what about starting with an overview of
> > what you're trying to do? which database? version? operating system?
> > what kind of examples are you looking for?
> >
> > Thanks
> >
> > --
> > Jean-Sebastien
> >
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: tuscany-dev-unsubscribe@ws.apache.org
> > For additional commands, e-mail: tuscany-dev-help@ws.apache.org
> >
> >
>
> Hi Adriano

I have to admit that I'm not an ODBC expert but there are a couple of things
that I don't understand in your question.

> We are trying to get the auto-generated keys after the invocation of the
SQLPrepare function of ODBC API.

1/ By "auto-gererated keys" do you mean
  a) Manually defined columns which auto increment or otherwise generate an
automatic key
  b) Magic DBMS columns that uniquely identify a row (and are likely to be
specific to the DBMS)

I'm assuming you are doing 1a)

2/ By "trying to get the auto-gererated keys after the invocation of the
SQLPrepare" do you mean
  a) You are trying to get at the column meta data
  b) You are trying to get at the key values

I assuming 2a) here as I don't understand why you would be trying to get the
key value before the execute. Maybe you can provide some more detail.

Given 1a) then retrieving the meta data for the column (2a) should not be
any different than with any other column. I took a quick look at ODBC API:
SQL_ATTR_AUTO_IPD and, despite the usual paucity of ODBC documentation, this
does seem to be to do with populating the IPD with column meta data
automatically. It is optional though so your driver might not support it. If
you can't get the meta data through the prepared statement then you would
have to use another approach, for example, you might have to resort to the
catalog.  Anyhow, the best I can suggest at the moment it that you try it
and see what happens.

As Jean-Sebastien suggested if you provide us with a little more info, for
example, a sample table description and some details about what you are
trying to achieve we may be able to help a little more.

Regards

Simon

Re: [C++] ODBC and accessing Generated Keys

[Adriano Crestani <ad...@gmail.com>]

We are trying to get the auto-gererated keys after the invocation of the
SQLPrepare function of ODBC API. For example, the SQLPrepare is called with
an insertion statement "INSERT INTO table1 values(?, ?, ?)". The odbc must
send this statement to the DBMS, the DBMS compile it and waits for the
parameters and its execution.

However there are some DBMSs that auto-generates the primary keys and is
capable to retrieve the auto-generated PKs right after the statement
compilation and before its execution. It's exactly what we're trying to do,
to retrieve the auto-generated PKs before the statement execution.

I wonder if it has something to do with the IPD(Implementation Parameter
Descriptor) that I found in ODBC API: SQL_ATTR_AUTO_IPD.

Adriano Crestani

On 1/1/07, Jean-Sebastien Delfino <js...@apache.org> wrote:
>
> Luciano Resende wrote:
> > Hi
> >
> >   I'm trying to work with Adriano Crestani in a C++ version of DAS and
> we
> > are trying to figure out how to get access to the generated keys after
> > the
> > execution of a prepared statement. Would you guys, the C++ experts, be
> > able
> > to give us some pointers/references or examples ?
> >
>
> Luciano,
>
> I'll be happy to help but I'll need a little more context :) generated
> keys? a prepared statement? hmm what about starting with an overview of
> what you're trying to do? which database? version? operating system?
> what kind of examples are you looking for?
>
> Thanks
>
> --
> Jean-Sebastien
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: tuscany-dev-unsubscribe@ws.apache.org
> For additional commands, e-mail: tuscany-dev-help@ws.apache.org
>
>

Re: [C++] ODBC and accessing Generated Keys

[Jean-Sebastien Delfino <js...@apache.org>]

Luciano Resende wrote:
> Hi
>
>   I'm trying to work with Adriano Crestani in a C++ version of DAS and we
> are trying to figure out how to get access to the generated keys after 
> the
> execution of a prepared statement. Would you guys, the C++ experts, be 
> able
> to give us some pointers/references or examples ?
>

Luciano,

I'll be happy to help but I'll need a little more context :) generated 
keys? a prepared statement? hmm what about starting with an overview of 
what you're trying to do? which database? version? operating system? 
what kind of examples are you looking for?

Thanks

-- 
Jean-Sebastien


---------------------------------------------------------------------
To unsubscribe, e-mail: tuscany-dev-unsubscribe@ws.apache.org
For additional commands, e-mail: tuscany-dev-help@ws.apache.org