Determine the statement type

The sqlstype.h file contains the defined integer constants for the SQL statements that can be prepared. The DESCRIBE statement returns one of these values in the SQLCODE (sqlca.sqlcode) variable to identify a prepared statement. That is, SQLCODE indicates whether the statement was an INSERT, SELECT, CREATE TABLE, or any other SQL statement.

Within the program that uses dynamic SQL statements, you can use the constants that the following table shows to determine which SQL statement was prepared.

Table 1. The constants for SQL statement types that the sqlstype.h file defines
SQL statement	Defined sqlstype.h constant	Value
SELECT (no INTO TEMP clause)	None	0
DATABASE	SQ_DATABASE	1
	Internal use only	2
SELECT INTO TEMP	SQ_SELINTO	3
UPDATE...WHERE	SQ_UPDATE	4
DELETE...WHERE	SQ_DELETE	5
INSERT	SQ_INSERT	6
UPDATE WHERE CURRENT OF	SQ_UPDCURR	7
DELETE WHERE CURRENT OF	SQ_DELCURR	8
	Internal use only	9
LOCK TABLE	SQ_LOCK	10
UNLOCK TABLE	SQ_UNLOCK	11
CREATE DATABASE	SQ_CREADB	12
DROP DATABASE	SQ_DROPDB	13
CREATE TABLE	SQ_CRETAB	14
DROP TABLE	SQ_DRPTAB	15
CREATE INDEX	SQ_CREIDX	16
DROP INDEX	SQ_DRPIDX	17
GRANT	SQ_GRANT	18
REVOKE	SQ_REVOKE	19
RENAME TABLE	SQ_RENTAB	20
RENAME COLUMN	SQ_RENCOL	21
CREATE AUDIT	SQ_CREAUD	22
	Internal use only	23–28
ALTER TABLE	SQ_ALTER	29
UPDATE STATISTICS	SQ_STATS	30
CLOSE DATABASE	SQ_CLSDB	31
DELETE (no WHERE clause)	SQ_DELALL	32
UPDATE (no WHERE clause)	SQ_UPDALL	33
BEGIN WORK	SQ_BEGWORK	34
COMMIT WORK	SQ_COMMIT	35
ROLLBACK WORK	SQ_ROLLBACK	36
	Internal use only	37–39
CREATE VIEW	SQ_CREVIEW	40
DROP VIEW	SQ_DROPVIEW	41
	Internal use only	42
CREATE SYNONYM	SQ_CREASYN	43
DROP SYNONYM	SQ_DROPSYN	44
CREATE TEMP TABLE	SQ_CTEMP	45
SET LOCK MODE	SQ_WAITFOR	46
ALTER INDEX	SQ_ALTIDX	47
SET ISOLATION, SET TRANSACTION	SQ_ISOLATE	48
SET LOG	SQ_SETLOG	49
SET EXPLAIN	SQ_EXPLAIN	50
CREATE SCHEMA	SQ_SCHEMA	51
SET OPTIMIZATION	SQ_OPTIM	52
CREATE PROCEDURE	SQ_CREPROC	53
DROP PROCEDURE	SQ_DRPPROC	54
SET CONSTRAINTS	SQ_CONSTRMODE	55
EXECUTE PROCEDURE, EXECUTE FUNCTION	SQ_EXECPROC	56
SET DEBUG FILE TO	SQ_DBGFILE	57
CREATE OPTICAL CLUSTER	SQ_CREOPCL	58
ALTER OPTICAL CLUSTER	SQ_ALTOPCL	59
DROP OPTICAL CLUSTER	SQ_DRPOPCL	60
RESERVE (Optical)	SQ_OPRESERVE	61
RELEASE (Optical)	SQ_OPRELEASE	62
SET MOUNTING TIMEOUT	SQ_OPTIMEOUT	63
UPDATE STATS...for procedure	SQ_PROCSTATS	64
	Defined for Kanji version only	65 and 66
	Reserved	67–69
CREATE TRIGGER	SQ_CRETRIG	70
DROP TRIGGER	SQ_DRPTRIG	71
	SQ_UNKNOWN	72
SET DATASKIP	SQ_SETDATASKIP	73
SET PDQPRIORITY	SQ_PDQPRIORITY	74
ALTER FRAGMENT	SQ_ALTFRAG	75
SET	SQ_SETOBJMODE	76
START VIOLATIONS TABLE	SQ_START	77
STOP VIOLATIONS TABLE	SQ_STOP	78
	Internal use only	79
