SQL Related Articles: Calling stored procedure with return OUT parameters 2003-09-01 -- Nardus Swanevelder Newsgroups: comp.lang.clarion
Thanks Jim for the explanation.
The advantages of using Ident_Current is that it will give you the
Ident number per table and you don't need to call a store procedure.
The only time this will not work is if 2 people insert a record into
the same table at exactly the same time.
This is however a problem for me so I started playing with store
procedures as you suggested.
The First part of my store procedure looks like this:
CREATE PROCEDURE SS_CreateQuote
--Input parameters
@Site Char(4) = '',
@Branch Char(20)= '',
@Client Char(20)='',
--Output parameters
@NewQuoteAutoNr Int Output,
@QuoteNr Int Output,
@OutError Int Output
/*
Object: SS_CreateQuote
Description: Create a Quote and return the Identity Number to the
system.
Create Date: 3/07/2003 (dd/mm/yyyy)
Change Date: 3/07/2003
Author: Nardus Swanevelder
*/
AS
Declare @VersionNr Int
--New quote so set version to 1
Set @VersionNr = 1
--Get last saved quote number from client table
SELECT @QuoteNr = LAST_QUOTENR
FROM CLIENT
WHERE (BRANCH_CODE = @Branch) AND (CLIENT_CODE = @Client)
--Increase Last Quote nr with 1
Set @QuoteNr = @QuoteNr + 1
--Add quote
Begin Tran Q1 -- Start transaction locking
INSERT QUOTE
(SITE, BRANCH_CODE, CLIENT_CODE, QUOTE_NR, VERSION_NR)
VALUES (@Site, @Branch, @Client, @QuoteNr, @VersionNr)
IF @@Error = 0 --no error
Begin
-- Update client table with new last quote number
Begin Tran C1
Update Client
Set LAST_QUOTENR = @QuoteNr
WHERE (BRANCH_CODE = @Branch) AND (CLIENT_CODE = @Client)
If @@Error = 0
Begin
SET @NewQuoteAutoNr = Cast(SCOPE_IDENTITY() AS INT)
Commit Tran C1
Commit Tran Q1
As you can see I return the Autonumber using Scope_Identity. The
problem I had is that I could not get Clarion to generate the Call
correctly. The Call in the trace should look something like
Call(?,?,?,?,?,?) but in the trace I just got the text as I have
passed it to Prop:SQL.
To cut a long story short the problem is that the Clarion driver can
not handle a underscore "_" in the parameter name. As soon as I
changed that everything worked fine. SQL does not have a problem with
the _ but clarion or the driver can not handle the _.
Here is an example of how I called the store procedure:
Bind('ReturnQuoteIdentity',ReturnQuoteIdentity)
Bind('QuoteNr', QuoteNr)
Bind('MyRS',MyRS)
quote{prop:sql} = 'NORESULTCALL SS_CreateQuote(<39>' &
Clip(QTE:Site) & '<39>, <39>' & |
Clip(QTE:Branch_Code) & '<39>, <39>' &
Clip(QTE:Client_Code) & '<39>, ' &|
' &ReturnQuoteIdentity [OUT], &QuoteNr [OUT],
&MyRS [OUT])'
Because Clarion will replace the parameters with the actual value the
above is not a problem becasue this is what would be passed to the
driver:
quote{Prop:sql} = 'NORESULTCALL SS_CreateQuote('B000','DDNS-JHB',
'AHC' ,&ReturnQuoteIdentity [OUT], &QuoteNr [OUT], &MyRS [OUT])'
You could also called the store procedure like this:
quote{prop:sql} = 'NORESULTCALL SS_CreateQuote(&Site [IN], &BranchCode
[IN], &ClientCode [IN], &ReturnQuoteIdentity [OUT], &QuoteNr [OUT],
&MyRS [OUT])'
Remember that you will have to bind all the variables if you are using
the method above.
Hope this help someone in the future.
"jim kane"
Stored Proc in MSAccess 2004-05-05 � gary sims I was fighting getting an MSAccess backend to work with storedproc (query). This article sorted out problems and all works well now. thanks, Gary Printed November 23, 2024, 2:10 am This article has been viewed/printed 35254 times. Google search has resulted in 4807 hits on this article since January 25, 2004. |