Re: using MSQL's LAST_INSERT_ID() with DatabaseAddAction

[Rolfe Jenny]

Title: Re: using MSQL's LAST_INSERT_ID() with DatabaseAddAction

I'm trying to do much the same thing as in the message beneath:
please forgive me if I've posted this twice!
<auth-descriptor>
<connection>xcpt</connection>
<table name="page">

<keys>
<key param="pageid" dbcol="pageid" type="int" mode="manual"/>
</keys>
<values>
<value param="pagetitle" dbcol="title" type="string"/>
<value param="pagename" dbcol="pagename" type="string"/>
<value param="pagestructure" dbcol="templatenum" type="string"/>
<value param="location" dbcol="techlocation" type="string"/>
<value param="layoutnum" dbcol="layoutnum" type="string"/>

</values>
</table>

<table name="boalinks">
<values>
<value param="page:pageid" dbcol="childpageid" type="int" />
<value param="layoutnum" dbcol="parentboaid" type="string"/>

</values>
</table>

</auth-descriptor>

What I want to do is automatically generate an id in one table and pick this
id up and put it in another table.
The adding to multiple tables bit works fine but the childpageid column is
empty. I have tried key:page:pageid and multiple variations on the theme
but nothing seems to work.

Any suggestions?
thanks
jenny



Subject: Re: using MSQL's LAST_INSERT_ID() with DatabaseAddAction


                On 01.Mar.2002 -- 03:34 PM, Giuseppe Di Pierri wrote:  > Hi there,  > > I have  a problem using the DatabaseAddAction in MySQL.  >  > I would do two table insertions, one after an other like the following  > example:   The value is written to a request attribute (table.column) that you  can use if you were using the manual method of determining the id  (looks up current max value +1).    

                 I was about to check in new database actions to scratchpad (atually,  new versions of the ones already there) but since I encountered  problems with datasources with today's CVS I cannot verify that they  still work. Those do understand mysql's autoincrement columns. But  they use a slightly different database.xml syntax *and* their name  and their configuration sitemap-wise will change with my checkin.   If you'd like to stay tuned.... should be only a matter of days  now.   > INSERT INTO base (id,...) VALUES(NULL,...);  > # id is defined as INT NOT NULL AUTO_INCREMENT  >  > INSERT INTO derived (id,....) VALUES(LAST_INSERT_ID(),....);  >  > <employee>  > <connection>personnel</connection>  > <table name="base">  > <keys>  > <key param="id" dbcol="id" type="int" mode="manual"/>  > </keys>  > <values>  > <value param="myparam" dbcol="...." type="string" />  > </values>  > </table>  > <table name="derived">  > <keys>  > <!-- how can I use the mysql LAST_INSERT_ID() here,  > in order to insert the previous inserted auto_increment value  > <key param="id" dbcol="id" type="int"/>  > ?-->  <key param="base.id" dbcol="id" type="int"/>     

Christian, you gave me the trigger to look at the source:

Is this also correct ?
<key param="key:base:id" dbcol="id" type="int"/>

bye
pino

                > </keys>  > <values>  > <value param="..." dbcol="..." type="string" />  > </values>  > </table>  > </employee>   Chris.