SET SESSION AUTHORIZATION	SQ_SETDAC	80
	Internal use only	81-82
CREATE ROLE	SQ_CREATEROLE	83
DROP ROLE	SQ_DROPROLE	84
SET ROLE	SQ_SETROLE	85
	Internal use only	86–89
CREATE ROW TYPE	SQ_CREANRT	90
DROP ROW TYPE	SQ_DROPNRT	91
CREATE DISTINCT TYPE	SQ_CREADT	92
CREATE CAST	SQ_CREACT	93
DROP CAST	SQ_DROPCT	94
CREATE OPAQUE TYPE	SQ_CREABT	95
DROP TYPE	SQ_DROPTYPE	96
	Reserved	97
CREATE ACCESS_METHOD	SQ_CREATEAM	98
DROP ACCESS_METHOD	SQ_DROPAM	99
	Reserved	100
CREATE OPCLASS	SQ_CREATEOPC	101
DROP OPCLASS	SQ_DROPOPC	102
CREATE CONSTRUCTOR	SQ_CREACST	103
SET (MEMORY/NON)_RESIDENT	SQ_SETRES	104
CREATE AGGREGATE	SQ_CREAGG	105
DROP AGGREGATE	SQ_DRPAGG	106
onutil check index command	SQ_CHKIDX	108
set schedule	SQ_SCHEDULE	109
"set environment..."	SQ_SETENV	110
	Reserved	111
	Reserved	112
	Reserved	113
	Reserved	114
SET STMT_CACHE	SQ_STMT_CACHE	115
RENAME INDEX	SQ_RENIDX	116
CREATE SEQUENCE	SQ_CRESEQ	124
DROP SEQUENCE	SQ_DRPSEQ	125
ALTER SEQUENCE	SQ_ALTERSEQ	126
RENAME SEQUENCE	SQ_RENSEQ	127
SET COLLATION	SQ_COLLATION	129
SET NO COLLATION	SQ_NOCOLLATION	130
SET ROLE DEFAULT	SQ_SETDEFROLE	131
SET ENCRYPTION	SQ_ENCRYPTION	132
save external directives	SQ_EXTD	133
CREATE XAdatasource TYPE	SQ_CRXASRCTYPE	134
CREATE XAdatasource	SQ_CRXADTSRC	135
DROP XAdatasource TYPE	SQ_DROPXATYPE	136
DROP XAdatasource	SQ_DROPXADTSRC	137
Truncate table	SQ_TRUNCATE	138
CREATE SECURITY LABEL COMPONENT	SQ_CRESECCMP	139
ALTER SECURITY LABEL COMPONENT	SQ_ALTSECCMP	140
DROP SECURITY LABEL COMPONENT	SQ_DRPSECCMP	141
RENAME SECURITY LABEL COMPONENT	SQ_RENSECCMP	142
CREATE SECURITY POLICY	SQ_CRESECPOL	143
DROP SECURITY POLICY	SQ_DRPSECPOL	144
RENAME SECURITY POLICY	SQ_RENSECPOL	145
CREATE SECURITY LABEL	SQ_CRESECLAB	146
DROP SECURITY LABEL	SQ_DRPSECLAB	147
RENAME SECURITY LABEL	SQ_RENSECLAB	148
GRANT DBSECADM	SQ_GRTSECADM	149
REVOKE DBSECADM	SQ_RVKSECADM	150
GRANT EXEMPTIONS	SQ_GRTSECEXMP	151
REVOKE EXEMPTIONS	SQ_RVKSECEXMP	152
GRANT SECURITY LABEL	SQ_GRTSECLAB	153
REVOKE SECURITY LABEL	SQ_RVKSECLAB	154
GRANT SETSESSIONAUTH	SQ_GRTSESAUTH	155
REVOKE SETSESSIONAUTH	SQ_RVKSESAUTH	156

Tip: Check the sqlstype.h header file on your system for the most updated list of SQL statement-type values.

To determine the type of SQL statement that was prepared dynamically, your program must take the following actions:

Use the include directive to include the sqlstype.h header file.
Compare the value in the SQLCODE variable (sqlca.sqlcode) against the constants defined in the sqlstype.h file.

A sample program that executes an SPL function uses the SQ_EXECPROC constant to verify that an EXECUTE FUNCTION statement has been prepared.