SQLROWSET with CALL SP() gets error 90 syntax error

1

Hi
I know it has been discussed before but I can’t really find a solution to my particular challenge.
The challenge actually consists of two things.
The syntax for the call and the smartest way to solve it.
I need to call a MSSQL SP with a number of parameters. The procedure returns a JSON string.
It is not possible to change the SP to a view since it is not a DB I have control over.
I have created a temp file with a blob field.
And in my world the call should be like this:

SQL{PROP:SQLROWSET = 'CALL SP(2,3)'
! Check for error
NEXT(SQL)
! Check for error
Move data from blob to text etc

But no matter what I do with SQLROWSET and the call to my SP I get error 90 + a description of a syntax error.
I have read the documentation over and over again

2

Hi Niels,

A couple of niggly things to start:
1 you are missing the closing brace } (probably just a typo)
2 You should be using PROP:SQL, not PROP:SQLRowSet. The fact that you are using Call rather than NoResultCall tells Clarion that it needs to provide a place for the result.

More usefully, you should tell us:
if your SP is a function, not a procedure, and it returns a single JSON string as output? You don’t have to loop through a resultset, it is a single string?

If that is that case, then you are probably best off using the syntax you can find on the “Using Embedded SQL” page in the help, just above “The above example shows how to return an output parameter”.

so something like:

myJSON cstring(4000)
  code
    bind('JSONstring',myJSON)
    anysqlfile{PROP:SQL} = '&JSONstring = call sp(2,3)'

HTH

http://clarion.help/doku.php?id=using_embedded_sql.htm

2 Likes
3

I checked it out, and it works perfectly for me in Oracle:

Here’s my function on the server:

create or replace function get_casenote(p_case_id IN number,p_date IN varchar2) return varchar2 IS
rv varchar2(4000);
  begin
select comments into rv
from case_note
where case_id = p_case_id and trunc(date_created) = to_date(p_date,'yyyy-mm-dd');
 return rv;
 end;

And here’s my program (dual is a table guaranteed to exist in Oracle – I don’t need a real table):

  PROGRAM

glo:connectstring cstring(100)

dual              FILE,DRIVER('Oracle'),OWNER(glo:connectstring),NAME('DUAL'),PRE(D)
record            RECORD
dummy string(1)
                    END
                END

  MAP
  END

mycomment cstring(4000)

    CODE
        glo:connectstring = 'myconnectstring'      
        open(dual)
        if error() then 
            stop(error())
        .
        bind('comment',mycomment)     
        dual{PROP:SQL} = '&comment = CALL get_casenote(139697,''2024-11-14'')'
        if error() then 
            stop(FILEERROR())
        ELSE
            message(mycomment)
       end

And if you set up a trace, what you will see sent to the server is:

04300H(1) 10:35:04.423 Parsing T9 Cursor 73198864 : BEGIN :1 := get_casenote(139697,'2024-11-14');END;  Return Code: 0 Time Taken:0.00 secs
04300H(1) 10:35:04.423 Binding field 1 as type VARCHAR(4000) T9 Cursor 73198864  Return Code: 0 Time Taken:0.00 secs
4

Hi Jon

I changed from SQLROWSET to SQL, created a MSSQL Scalar Function that calls my SP, returning the JSON string and it works exactly as I want it to.
THANKS!

5

Hi Niels, don’t use
SQL{PROP:SQLROWSET = ‘CALL SP(2,3)’ (by the way, a } is missing in your example).
Just use
SQL{Prop:SQLRowSet} = ‘exec sp 2, 3’ or even better
SQL{Prop:SQLRowSet} = ‘exec sp @FirstParameter = 2, @SecondParameter = 3’ where First and Second parameter are the name you set in your stored procedure.

2 Likes
6

Hi Mauricio

Thanks for the post. I have tried all possible combinations of calls to my SP - with and without parentheses and CALL/EXEC but keep getting the same error.
I think I will give it another try, because it will make everything a little easier for me.

7

So, you’re still getting the error, or not?

I don’t think you have told us what the error is?

What do you get when you use FileError()…

IF ERRORCODE()
MESSAGE(FILEERROR())
END

8

Finally I have got it working exactly as I want it to.
Two things I have learned.
One that SQLROWSET can take my SQL code exactly as if I want to use it in SSMS
Two that to get the result back in full length I need to put the SQL query result in a variable VARCHAR(MAX) and then select this variable.
Thanks to everyone for the input.

1 Like