Syntax Check SQL Statement (QSQCHKS) API
- Dettagli
- Categoria: Faq AS400
- Pubblicato Giovedì, 24 Novembre 2011 07:56
- Scritto da Super User
- Visite: 5598
A lot of people run SQL statements often, but few of them can find the place where an error occurs in those statements. In this article, I'll show you how to detect the position of these errors by using the Syntax Check SQL Statement (QSQCHKS) API , which is documented here.
Several years ago, I discovered this API, but there was no example of how to use it. Later, I discovered one curious undocumented feature that was not described in the API documentation. So today I decided to share this information with you.
According to IBM documentation, this API uses an internal parser that can scan the string of the SQL statement, using Interactive SQL syntax rules or rules of the language you specify. The result will show the column and row error position in the SQL statement string along with some additional information.
The sample program below shows how to use this API. Note that I used comments in certain blocks of my code. Later in this article, I will explain in detail. My code is divided into two parts: definitions of some structures for this API and the code for using it. I kept this code as it was written many years ago when there was no free-format source
* ---- Definitions
D vSTMT S 16322A
D Stmt DS QUALIFIED
D Len 10I 0
D NumRec 10I 0 INZ(1)
D Lang 10A INZ('*NONE')
D inSize 10I 0 INZ(%size(QSQ_Stmt))
D nRec 10I 0
DQSQ_Options DS QUALIFIED
D NumKeys 10I 0 INZ(1)
D key01 10I 0 INZ(1)
D key01len 10I 0 INZ(10)
D key01data 10A INZ('*SYS')
DQSQ_Stmt DS QUALIFIED
D MsgfName 1 10
D MsgfLib 11 20
D NumStmts 21 24B 0
D StmtI likeDS(StmtT)
DStmtT DS QUALIFIED
D lenStmt 1 4B 0
D rowBegin 5 8B 0
D colBegin 9 12B 0
D rowEnd 13 16B 0
D colEnd 17 20B 0
D rowErr 21 24B 0
D colErr 25 28B 0
D msgID 29 35
D state 36 40
D lenMsg 41 44B 0
D msgData 45 145A
D msgData 45 145A
DQUSEC DS 116 INZ
D QUSBPRV 1 4B 0 Bytes Provided
D QUSBAVL 5 8B 0 Bytes Available
D QUSEI 9 15 Exception Id
D QUSERVED 16 16 Reserved
D QUSMSGDTA 17 116 Message Data
*--- Temporary variable -----------------------
D s80 S 80A
*------------------------------
* Checking of the SQL statement
C cSQLsr BEGsr
* 1) Syntax Check SQL Statement
* 1-1) Initialization
C Eval Stmt.Len = %len(%trimr(vSTMT))
*>> unexpected change ------------------------------
C Stmt.Len IFlt 80 IF1
C Z-add 80 Stmt.Len
C ENDif ENDif1
*--------------------------------------------------
C MOVE *BLANK QUSEI
* 1-2) The Len must be greater than *ZERO
C Stmt.Len IFle *ZERO IF2
C Eval s80 = 'The local statement is empty'
C LEAVEsr
C ENDif ENDif2
* 1-3) Run api
C CALL 'QSQCHKS' 98
C PARM vSTMT In>Statement
C PARM Stmt.Len In>Len
C PARM Stmt.NumRec In>
C PARM Stmt.Lang In>
C PARM QSQ_Options In>
C PARM QSQ_Stmt Out>DS result
C PARM Stmt.inSize In>size DS
C PARM Stmt.nRec Out>num records
C PARM QUSEC Out>Error
* 1-4) And the result
C SELECT SELECT1
* - External error
C *In98 WHENeq *ON
C LEAVEsr
* - Api Error
C QUSEI WHENne *BLANK
* ... Retrieve msg from QCPFMSG *msgf using
* QUSEI and QUSMSGDTA
C LEAVEsr
* - SQL Error
C WHEN QSQ_Stmt.StmtI.msgID <> *BLANK
* ... Retrieve msg from QSQLMSG *msgf using
* QSQ_Stmt.StmtI.msgID and QSQ_Stmt.StmtI.msgData
C IF QSQ_Stmt.StmtI.colErr > *ZERO IF3
* ... Here is the error position
C ENDif ENDif3
C LEAVEsr
C ENDsl ENDsl1
C ENDsr
In the middle of the code, you see the call to the QSQCHKS API. The first and second parameters are the SQL statement string and its length. The third parameter is the number of records—in our case, it's 1. If you want to use standard SQL rules for checking, you can set the fourth parameter to '*NONE'. The fifth parameter is settings. I set it because I wanted to add a key for that condition according to the API documentation: '*SYS - Table names are qualified using the system naming convention in the form library/table.' The sixth and seventh parameters are the resulting data structure and its size. The last two parameters are the number of records processed (usually one) and the standard error structure.
I added this block: <<unexpected change because in some cases this API returns error SQL0901: 'Record length parameter not valid'. The solution is simply to add this block to avoid unexpected problems when the Stmt.Len is between 1 and 79 and the SQL statement is completely correct in grammatical rules but this error occurs.
This block has some history. The first time I received SQL0901, I didn't understand what it meant. The API was called several times with no error, but then suddenly the error occurred. I went deep into the Internet and found out that there was a PTF to resolve the issue of SQL construction failing with SQL0901. So I thought that there had to be something in the IBM documentation that would help to explain. And I read the QSQCHKS documentation again from V3R1 till now. I found interesting information: "{Left,Right} margin. The {left,right} margin for the source. This parameter is only valid if language is PL/I or C and the valid values are from 1 to 80." So I guessed that somewhere deep in QSQCHKS there's mention of margin 80. With debug, I checked it and discovered that Stmt.Len needs to be greater than 80 in order to avoid exception error SQL0901.
I hope that this information helps you avoid errors when you use this API!
