MS SQLServer Lasso 6 db transactions
Locked
MS SQLServer Lasso 6 db transactions
 
|
MS_SQL-Server, IIS6, Lasso6, Win2k...
I need to do multiple inserts on several tables. After the first, I need to grab the ID of the newly created row before any other users might enter e row. In Cold Fusion, I used CFTRANSACTION tags around my CFQUERY (Lasso=Inline) statements. How can I do and insert and then grab the newly created ID (integer-auto-increment for identity - primary key field) from the new record for an insert elsewhere? Two separate standard statements don't preclude additional inserts between my insert and my select. -Jon -- ------------------------------ Lasso Support: http://support.omnipilot.com/ Search the list archives: http://www.listsearch.com/lassotalk.lasso Manage your list subscription: http://www.listsearch.com/lassotalk.lasso?manage |
Re: MS SQLServer Lasso 6 db transactions
|
|
Jonathan Schwarz ?crit: > MS_SQL-Server, IIS6, Lasso6, Win2k... > > I need to do multiple inserts on several tables. After the first, I need > to grab the ID of the newly created row before any other users might > enter e row. In Cold Fusion, I used CFTRANSACTION tags around my CFQUERY > (Lasso=Inline) statements. > > How can I do and insert and then grab the newly created ID > (integer-auto-increment for identity - primary key field) from the new > record for an insert elsewhere? Two separate standard statements don't > preclude additional inserts between my insert and my select. > > > -Jon > The simpliest way is to create a stored procedure You can also use the same inline to send you sql queries to the server. It will be a bloc and identified as a complete transaction. --- Olivier Miossec AIM/ichat omiossec -- http://www.lasso-developpeur.net -- Need a Lasso consultant ? (Lasso, Java, Databases Consultant ...) http://www.lasso-developpeur.net/oliviermiossec/ -- ------------------------------ Lasso Support: http://support.omnipilot.com/ Search the list archives: http://www.listsearch.com/lassotalk.lasso Manage your list subscription: http://www.listsearch.com/lassotalk.lasso?manage |
Re: MS SQLServer Lasso 6 db transactions
|
|
In reply to this post by Jonathan Schwarz
What if I put the "Select MAX(ID)" inline inside of the "inline" that does the insert - would this effectively do the same thing?
-Jon -----Original Message----- From: [hidden email] [mailto:[hidden email]] On Behalf Of Olivier Miossec Sent: Wednesday, June 01, 2005 8:30 AM To: [hidden email] Subject: Re: MS SQLServer Lasso 6 db transactions Jonathan Schwarz écrit: > MS_SQL-Server, IIS6, Lasso6, Win2k... > > I need to do multiple inserts on several tables. After the first, I need > to grab the ID of the newly created row before any other users might > enter e row. In Cold Fusion, I used CFTRANSACTION tags around my CFQUERY > (Lasso=Inline) statements. > > How can I do and insert and then grab the newly created ID > (integer-auto-increment for identity - primary key field) from the new > record for an insert elsewhere? Two separate standard statements don't > preclude additional inserts between my insert and my select. > > > -Jon > The simpliest way is to create a stored procedure You can also use the same inline to send you sql queries to the server. It will be a bloc and identified as a complete transaction. --- Olivier Miossec AIM/ichat omiossec -- http://www.lasso-developpeur.net -- Need a Lasso consultant ? (Lasso, Java, Databases Consultant ...) http://www.lasso-developpeur.net/oliviermiossec/ -- ------------------------------ Lasso Support: http://support.omnipilot.com/ Search the list archives: http://www.listsearch.com/lassotalk.lasso Manage your list subscription: http://www.listsearch.com/lassotalk.lasso?manage -- ------------------------------ Lasso Support: http://support.omnipilot.com/ Search the list archives: http://www.listsearch.com/lassotalk.lasso Manage your list subscription: http://www.listsearch.com/lassotalk.lasso?manage |
Re: MS SQLServer Lasso 6 db transactions
|
|
In reply to this post by Jonathan Schwarz
Jonathan Schwarz ?crit: > What if I put the "Select MAX(ID)" inline inside of the "inline" that does the insert - would this effectively do the same thing? > You may read this http://www.listsearch.com/lassotalk.lasso?id=148976 About Sql Server Identity and scope --- Olivier Miossec AIM/ichat omiossec -- http://www.lasso-developpeur.net -- Need a Lasso consultant ? (Lasso, Java, Databases Consultant ...) http://www.lasso-developpeur.net/oliviermiossec/ -- ------------------------------ Lasso Support: http://support.omnipilot.com/ Search the list archives: http://www.listsearch.com/lassotalk.lasso Manage your list subscription: http://www.listsearch.com/lassotalk.lasso?manage |
Re: MS SQLServer Lasso 6 db transactions
|
|
In reply to this post by Jonathan Schwarz
Is there no equivalent to [KeyField_Value] for SQLServer?
-Jon -----Original Message----- From: [hidden email] [mailto:[hidden email]] On Behalf Of Olivier Miossec Sent: Wednesday, June 01, 2005 9:28 AM To: [hidden email] Subject: Re: MS SQLServer Lasso 6 db transactions Jonathan Schwarz écrit: > What if I put the "Select MAX(ID)" inline inside of the "inline" that does the insert - would this effectively do the same thing? > You may read this http://www.listsearch.com/lassotalk.lasso?id=148976 About Sql Server Identity and scope --- Olivier Miossec AIM/ichat omiossec -- http://www.lasso-developpeur.net -- Need a Lasso consultant ? (Lasso, Java, Databases Consultant ...) http://www.lasso-developpeur.net/oliviermiossec/ -- ------------------------------ Lasso Support: http://support.omnipilot.com/ Search the list archives: http://www.listsearch.com/lassotalk.lasso Manage your list subscription: http://www.listsearch.com/lassotalk.lasso?manage -- ------------------------------ Lasso Support: http://support.omnipilot.com/ Search the list archives: http://www.listsearch.com/lassotalk.lasso Manage your list subscription: http://www.listsearch.com/lassotalk.lasso?manage |
Re: MS SQLServer Lasso 6 db transactions
|
|
In reply to this post by Jonathan Schwarz
Olivier -
I have read and reread what your referenced thread stated - I am still a little unsure about its use - one of the 3 methods has been 'protected' and I can't see the tag at all, of the other two the 3rd one sounded promising... just not clear on implementation, could you give an example of an Inline -Add that uses SCOPE_IDENTITY()? Thanks!!! -Jon -----Original Message----- From: [hidden email] [mailto:[hidden email]] On Behalf Of Olivier Miossec Sent: Wednesday, June 01, 2005 9:28 AM To: [hidden email] Subject: Re: MS SQLServer Lasso 6 db transactions Jonathan Schwarz écrit: > What if I put the "Select MAX(ID)" inline inside of the "inline" that does the insert - would this effectively do the same thing? > You may read this http://www.listsearch.com/lassotalk.lasso?id=148976 About Sql Server Identity and scope --- Olivier Miossec AIM/ichat omiossec -- http://www.lasso-developpeur.net -- Need a Lasso consultant ? (Lasso, Java, Databases Consultant ...) http://www.lasso-developpeur.net/oliviermiossec/ -- ------------------------------ Lasso Support: http://support.omnipilot.com/ Search the list archives: http://www.listsearch.com/lassotalk.lasso Manage your list subscription: http://www.listsearch.com/lassotalk.lasso?manage -- ------------------------------ Lasso Support: http://support.omnipilot.com/ Search the list archives: http://www.listsearch.com/lassotalk.lasso Manage your list subscription: http://www.listsearch.com/lassotalk.lasso?manage |
Re: MS SQLServer Lasso 6 db transactions
|
|
In reply to this post by Jonathan Schwarz
Jonathan Schwarz ?crit: > Olivier - > > I have read and reread what your referenced thread stated - I am still a little unsure about its use - one of the 3 methods has been 'protected' and I can't see the tag at all, of the other two the 3rd one sounded promising... just not clear on implementation, could you give an example of an Inline -Add that uses SCOPE_IDENTITY()? > > Thanks!!! > It depend on what you want to do (your sql queries) and how you need to execute them. --- Olivier Miossec AIM/ichat omiossec -- http://www.lasso-developpeur.net -- Need a Lasso consultant ? (Lasso, Java, Databases Consultant ...) http://www.lasso-developpeur.net/oliviermiossec/ -- ------------------------------ Lasso Support: http://support.omnipilot.com/ Search the list archives: http://www.listsearch.com/lassotalk.lasso Manage your list subscription: http://www.listsearch.com/lassotalk.lasso?manage |
Re: MS SQLServer Lasso 6 db transactions
|
|
In reply to this post by Jonathan Schwarz
Its actually quite simple. I want to do an Inline -Add and then grab the KeyField Value of the record just entered. Here's what I have to-date (but there is apparently no guarantee in this method that another INSERT could occur before I get the ID field back):
[Inline: -Add, -Database='abc', , -Table='this_table', -KeyField='ID', 'companyName'=(Var:'companyName'), 'custContact'=(Var:'custContact'), 'phone'=(Var:'phone'), 'addrStreet1'=(Var:'addrStreet1'), 'addrStreet2'=(Var:'addrStreet2'), 'addrCity'=(Var:'addrCity'), 'addrState'=(Var:'addrState'), 'addrZip'=(Var:'addrZip'), 'entryDate'=(date)] [Var:'this_keyfield' = (KeyField_Value)] (this returns blank because its SQLServer) [Var:'initGet_sql'='SELECT MAX(ID) AS MAXID FROM abc.dbo.this_table'] [Inline: -InlineName='initGet', -Database='abc', -SQL=(Var:'initGet_sql'), -MaxRecords='1'] [Var:'thisRecord' = (field:'MAXID')] [/Inline] [/Inline] -Jon -----Original Message----- From: [hidden email] [mailto:[hidden email]] On Behalf Of Olivier Miossec Sent: Wednesday, June 01, 2005 5:00 PM To: [hidden email] Subject: Re: MS SQLServer Lasso 6 db transactions Jonathan Schwarz écrit: > Olivier - > > I have read and reread what your referenced thread stated - I am still a little unsure about its use - one of the 3 methods has been 'protected' and I can't see the tag at all, of the other two the 3rd one sounded promising... just not clear on implementation, could you give an example of an Inline -Add that uses SCOPE_IDENTITY()? > > Thanks!!! > It depend on what you want to do (your sql queries) and how you need to execute them. --- Olivier Miossec AIM/ichat omiossec -- http://www.lasso-developpeur.net -- Need a Lasso consultant ? (Lasso, Java, Databases Consultant ...) http://www.lasso-developpeur.net/oliviermiossec/ -- ------------------------------ Lasso Support: http://support.omnipilot.com/ Search the list archives: http://www.listsearch.com/lassotalk.lasso Manage your list subscription: http://www.listsearch.com/lassotalk.lasso?manage -- ------------------------------ Lasso Support: http://support.omnipilot.com/ Search the list archives: http://www.listsearch.com/lassotalk.lasso Manage your list subscription: http://www.listsearch.com/lassotalk.lasso?manage |
«
Return to Lasso - Talk
|
1 view|%1 views
| Free forum by Nabble | Edit this page |