Hello folks, I'm stuck on a problem which I hope some one could help me with. I have a database entry form where the user will enter in information for two related tables. When she submits the form, I want to first post one tables information, then call another sql method to get the id generated for that post, then take that value and add it to the REQUEST object and pass it to the second sql insert for inserting to the second table (along with some other parts of the REQUEST object from the form). I'm planning on using MySQL that has a special select: select LAST_INSERT_ID() to return the last auto-generated id value to get the id. I know you can add a variable to the REQUEST object via <!--#call "REQUEST.set('varname', value)"--> but how to I get the value from the sql method? The only way I know now is via #var tags but you can't nest the DTML. I hope this makes sense... -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Tom Jenkins DevIS (http://www.devis.com) Linux & Samba 2.0 : The best Windows file server http://www.zdnet.com/sr/stories/issue/0,4537,2196106,00.html "Do not be overcome by evil, but overcome evil with good." Romans 12:21
Tom Jenkins wrote:
Hello folks, I'm stuck on a problem which I hope some one could help me with. I have a database entry form where the user will enter in information for two related tables. When she submits the form, I want to first post one tables information, then call another sql method to get the id generated for that post, then take that value and add it to the REQUEST object and pass it to the second sql insert for inserting to the second table (along with some other parts of the REQUEST object from the form).
Do you really need this? You can put more than one sql commands in a sql method, you just have to put a <!--#var sql_delimiter --> between them. You can insert in the two tables with just one method. The last_insert_id() should be in the values field of the second insert. Take a look at the code genereated by the sql wizard in the contrib page.
I'm planning on using MySQL that has a special select: select LAST_INSERT_ID() to return the last auto-generated id value to get the id.
I know you can add a variable to the REQUEST object via <!--#call "REQUEST.set('varname', value)"--> but how to I get the value from the sql method? The only way I know now is via #var tags but you can't nest the DTML.
Take a look at the other thread. []s -- Paulo Eduardo Neves PUC-Rio de Janeiro Pager: Central: 292-4499 cod. 213 99 64 ou use a URL: http://www.learn.fplf.org.br/neves/mensagempager.html
Do you really need this? You can put more than one sql commands in a sql method, you just have to put a <!--#var sql_delimiter --> between them. You can insert in the two tables with just one method. The last_insert_id() should be in the values field of the second insert. Take a look at the code genereated by the sql wizard in the contrib page. Hey Paulo, thanks for that tip. I didn't know about #var sql_delimiter and the ability to put more than one sql commands in a sql method. I had wondered about it but the #var sql_delimiter was the key. With this information, no I don't need to insert into the REQUEST object. But I'm still curious <g>.
Unfortunately, putting last_insert_id() directly into the values of the second insert did not work. It works from mysql command line. <!--#var sql_delimiter --> INSERT INTO entry_tbl (master_id,submitted_by,staff_id,version,comment) VALUES (LAST_INSERT_ID(), <!--#sqlvar entry_tbl.submitted_by type=int -->, <!--#sqlvar master_tbl.entered_by type=int -->, <!--#sqlvar entry_tbl.version type=string optional-->, <!--#sqlvar master_tbl.comment type=string optional-->) It surprised me that it didn't work as I thought it would get sent enmass to mysql for processing so I had thought that the DA wouldn't interfer with it. feeling-very-dense-ly y'rs -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Tom Jenkins DevIS (http://www.devis.com) Linux & Samba 2.0 : The best Windows file server http://www.zdnet.com/sr/stories/issue/0,4537,2196106,00.html "Do not be overcome by evil, but overcome evil with good." Romans 12:21
participants (2)
-
Paulo Eduardo Neves -
Tom Jenkins