Input & Output Variables with Stored Procedures

[Kelly Orr]

Can someone give me an example and how to call stored procedures with
input and output variables? This is a new capability with 5.5 and I
can't seem to make it work in the manner the online help shows. I know
I can concantenate a string for input and get a result set in the record
layout, but that is not what I want. The online help says you can use
ouput parameters and return codes from stored procedures.

Thanks,

--
Kelly Orr
GoldTech Computer Services, Inc.
http://www.goldtechservices.com
478 Pistol Club Road
Easley, South Carolina 29640
864-294-0138 Voice
864-246-1454 Fax

[James Cooke]

search for stored procedures on Clarionfoundry

--
--
Kind regards,
James Cooke
http://www.clarionfoundry.com - a concise technical reference for Clarion
Programmers
http://63.99.156.24/
=========================================

"Kelly Orr" <ke...@goldtechservices.com> wrote in message
news:3D331DAE...@goldtechservices.com...

[Kelly Orr]

James,

Unfortunately, I already looked there. The solution given was to do it the
old way where you have to declare a table with the same layout as the output
from the stored procedure and do a NEXT() after you do the PROP:SQL statement
to call the stored procedure. I have no problem making that way work. In the
documenation for 5.5, it states you can use input and output parameters with
it. This was not available before 5.5. I have done it exactly as the docs
state and it never does the bind of the parameters and Sql Server says it's
invalid syntax. Here is how the docs have it:

Example:
PROGRAM
MAP
CallProc(STRING)
END
MyFile FILE,DRIVER(‘MSSQL’)
Record RECORD
c LONG
. .
Ret LONG
Out STRING(10)
CODE
BIND(‘RetCode’, Ret)
BIND(‘Out’, Out)
CallProc(‘&RetCode = CALL StoredProcTest(‘’1'’,&Out)’)
MESSAGE(Return value of StoredProcTest =’ & Ret)
MESSAGE(Output parameter of StoredProcTest =’ & Out)
CallProc PROCEDURE(Str)
CODE
MyFile{PROP:SQL} = Str
Note: The above example shows how to return an output parameter.
For a more specific example tailored to MSSQL, refer to the MSSQL
Accelerator chapter.

[James Cooke]

did you look at
http://www.clarionfoundry.com/Pages/paragraph.aspx?BodyPK=9KBQf982Vi0IkDzvT3
It

This is a C5.5 egg-sample I think.

--
--
Kind regards,
James Cooke
http://www.clarionfoundry.com - a concise technical reference for Clarion
Programmers
http://63.99.156.24/
=========================================

"Kelly Orr" <ke...@goldtechservices.com> wrote in message

news:3D333B45...@goldtechservices.com...

[Pablo Sánchez]

Kelly , take a look at the programmer´s guide.
There´s an example for that with MSSQL which should
be applicable to odbc.
--
Pablo Sánchez
pab...@adinet.com.uy

[Parker MacDonald]

Kelly:

Are you trying to return data from the stored procedure like an average or
some other calculated amount or are you more interested in getting back a
return code from the SP like a success or failure indicator?

Regards

Parker

[Lesley Dean]

Hi Kelly,

I don't know whether you're getting an error or just not getting the result,
but some things to consider which may help in getting to the answer are:

1. Whereever you need to pass strings in a sql statement, use <39> instead
of ' - I have found this very useful.
2. Try calling the procedure straight - not passing the statement to a proc:
MyFile{prop:sql} = '&RetCode = CALL StoredProcTest(<39>1<39>,&Out)'
! then check for errorcode() = 90
if errorcode() = 90 then stop('SQL error occurred: ' & fileerrorcode() & ' '
& fileerror()).
! Run a trace on the driver you're using, and see if any errors show up
there.
3. These may all be obvious, but also check that if you call the procedure
directly in the backend, that it does in fact return something - preferrably
the desired result! (Use Sql Query Analyser if you're using MSSQL!).

Just a few thoughts!
Hope it helps!
--
----------------------------
Lesley Dean
CapeSoft
Web: www.capesoft.com

"Kelly Orr" <ke...@goldtechservices.com> wrote in message

news:3D333B45...@goldtechservices.com...

[Kelly Orr]

Hi Lesley,

I have no problems doing it the old way where you issue a next to get the result set.  I have modified the proc and it now has an output variable for the results instead of issueing a select to retrieve them.  The proc works if called by another one, but if I try to do it the new way using an output variable, it gives a syntax error in SQL server.  This is the way I am doing it:

  BIND('DOG_ID',dog:ID)
  IF ERRORCODE()
    IF ERRORCODE() = 90
      MESSAGE(FILEERRORCODE() & ':' & FILEERROR())
    ELSE
      MESSAGE(ERRORCODE() & ':' & ERROR())
    END
  END
  BIND('EXPENSES',loc:Expenses)
  IF ERRORCODE()
    IF ERRORCODE() = 90
      MESSAGE(FILEERRORCODE() & ':' & FILEERROR())
    ELSE
      MESSAGE(ERRORCODE() & ':' & ERROR())
    END
  END
  SPFile{PROP:SQL} = 'CALL USP_DOG_EXPENSES (&DOG_ID,&EXPENSES)'
 
When the last statement is executed, the error in the trace/log file is
Executing Statement 13d51a8:CALL USP_DOG_EXPENSES (&DOG_ID,&EXPENSES)
Error Occurred: 37000[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '&'.
 Time Taken:0.00 secs
SET_PROPERTY(SPFILE 2) File Error: Line 1: Incorrect syntax near '&'. Time Taken: 0.00 secs

It does not look like the bind is working, however I get no errors on the bind.  I have also tried it with the [IN] and [OUT] with the variable names and there was no difference.

[The Squire]

Hi Kelly,

I am also trying to achieve this at the moment and I am getting exactly
the same error as you are. If you or anybody else can find a solution
to this I would be very grateful.

Many thanks

Lee.

[Lesley Dean]

Hi Kelly, and Lee,

 

I have to admit I have not done enough of these scenarios to call it good experience! I was hoping someone else would offer a solution! I only have one procedure returning an output variable. This procedure does not have any input variable. My thoughts at this point are that possibly the procedure itself is expecting a different parameter or syntax. What does your CREATE PROCEDURE script look like - ie, the definition of your procedure? I have not included my working procedure here, as it does in fact return an error in the trace but not in my error check, but it is returning the desired result! I think I could improve on it a bit though!

 

The other question was, if you call it with a specific value for the input parameter, does it still return an error? ie. SPFile{PROP:SQL} = 'CALL USP_DOG_EXPENSES (1234,&EXPENSES)'

 

HTH

--
----------------------------
Lesley Dean
CapeSoft
Web: www.capesoft.com

"Kelly Orr" <ke...@goldtechservices.com> wrote in message news:3D341E9D...@goldtechservices.com...

[Kelly Orr]

Hi Dean,

Here's the procedure.  I know the proc works becuase I created another dummy procedure to call it like I want the Clarion app to call it.

CREATE PROCEDURE USP_DOG_EXPENSES
    @DOG_ID      INT,
    @EXPENSES    DECIMAL(9,2) OUTPUT
AS
SELECT @EXPENSES = SUM(CostOfVisit)
  FROM VetVisits
 WHERE DogID = @DOG_ID
GO

Before you say anything about using a DECIMAL variable as a return, I have also done this using a string just in case that was the problem, and there was no difference.  :)

Lesley Dean wrote:

Hi Kelly, and Lee, I have to admit I have not done enough of these scenarios to call it good experience! I was hoping someone else would offer a solution! I only have one procedure returning an output variable. This procedure does not have any input variable. My thoughts at this point are that possibly the procedure itself is expecting a different parameter or syntax. What does your CREATE PROCEDURE script look like - ie, the definition of your procedure? I have not included my working procedure here, as it does in fact return an error in the trace but not in my error check, but it is returning the desired result! I think I could improve on it a bit though! The other question was, if you call it with a specific value for the input parameter, does it still return an error? ie. SPFile{PROP:SQL} = 'CALL USP_DOG_EXPENSES (1234,&EXPENSES)' HTH--
----------------------------
Lesley Dean
CapeSoft
Web: www.capesoft.com

"Kelly Orr" <ke...@goldtechservices.com> wrote in message news:3D341E9D...@goldtechservices.com...Hi Lesley,

[Lesley Dean]

Hi Kelly!

 

After a good hour or 2 or fiddling with the procedures, I decided there either needs to be much improved detailed documentation on this subject, or there is a bug in the driver! I took my working procedure, and I took a personalised version of your procedure, and tried to compare and make them both work. I found that I got mine to work after making sure the dummy file passing the statement was opened in 10h mode (read-only), and that the bindings were correct, but could not get the other one to work.

 

First let me show you my two examples:

SQL Procedures:

1. Personalised version of your proc:

CREATE PROCEDURE ds_testproc
    @test_in     VARCHAR(50),
    @test_out    DECIMAL(9,2) OUTPUT
AS
SELECT @test_out = SUM(UnitPrice)
  FROM Products
 WHERE Description = @test_in

GO

2. My working proc:

CREATE procedure ds_GetServerCase @CaseVar int output
as
if 'a' = 'A'
  set @CaseVar = 0
else
  set @CaseVar = 1
return @CaseVar
GO

Clarion code:

! Example 1 - 1 input parameter, 1 output parameter

      dummy{prop:sql} = '&MyVar = CALL ds_testproc(&test_in,&test_out)'
      if errorcode() = 90 then stop('SQL Error: ' & fileerrorcode() & ' ' & fileerror()).
      if errorcode() then stop('Error: ' & errorcode() & ' ' & error()).
      message('Result_MyVar = ' & MyVar)
      message('Result_test_out = ' & test_out)

! Example 2 - 0 input, 1 output parameter

      dummy{prop:sql} = '&MyVar = CALL ds_GetServerCase(&Var2)'
      if errorcode() = 90 then stop('SQL Error: ' & fileerrorcode() & ' ' & fileerror()).
      if errorcode() then stop('Error: ' & errorcode() & ' ' & error()).
      message('Result_MyVar = ' & MyVar)
      message('Result_var2 = ' & Var2)

My observasions are:

1. what we are looking for in the trace is that when the stored procedure is called it takes this format in the trace:

Preparing Statement 13212c0 : {?=CALL ds_GetServerCase(?)} Time Taken:0.00 secs
Notice that "?" are used where our variables are placed. Interestingly enough, this proc is not actually definied to take an input paramter, but yet it requires that I put something there. I do in fact see the following error in the trace, but it is not returned as an error in my application, and the required value is set.

Error Occurred: 37000[Microsoft][ODBC SQL Server Driver][SQL Server]Procedure 'ds_GetServerCase' expects parameter '@CaseVar', which was not supplied.

 

2. The format in which your proc is called in the trace is:

Executing Statement 13219a4:&MyVar = Call ds_testproc(&test_in, &test_out)

Error Occurred: 37000[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '&'.

The driver doesn't seem to recognise that we are calling a sql stored proc with unknown variables! Notice the lack of curly brackets and "?"!! (very technical language I know <g>!).

 

3. I then tried a variety of different statements to try and get the same "syntax" in the trace as the "ds_GetServerCase" proc for the "ds_testproc". The only way I managed to get the "right" looking trace was by calling the proc like this:

dummy{prop:sql} = '&MyVar = CALL ds_testproc()'    ! NO PARAMETERS!
The trace returned this:

Preparing Statement 1320b6c : {?=CALL ds_testproc} Time Taken:0.00 secs
And of course, as expected, this:

Error Occurred: 37000[Microsoft][ODBC SQL Server Driver][SQL Server]Procedure 'ds_testproc' expects parameter '@test_in', which was not supplied.

4. I then added a parameter, and it reverted back to the original trace errors and syntax! I then tried calling the proc with a 3rd parameter, to see if it would generate a different error, and nope, it didn't! I tried a variety of options which returned these traces:
Executing Statement 1320b6c:&MyVar = Call ds_testproc(&test_in,&test_out,&Var2)

Error Occurred: 37000[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '&'.

Executing Statement 13219a4:Call ds_testproc(&test_in, &test_out)

Error Occurred: 37000[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '&'.

Executing Statement 13219a4:&MyVar = Call ds_testproc(&test_in)

Error Occurred: 37000[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '&'.

Executing Statement 13219a4:&MyVar = Call ds_testproc('Fruit',&test_out)

Error Occurred: 37000[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '&'.

Executing Statement 1320b6c:CALL ds_testproc('Fruit',&test_out)
Error Occurred: 37000[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'Fruit'.

5. At this point I decided to quit trying, although I am really curious to know why, how, etc. If anybody else could offer a solution I would be very happy! It seems ridiculous that my proc works, and yours doesn't, and therefore, my procedure does not help anybody!! <g>

 

I hope this makes sense, and that you find someone out there who can clarify this one for us! If you do, please pass on your findings to me.

Thanks.

[Parker MacDonald]

Kelly:

Mix in the proper amount of single quotes and you should be in business!

SPFile{PROP:SQL} = 'CALL USP_DOG_EXPENSES ('''&DOG_ID''',&EXPENSES)'

You could also use the previously suggested <39> to make the text a little
more readable.

Regards

Parker

Kelly Orr <ke...@goldtechservices.com> wrote:

>
>--------------D5E770C6A5CF75B02F4BFF35
>Content-Type: text/plain; charset=us-ascii
>Content-Transfer-Encoding: 7bit

[Lesley Dean]

Hi Parker,

Do you have a working example you could show us? Your suggestion doesn't
seem to work in my tests. If you have a working example you could show us,
that would be greatly appretiated!

Thanks.

--
----------------------------
Lesley Dean
CapeSoft
Web: www.capesoft.com

"Parker MacDonald" <Parker_M...@uccb.ns.ca> wrote in message
news:3d39870c...@news.softvelocity.com...

[Parker MacDonald]

Lesley:

First off I made a typo in the code change I suggested it should have been:

SPFile{PROP:SQL} = 'CALL USP_DOG_EXPENSES ('''&DOG_ID&''',&EXPENSES)'

Notice the & I left out after DOG_ID

Now for my "working example"

The stored proc looks like this:

CREATE proc usp_sect_enrollment
@acad_year int,
@acad_session char(2),
@course_no varchar(16),
@section_no int,
@enrollment int OUTPUT
AS
select @enrollment = count(*)
from dbo.stu_acad_detail with(index=by_cor_section)
where acad_year = @acad_year and
acad_session = @acad_session and
course_no = @course_no and
section_no = @section_no and
course_type = 'R' and
course_status = 'A'

I pass the year,session,course number and course section to the SP and get
back the number of students enrolled.

The clarion code looks like this:

BIND('OUT',LOC:ENROLLMENT)
SECTIONS{Prop:SQL}='CALL usp_Sect_Enrollment ('''&SEC:ACAD_YR&''',' |
& ''''&SEC:ACAD_SESSION&''','|
& ''''&SEC:COURSE_NO&''',' |
& ''''&SEC:SECTION_NO&''',' |
& '&OUT)'
IF ERRORCODE()
message(error() & errorfile())
END
UNBIND('OUT')

now LOC:ENROLLMENT has the value I need

All of the quotes are single quotes.

Hope this helps a little

Regards

Parker