Samples for coding SQL WHERE criteria in ILE RPG program when using RPG2SQL Integrator
From Support
Sample hard coded SQL Select statement which selects all fields and records from theTitles table in the sample Pubs database installed with SQL Server. This example sets a variable named SQLStmt with the SQL record selection criteria and then calls SQL_RunSQLSel to select the records from the table for reading.
*---------------------------------------------------------------------
* ** Set SQL record select statement to select all records
*---------------------------------------------------------------------
C Eval SQLStmt = 'SELECT * FROM Titles'
*---------------------------------------------------------------------
* ** Run Query to Open ADO Recordset
*---------------------------------------------------------------------
C Eval Rtn = SQL_RunSQLSel(SQL_Socket:
C SQLStmt)
Sample hard coded SQL Select statement which selects all fields and records where the book type = business from theTitles table in the sample Pubs database installed with SQL Server. This example sets a variable named SQLStmt with the SQL record selection criteria and then calls SQL_RunSQLSel to select the records from the table for reading. **Notice that the word business is surrounded with the fields called QUOT, which inserts a single quote at the beginning and end of the word business. All character criteria must be surrounded with single quotes. Numeric values do not need to be surrounded with single quotes.
*---------------------------------------------------------------------
* ** Set SQL record select statement to select all records where book type = business
*---------------------------------------------------------------------
C Eval SQLStmt = 'SELECT * FROM Titles where type = ' + QUOT +
C 'business' + QUOT
*---------------------------------------------------------------------
* ** Run Query to Open ADO Recordset
*---------------------------------------------------------------------
C Eval Rtn = SQL_RunSQLSel(SQL_Socket:
C SQLStmt)
Sample soft coded SQL Select statement which selects all fields and records where the book type = the value passed into variable name sbusiness from the Titles table in the sample Pubs database installed with SQL Server. This example sets a variable named SQLStmt with the SQL record selection criteria and then calls SQL_RunSQLSel to select the records from the table for reading. **Notice that the field name sbusiness is surrounded with the fields called QUOT, which inserts a single quote at the beginning and end of the word business. All character criteria must be surrounded with single quotes. Numeric values do not need to be surrounded with single quotes.
*---------------------------------------------------------------------
* ** Set SQL record select statement to select all records where book type = value in sbusiness
*---------------------------------------------------------------------
C Eval SQLStmt = 'SELECT * FROM Titles where type = ' + QUOT +
C %trimr(sbusiness) + QUOT
*---------------------------------------------------------------------
* ** Run Query to Open ADO Recordset
*---------------------------------------------------------------------
C Eval Rtn = SQL_RunSQLSel(SQL_Socket:
C SQLStmt)
Sample hard coded SQL Select statement which selects all fields and records where the book title contains the characters: bus from the Titles table in the sample Pubs database installed with SQL Server. This example sets a variable named SQLStmt with the SQL record selection criteria and then calls SQL_RunSQLSel to select the records from the table for reading. **Notice that the word business is surrounded with the fields called QUOT, which inserts a single quote at the beginning and end of the word business. All character criteria must be surrounded with single quotes. Numeric values do not need to be surrounded with single quotes.
<nowii> *--------------------------------------------------------------------- * ** Set SQL record select statement to select all records where book title contains 'bus' *---------------------------------------------------------------------
C Eval SQLStmt = 'SELECT * FROM Titles where title like ' + QUOT + C '%bus%' + QUOT
*--------------------------------------------------------------------- * ** Run Query to Open ADO Recordset *---------------------------------------------------------------------
C Eval Rtn = SQL_RunSQLSel(SQL_Socket: C SQLStmt) </nowiki> Sample soft coded SQL Select statement which selects all fields and records where the book title contains the characters found in field sbusiness from the Titles table in the sample Pubs database installed with SQL Server. This example sets a variable named SQLStmt with the SQL record selection criteria and then calls SQL_RunSQLSel to select the records from the table for reading. **Notice that the word business is surrounded with the fields called QUOT, which inserts a single quote at the beginning and end of the word business. All character criteria must be surrounded with single quotes. Numeric values do not need to be surrounded with single quotes.
*---------------------------------------------------------------------
* ** Set SQL record select statement to select all records where book title contains value in stitle
*---------------------------------------------------------------------
C Eval SQLStmt = 'SELECT * FROM Titles where title like ' + QUOT +
C + '%' + %trimr(stitle) + '%' + QUOT
*---------------------------------------------------------------------
* ** Run Query to Open ADO Recordset
*---------------------------------------------------------------------
C Eval Rtn = SQL_RunSQLSel(SQL_Socket:
C SQLStmt)
Sample soft coded SQL Select statement which selects records from theTitles table in the sample Pubs database installed with SQL Server. This example sets a variable named SQLStmt with the soft-coded SQL record selection criteria and then calls SQL_RunSQLSel to select the records from the table for reading.
*---------------------------------------------------------------------
* ** Set SQL record select statement with criteria for Title_ID field,
* Title field and Type field. This sample uses the LIKE statement
* and percent signs around each search field to do a partial word search on all 3 fields.
*---------------------------------------------------------------------
C Eval SQLStmt = 'SELECT * FROM Titles ' +
C 'WHERE Title_ID LIKE ' + QUOT + '%' +
C %trimr(ITITLEID) + '%' + QUOT + ' AND ' +
C 'TITLE LIKE ' + QUOT + '%' +
C %trimr(ITITLE) + '%' + QUOT + ' AND ' +
C 'TYPE LIKE ' + QUOT + '%' +
C %trimr(ITYPE) + '%' + QUOT
*---------------------------------------------------------------------
* ** Run Query to Open ADO Recordset
*---------------------------------------------------------------------
C Eval Rtn = SQL_RunSQLSel(SQL_Socket:
C SQLStmt)
