Re: Q: How obtain ID assigned by Informix Serial datatype from PC Client (C++/MFC)
Malin Goodwin wrote:
>
> I have an Informix Database where I use SERIAL
> datatype to generate unique id's. If a new
> row is inserted with id = 0, informix will assign
> the next available id.
>
> I need to use the new id (that informix assigned)
> to insert a related row in a second table.
>
> Question:
> How/Can I achieve this from a
> PC Client written in microsoft C++/MFC
> with Recordsets (using OpenLink
> ODBC Driver ) ?
>
> Below is some sample code to show the embedded
> SQL, and what is attempted in C++/MFC.
>
> The use of Requery() in the example below poses
> the following problems:
> (1) If other clients have inserted new rows
> between my update/requiry (very possible) I may
> not get my "own" row.
> (Need to use Open(CRecordset::snapshot)? )
>
> (2) Need to call Open() with m_strSort = "id DESC"
> which assumes that each new id will be larger
> than previously assigned ID's. I'm not sure
> that this assumption can be made
>
> Has anyone tried to do something like this?
>
> Thanks
> /Malin
>
> /*********** Embedded sql********************/
> int new_id;
> insert into PERSON (id, name)
> values ( 0, "Fred");> new_id = sqlca.sqlerrd[1];
> insert into TASK (id, task)
> values ( :new_id, "Shovel snow");> /********************************************/
>
> /**************** C++ with MFC **************/
> CPerson p; /* derived from CRecordset */
> CTask t; /* derived from CRecordset */
> int new_id;
>
> p.Open();
> p.AddNew();
> strcpy(p.name, "Fred");
> p.id = 0;
> p.Update();
>
> p.Requery();
> new_id = p.id;
>
> p.Close()
> t.Open()
> t.AddNew()
> t.id = new_id;
> strcpy(t.task, "Rake the lawn");
> t.Update();
> t.Close();
>
> /******* end of sample code ******************I think in the sqlca you can get the value of the serial you just inserted.
Cheers