MS SQLServer Lasso 6 db transactions

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

MS SQLServer Lasso 6 db transactions

Jonathan Schwarz
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
Reply | Threaded
Open this post in threaded view
|

Re: MS SQLServer Lasso 6 db transactions

Olivier Miossec

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
Reply | Threaded
Open this post in threaded view
|

Re: MS SQLServer Lasso 6 db transactions

Jonathan Schwarz
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
Reply | Threaded
Open this post in threaded view
|

Re: MS SQLServer Lasso 6 db transactions

Olivier Miossec
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
Reply | Threaded
Open this post in threaded view
|

Re: MS SQLServer Lasso 6 db transactions

Jonathan Schwarz
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
Reply | Threaded
Open this post in threaded view
|

Re: MS SQLServer Lasso 6 db transactions

Jonathan Schwarz
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
Reply | Threaded
Open this post in threaded view
|

Re: MS SQLServer Lasso 6 db transactions

Olivier Miossec
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
Reply | Threaded
Open this post in threaded view
|

Re: MS SQLServer Lasso 6 db transactions

Jonathan Schwarz
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