stored procedure %SYSTEM.SQL_TableExists

Question

Question

CM Wang · Jul 5, 2017

#Caché #SQL

"SELECT %SYSTEM.SQL_TableExists('table name') "could work as expected under SQL shell,

but for "CALL %SYSTEM.SQL_TableExists('table name')" does not work (not any error reported, it just show nothing).

Is there any reason why CALL could not be applied to a stored procedure?

Thanks.

Discussion (7)0

Log in or sign up to continue

Vitaliy Serdtsev · Jul 5, 2017

SAMPLES>d $system.SQL.Shell()
SQL Command Line Shell
----------------------------------------------------
 
The command prefix is currently set to: >.
Enter q to quit, ? for help.
SAMPLES>>?=CALL %SYSTEM.SQL_TableExists('Sample.Person')
1.      ?=CALL %SYSTEM.SQL_TableExists('Sample.Person')
 
 
executing statement with parameter values: set %tResult=%tStatement.%Execute()
 
 
Output Values:
 
 0. 1
statement prepare time(s)/globals/lines/disk: 0.0025s/14/862/0ms
          execute time(s)/globals/lines/disk: 0.0003s/7/179/0ms
                          cached query class: %sqlcq.SAMPLES.cls2
---------------------------------------------------------------------------
SAMPLES>>

2 0

Alexander Koblov · Jul 5, 2017

Kyle, please notice that indeed

CALL %SYSTEM.SQL_TableExists('table name')

shows nothing -- no result is returned.

Whereas

?= CALL %SYSTEM.SQL_TableExists('table name')

prints boolean result 1 or 0 depending on whether 'table name' exists.

0 0

score 3 · Answer 1 · 2017-07-05T08:52:45-04:00

Vitaliy Serdtsev · Jul 5, 2017

?=CALL %SYSTEM.SQL_TableExists('table name')

3 0

score 0 · Answer 2 · 2017-07-05T09:23:05-04:00

Vitaliy Serdtsev · Jul 5, 2017

Thanks for minus. My legs more will not be here.

0 0

score 0 · Answer 3 · 2017-07-05T09:14:20-04:00

Works for me:

[SQL]SAMPLES>>CALL %SYSTEM.SQL_TableExists('table name')

1. CALL %SYSTEM.SQL_TableExists('table name')



statement prepare time(s)/globals/lines/disk: 0.0398s/1847/15479/4ms

          execute time(s)/globals/lines/disk: 0.0007s/7/159/0ms

                          cached query class: %sqlcq.SAMPLES.cls8

So I don't know what Vitaliy is on about. What's your version?

score 0 · Answer 4 · 2017-07-05T10:22:51-04:00

if "ReturnResultsets" specified in the SqlProc definittion,

could I just directly use "call SqlProcName" instead of "? = call SlqProcName" ?

score 0 · Answer 5 · 2017-07-05T11:14:30-04:00

Alexander Koblov · Jul 5, 2017

Yes.

0 0