|
WSupported on Windows
|
USupported on Unix
|
VSupported on OpenVMS
|
NSupported in Synergy .NET
|
value = %SSC_DESCSQL(dbchannel, dbcursor, numvars, description)
Return value
value
This function returns SSQL_NORMAL (success) or SSQL_FAILURE (failure). (i)
Arguments
dbchannel
An internal database channel previously initialized using %SSC_INIT and connected by %SSC_CONNECT. (n)
dbcursor
The ID number of an open database cursor (opened with %SSC_OPEN). This must be in the range 1 through the number specified by maxcur when the database channel was initialized (with %SSC_INIT). (n)
numvars
The maximum number of variables that can be returned in the description array. (n)
description
A returned record description. See the ssc_desc record in Examples below for the record layout. (a)
Discussion
This routine provides a way of finding out the number of variables you should define with %SSC_DEFINE, and describes a SQL statement associated with the specified cursor.
With %SSC_DEFINE you must define the same number of variables as are defined by the number of SELECT columns, although you may not always know this number ahead of time. For example, when you use a SELECT * statement, %SSC_DESCSQL can determine the number of columns returned.
The variables in the description argument (e.g., the var_descs array in Examples below) are loaded only if the array is big enough to represent all the columns in the SELECT statement.
|
The data fields returned are the returned values from the database and do not always make sense. For example, Oracle won’t always return information in var_len for an integer that is 1, 2, 4, or 8 bytes. |
The following example demonstrates the use of %SSC_DESCSQL to dynamically describe the result set of an arbitrary SQL SELECT command (up to 8 columns in this case). The var_descs array holds the metadata for each column, including name, type, and length. Because %SSC_DEFINE requires that the number of passed variables exactly match the number of columns in the result set, the program includes a CASE block with multiple %SSC_DEFINE calls to handle different column counts. For simplicity, all returned values are treated as strings.
main
.include "CONNECTDIR:ssql.def"
.define MX_VARS ,8
record
sqlp ,a100, "SELECT DEPTNUM, DEPTNAME, salary, hrdate FROM ORG1"
sts ,i4
dbchn ,i4, 1 ;Use database channel #1
ttchan ,i4, 0
cur1 ,i4 ;SQL cursors
user ,a100
dbid ,i4
ix ,i4
cmdlin ,a255
argcnt ,i4
argpos ,[7]i4
arglen ,[7]i4
vara ,[MX_VARS]a255
returnedRow ,a1023
record ssc_desc
var_nmbr ,d3
group var_descs ,[MX_VARS] a
grp_var_name ,a30
grp_var_type ,d2 ;Possible var_types are the following:
grp_var_len ,d5 ; 0-integer
grp_var_dec ,d2 ; 1-char
endgroup ; 2-number
; 3-null-terminated char
; 4-packed decimal
; 5-zoned decimal
; 8-float
; 9-varchar
; 10-large binary object (blob)
; 11-large character object (clob)
; 12-datetime
; 80-varlen blob
; 81-Unicode char UTF-8 format
; 82-Unicode char UTF-16 format
; 83-Unicode char UCS-2 format
; 84-Unicode char UCS-4 format
; 85-Unicode LOB UTF-8 format
; 86-Unicode varchar UTF-8 format
; 87-Unicode varchar UTF-16 format
; 88-Unicode varchar UCS-2 format
; 89-Unicode varchar UCS-4 format
; 98-ROWID
; 99-binary
; If var_type is an integer, var_len
; will always return the value of 10
;
record sqlvar
var_name ,a30
var_type ,d2
var_len ,d5
var_dec ,d2
proc
open(ttchan,o,"TT:")
xcall getlog("SQL_CONNECT",user, ix) ;Get connect string.
if (.not.user)
begin
writes(ttchan,"Error: No connect string defined. Set SQL_CONNECT.")
stop
end
writes(ttchan,"Parsing command line.")
xcall cmdln(cmdlin,argcnt,argpos,arglen)
if (argcnt > 7)
begin
writes(ttchan,"Error: Too many arguments.")
stop
end
for ix from 1 thru (argcnt - 1)
begin
if (cmdlin(argpos[ix]:arglen[ix]) .eqs. "-c" || cmdlin(argpos[ix]:arglen[ix]) .eqs. "--command")
sqlp = cmdlin(argpos[ix+1]:arglen[ix+1])
end
writes(ttchan,"Command:" + %atrim(sqlp))
writes(ttchan,"Connect String: " + %atrim(user))
ix = %option(48,1)
if ((sts = %ssc_init(dbchn)) == SSQL_NORMAL)
begin
writes(ttchan,"Connecting to database. Please wait.")
if ((sts = %ssc_connect(dbchn, user)) != SSQL_NORMAL) exit
if ((sts = %ssc_getdbid(dbchn,dbid)) != SSQL_NORMAL) exit
if (dbid == 12) ;If SQL Server, set database to "pubs".
begin
if (%ssc_cmd(dbchn, cur1, SSQL_USEDB, "pubs")) exit
end
if (sqlp(1,7).eqs.'SELECT ')
begin
if ((sts = %ssc_commit(dbchn, SSQL_TXON)) != SSQL_NORMAL)
goto err_exit
if (sts = %ssc_open(dbchn, cur1, sqlp, SSQL_SELECT) != SSQL_NORMAL)
goto err_exit
if ((sts = %ssc_commit(dbchn, SSQL_TXOFF)) != SSQL_NORMAL)
goto err_exit
if (sts = %ssc_descsql(dbchn, cur1, MX_VARS, ssc_desc))
goto err_exit
writes(ttchan, "Columns (" + %string(var_nmbr) + "):")
for ix from 1 thru var_nmbr ;Display columns
begin
sqlvar = var_descs(ix)
writes(ttchan, "COL #" + %string(ix) + ": " + var_name +
& " Type:" + %string(var_type) +
& " Len:" + %string(var_len) + "." + %string(var_dec))
end
writes(ttchan, "Data:")
returnedRow = ""
for ix from 1 thru var_nmbr
begin
sqlvar = var_descs(ix)
returnedRow = %atrim(returnedRow) + %atrim(var_name) + ' | '
end
writes(ttchan, %atrim(returnedRow))
case var_nmbr of
begincase
1: sts = %ssc_define(dbchn, cur1, var_nmbr, vara[1])
2: sts = %ssc_define(dbchn, cur1, var_nmbr, vara[1], vara[2])
3: sts = %ssc_define(dbchn, cur1, var_nmbr, vara[1], vara[2], vara[3])
4: sts = %ssc_define(dbchn, cur1, var_nmbr, vara[1], vara[2], vara[3], vara[4])
5: sts = %ssc_define(dbchn, cur1, var_nmbr, vara[1], vara[2], vara[3], vara[4], vara[5])
6: sts = %ssc_define(dbchn, cur1, var_nmbr, vara[1], vara[2], vara[3], vara[4], vara[5], vara[6])
7: sts = %ssc_define(dbchn, cur1, var_nmbr, vara[1], vara[2], vara[3], vara[4], vara[5], vara[6], vara[7])
8: sts = %ssc_define(dbchn, cur1, var_nmbr, vara[1], vara[2], vara[3], vara[4], vara[5], vara[6], vara[7], vara[8])
endcase
if (sts != SSQL_NORMAL)
goto err_exit
while ((sts = %ssc_move(dbchn, cur1, 1)) == SSQL_NORMAL)
begin
returnedRow = ""
for ix from 1 thru var_nmbr
begin
returnedRow = %atrim(returnedRow) + %atrim(vara[ix]) + ' | '
end
writes(ttchan, %atrim(returnedRow))
end
end
sts = %ssc_close(dbchn, cur1)
end
err_exit,
close ttchan
endmain
