With the help of the QSQCHKS API, you can easily and quickly find where errors occur.
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
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 cSQL#sr 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!
More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.
TRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now! Request Now.
Forms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.
Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:
Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.
IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn:
LATEST COMMENTS
MC Press Online