SQL Migration Overview and Examples

This topic contains three sections – an Overview, Examples, and Framework Support. The Overview provides detailed statements of what happens to embedded SQL when it is migrated. The examples in this section show only snippets of code to demonstrate the statement.

The Examples have complete legacy source and migrated code more fully reflecting the statements from the Overview section.

The Framework Support shows the classes and details of note (with links) provided to support the migration of the embedded SQL.

Overview

  1. The RPG Agent assumes an ODBC driver for database connection when migrating the code. There are four statements (in MyJob.vr) that define the connection field and the driver connection string (connectStr), and then establish the connection. For example:

    1) DclFld ADO_Connection Type(System.Data.Odbc.OdbcConnection) Access(*Public)
    2) DclFld connectStr Type(*String)
   connectStr = string.Format("Driver={{Client Access ODBC Driver (32-bit)}};" + +
	             "System={0};DBQ={1},*USRLIBL;Uid={2};Pwd={3}", +
	             MyDatabase.Server, +
	             MyDatabase.DBName, +
	             MyDatabase.User,	+
	             /* Add your password here /*)
    3) ADO_Connection = *New System.Data.Odbc.Odbc.Connection(connectStr)

    In MyJob.vr, the RPG Agent will place messages showing the replacement values when using MS SQL Server (instead of ODBC). Basically, when using MS SQL Server, the same statements will read as follows:

    1) DclFld ADO_Connection Type(System.Data.SwlClient.SqlConnection) Access(*Public)
    2) DclFld connectStr Type(*String)
   connectStr = string.Format("Data Source={0};Initial Catalog={1};Integrated Security=True")
    3) ADO_Connection = *New System.Data.SqlClient.SqlConnection(connectStr)

  2. The SQL Commands directly supported are: PREPARE, DECLARE CURSOR, SELECT, OPEN CURSOR, CLOSE CURSOR, FETCH, INSERT, DELETE, and UPDATE>.

  3. A "query" is migrated using one of the ExecuteSQL_Query methods (SqlQueryResults is the result set) while all other "non-query" SQL commands are migrated using the ExecuteSQL_Statement methods.

  4. When WHERE clause host variables are used, the variable name in the legacy source is replaced within the migrated statement with a monarch-generated name as: "@sql_parm_n" where n is a sequence number of the parameter in the original statement i.e. @sql_parm_1, @sql_parm_2, etc. The host variables' values are then passed to the methods as a collection of parameters using instances of DBParm, DBStrParm, or DBScaledParm classes.

    For example, WHERE wddate = :ODATE# and wdemp# = :F1EMP# is migrated as shown below. Notice the two parameters @sql_parm_1 and @sql_parm_2 in the statement defined with DBParm and DBScaledParm respectively.

     . . .( + 
"WHERE wddate = @sql_parm_1 and wdemp# = @sql_parm_2;", + +
. . .   
*New DBParm(DbType.Time, ODATE#),
*New DBScaledParm(DbType.Decimal, F1EMP#, %Len(F1EMP#), %DecPos(F1EMP#))
 )

  5. INSERT clause host variables are handled the same as host variables in the WHERE clause.

  6. When the SELECT clause has host variables, the variables' values are converted to string and concatenated as part of the SELECT statement.

    For example, SELECT :#TgtDate, :F4EMP#, WDORD# is migrated as shown below. Notice how the two variables' values are converted to string using the built in function %Char when they are concatenated to the SELECT statement.

    SqlQueryResults = ExecuteSQL_Query ( +
"SELECT " + %Char(#TgtDate) + ", " + %Char(F4EMP#) + ", WDORD#, +
. . . )

  7. When the INTO keyword is used, the INTO section of the statement is removed and the values are propagated from the query results.

    For example, SELECT wddate, wdseq# INTO :F1DATE, :F1SEQ# is migrated as shown below. 

    SqlQueryResults =  ExecuteSQL_Query ( +
"SELECT wddate, wdseq#                         " +
 . . . )
F1DATE = SqlQueryResults["wddate"] *AsFld F1DATE 
F1SEQ# = SqlQueryResults["wdseq#"] *AsFld F1SEQ#

  8. SELECT statements that have an expression (instead of a list of columns) are modified by inserting "as SqlExprResultColumn" so that a new column is added to the result table. You can access it using SqlQueryResults["SqlExprResultColumn"].

    For example, SELECT IfNull(MAX(WDSEQ#),0)+1 INTO :F4SEQ# is migrated as shown below.

    SqlQueryResults = ExecuteSQL_Query ( +
"SELECT IfNull(MAX(WDSEQ#),0)+1 as SqlExprResultColumn                      "  + +
 . . . )
F4SEQ# = SqlQueryResults["SqlExprResultColumn"] *AsFld F4SEQ#

  9. The migration of a prepared statement is slightly more complex in that there are several commands to define the SELECT statement, prepare the statement, declare the cursor, open the cursor (execute the statement), fetch from the result set, and ultimately close the cursor. However, once a statement is prepared it can then be executed multiple times within the scope of the source program. For simplicity, each of the following statements is taken individually as it relates to prepared statements.

    For example, SelectF1 = 'select wddate, wdord#, wdseq# ' is migrated in a straightforward manner as shown below.

    SelectF1 = 'select wddate, wdord#, wdseq# '

    For example, PREPARE SelF1 FROM :SelectF1 is migrated as shown below. Notice how SelF1 is declared as Type(SqlPreparedStatement) then instanced with the connection and select statement (SelectF1). 

    DclFld SelF1 Type(SqlPreparedStatement) 
SelF1 = *New SqlPreparedStatement( monarchJob.ADO_Connection, SelectF1 )

    For example, DECLARE SelF1CSR SCROLL CURSOR FOR SELF1 is migrated as shown below. Notice how SelF1CSR is declared as Type(SqlCursor) then instanced with the connection and prepared statement (SELF1). A scroll type is then assigned.

    DclFld SelF1CSR Type(SqlCursor) 
SelF1CSR = *New SqlCursor( monarchJob.ADO_Connection, SELF1 ) 
SelF1CSR.ScrollType = SqlCursor.ScrollTypes.Scrollable

    For example, OPEN SelF1CSR is migrated as shown below. The OPEN executes the SQL statement. The cursor (SelF1CSR) has its own SQLCA, which is assigned to the global SQLCA so any testing of SQLCA.SQLCOD will still work. Although not shown, the RPG Agent will automatically qualify references to SQLCOD as SQLCA.SQLCOD since SQLCA is an instance of a class, and SQLCOD is a member field of SQLCA. 

    SelF1CSR.Open()
SQLCA = SelF1CSR.SQLCA

    For example, FETCH NEXT FROM SelF1CSR INTO :F1DATE, :F1ORD#, :F1SEQ# is migrated as shown below. Notice how SelF1CSR.Fetch, which returns true when results are available, conditions the statements that retrieve the results preventing a program from accessing SqlQueryResults which is invalid (*nothing) when the FETCH is unsuccessful.

    If ( SelF1CSR.Fetch( SqlCursor.FetchOrientations.Next ) )
   F1DATE = SelF1CSR.SqlQueryResultsByIndex( 0 ) *AsFld F1DATE
   F1ORD# = SelF1CSR.SqlQueryResultsByIndex( 1 ) *AsFld F1ORD# 
   F1SEQ# = SelF1CSR.SqlQueryResultsByIndex( 2 ) *AsFld F1SEQ# 
EndIf
SQLCA = SelF1CSR.SQLCA

    Notice how after the Fetch runs, the resulting data is retrieved using the name of the cursor (SelF1CSR) + "." + SqlQueryResultsByIndex instead of the global SqlQueryResults (used for single-row queries). Each instance of SqlCursor class keeps its own SqlQueryResults collections, making it possible to use more than one cursor at a time while keeping the result datasets independent. The access by Index as opposed to by name (as it is done when migrating single-row) is just a migration simplicity for the convenience of the RPG Agent, since the named collection used while preparing or creating the cursor may be out of scope.

    Closing the cursor, which in the legacy source is: CLOSE SelF1CSR, now becomes

    SelF1CSR.Close

All other SQL commands not mentioned in number 1 will be blindly migrated as "ExecuteSQL_Statement (statement)". No attempt will be made to look for host-variables and no migration task will be produced (it is up to the data provider's implementation to accept or reject the request at application runtime).

Example 1

The following legacy source example shows three host variables:

C/EXEC SQL
C+ DELETE FROM DEPARTMENT
C+  WHERE WDDATE = :ODATE# and WDEMP# = :F1EMP# and
C+        WDORD# = :F1ORD#
C/END-EXEC

The following migrated code using DBParm, DBStrParm, or DBScaledParm to pass the value for the three host variables. 

ExecuteSQL_Statement ( +
"DELETE FROM DEPARTMENT 
 WHERE WDDATE = @sql_parm_1 and WDEMP# = @sql_parm_2 +
 WDORD = @sql_parm_3;", +
*New  DBParm(DbType.Time, ODATE#), + 
*New  DBScaledParm(DbType.Decimal, F1EMP#, %Len(F1EMP#), %DecPos(F1EMP#)), +
*New  DBStrParm(DbType.String, F1ORD#, %Len(F1ORD#) + 
)

Example 2

The following legacy source has the Into keyword removed from the Select statement and replaced with blanks. The host variables are saved and used after executing the statements to retrieve their values as shown:

C/EXEC SQL
C+ Select WDDATE, WDSEQ#, WDEMP# Into :F1Date, :F1Seq#, :F1Emp#
C+ From wfmmst Join wfmdet On wmemp# = wdemp#
C+ Where wdmsc4 <> 'A' and wdetim = 0 and wdord# = :F1Ord# and
C+       wdmsc1 = 'P'
C/END-EXEC

This migrated code is generated. 

SqlQueryResults = ExecuteSQL_Query ( +
"Select WDDATE, WDSEQ#, WDEMP#                          " + +
" From wfmmst Join wfmdet On wmemp# = wdemp#"  + + 
" Where wdmsc4 <>  'A' and wdetim = 0 and wdord# = @sql_parm_1 and" + +
"       wdmsc1 = 'P';", +
*New  DBStrParm(DbType.String, F1Ord#, %Len(F1Ord#)) + 
)
F1Date = SqlQueryResults["WDDATE"] *AsFld F1Date
F1Seq# = SqlQueryResults["WDSEQ#"] *AsFld F1Seq#
F1Emp# = SqlQueryResults["WDEMP#"] *AsFld F1Emp#

Select statements that have an expression instead of a list of columns, such as Count(*), are modified by inserting "as SqlExprResultColumn" so that a new column is added to the result table, which can be accessed using SqlQueryResults["SqlExprResultColumn"] shown in the following example:

C/EXEC SQL
C+ Select Count(*)
C+ Into :#Count
C+ From wfmmst Join wfmdet
C+ On   wmemp# = wdemp#
C+ Where wdmsc4 <> 'A' and wdetim = 0 and wdosst = ' ' and
C+       wdord# = :F1Ord#
C/END-EXEC

This migrated code is generated as: 

SqlQueryResults = ExecuteSQL_Query ( +
"Select Count(*) as SqlExprResultColumn " + +
"               : ++
" From wfmmst Join wfmdet"  + + 
" Where wdmsc4 <>  'A' and wdetim = 0 and wdosst = ''  and" + +
"       wdord# = @sql_parm_1;", +
*New  DBStrParm(DbType.String, F1Ord#, %Len(F1Ord#)) + 
)
#Count = SqlQueryResults["SqlExprResultColumn"]  *AsFld #Count

Example 3

C/EXEC SQL 
C+ DECLARE Cursor1
C+ INSENSITIVE     SCROLL CURSOR
C+ WITHOUT HOLD WITH RETURN
C+ FOR 
C+ SELECT FLD1, FLD2, FLD3 
C+     FROM
C+        TBLA, TBLB
C+     WHERE
C+         :PARAM1 < KEY1 AND
C+         :PARAM2 LIKE "AAAA" AND
C+         :PARAM3 < KEY2
C+     ORDER BY 
C+         TBLA DESC
C+     FETCH FIRST 2 ROWS ONLY
C+     OPTIMIZE FOR 2 ROWS
C+     WITH RR 
C/END-EXEC

This migrated code is generated. 

/Error Unsupported: "INSENSITIVE" CURSOR qualifier not supported for DECLARE CURSOR.
/Error Unsupported: "WITHOUT HOLD" CURSOR qualifier not supported for DECLARE CURSOR.
/Error Unsupported: "WITH RETURN" CURSOR qualifier not supported for DECLARE CURSOR.
 DclFld Cursor1 Type(SqlCursor)
 Cursor1 = *New SqlCursor( monarchJob.ADO_Connection, "SELECT FLD1, FLD2, FLD3" + +
 "     FROM" + +
 "        TBLA, TBLB" + +
 "     WHERE" + +
 "         @sql_parm_1 < KEY1 AND" + +
 "         @sql_parm_2 LIKE 'AAAA' AND" + +
 "         @sql_parm_3 < KEY2" + +
 "     ORDER BY" + +
 "         TBLA DESC" + +
 "     FETCH FIRST 2 ROWS ONLY" + +
 "     OPTIMIZE FOR 2 ROWS" + +
 "     WITH RR;", 
 *New  DBStrParm(DbType.String, PARAM1, %Len(PARAM1)), +
 *New  DBStrParm(DbType.String, PARAM2, %Len(PARAM2)), +
 *New  DBStrParm(DbType.String, PARAM3, %Len(PARAM3))  +
 )
 Cursor1.ScrollType = SqlCursor.ScrollTypes.Scrollable

Example 4 – Prepared Statement

The following segments of legacy code (where "SelectF1" is a *Char len(500)) dynamically assigned to a valid SELECT quoted statement.

C/EXEC SQL
C+ PREPARE SelF1 FROM :SelectF1
C/END-EXEC
C* Declare the SQL cursor to hold the data retrieved from the SELECT
C/EXEC SQL
C+ DECLARE SelF1CSR SCROLL CURSOR FOR SELF1
C/END-EXEC 
C* Open the SQL cursor. 
C/EXEC SQL
C+ OPEN SelF1CSR
C/END-EXEC
C* Process the records in the SQL cursor until the return not = 0 
C                   Do        SubfilePage
C* 
C* Get the next row from the SQL cursor. 
C/EXEC SQL 
C+   FETCH NEXT FROM SelF1CSR 
C+   INTO :F1DATE, :F1ORD#, :F1SEQ#, :F1EMP#, :F1WFGR, :F1DIST 
C/END-EXEC
C*
C                   If        SQLCOD = 0 
                         Success - do something 
C                   Else
                         Failure, exit loop  
C                   Leave 
C                   EndDo 
C/EXEC SQL 
C+ CLOSE SelF1CSR
C/END-EXEC

Generates this migrated code: 

Error: Validation of dynamic SQL statement using host variables delayed until run-time. Please review migrated code.
DclFld SelF1 Type(SqlPreparedStatement)
SelF1 = *New SqlPreparedStatement( monarchJob.ADO_Connection, SelectF1 )
//* Declare the SQL cursor to hold the data retrieved from the SELECT
DclFld SelF1CSR Type(SqlCursor)
SelF1CSR = *New SqlCursor( monarchJob.ADO_Connection, SELF1 )
SelF1CSR.ScrollType = SqlCursor.ScrollTypes.Scrollable
//* Open the SQL cursor.
SelF1CSR.Open()
//* Process the records in the SQL cursor until the return not = 0
DoToVal(SubfilePage)

//* Get the next row from the SQL cursor.
If ( SelF1CSR.Fetch( SqlCursor.FetchOrientations.Next ) )
     F1DATE = SelF1CSR.SqlQueryResultsByIndex( 0 ) *AsFld F1DATE
     F1ORD# = SelF1CSR.SqlQueryResultsByIndex( 1 ) *AsFld F1ORD#
     F1SEQ# = SelF1CSR.SqlQueryResultsByIndex( 2 ) *AsFld F1SEQ#
     F1EMP# = SelF1CSR.SqlQueryResultsByIndex( 3 ) *AsFld F1EMP#
     F1WFGR = SelF1CSR.SqlQueryResultsByIndex( 4 ) *AsFld F1WFGR
     F1DIST = SelF1CSR.SqlQueryResultsByIndex( 5 ) *AsFld F1DIST
EndIf
 SQLCA = SelF1CSR.SQLCA
If( SQLCA.SQLCOD = 0 )
       Success - do something           
Else
       Failure, exit loop         
EndIf
EndDo

Example 5 – INSERT

The following legacy source example shows an INSERT:

C/EXEC SQL
C+ INSERT INTO PRMPTP
C+ SELECT 'EMPLOYEE' AS PRFLD, DIGITS(WMEMP#) AS PRVALU,
C+        WMNAME AS PRDESC
C+ FROM WFMMST
C+ WHERE EMEMP# <> :F1EMP#
C/END-EXEC

Migrates as: 

ExecuteSQL_Statement ( + 
"INSERT INTO PRMPTP" + + 
"SELECT 'EMPLOYEE' AS PRFLD, DIGITS(WMEMP#) AS PRVALU," + + 
"WMNAME        AS PRDESC" + + 
"WHERE WMEMP# <>  = @sql_parm_1;", +
*New  DBScaledParm(DbType.Decimal, F1EMP#, %Len(F1EMP#), %DecPos(F1EMP#)) +
)

The following legacy source example shows an INSERT with VALUES keyword:

C/EXEC SQL
C+ INSERT INTO SVCC
C+ (SCCSTS, SCCDO#, SCCDAT, SCCTIM, SCCOSC, SCCNSC, SCCUSR),
C+  VALUES(' ', :SvOrd#, :Msoedt, :#Time6, :Msostg, :@Stg, :Sdsusr
C/END-EXEC

Migrates as: 

ExecuteSQL_Statement ( + 
"INSERT INTO SVSCC" + + 
" (SCCSTS, SCCSO# SCCDAT, SCCTIM, SCCOSC, SCCNSC, SCCUSR)" + +
" VALUES(' ', @sql_parm_1, @sql_parm_2, @sql_parm_3, @sql_parm_4, @sql_parm_5, @sql_parm_6,;" +
*New DBStrParm(DbType.String, System. SvOrd#, %Len(SvOrd#)), +
*New  DBScaledParm(DbType.Decimal, Msoedt, %Len(Msoedt), %DecPos(Msoedt)), +
*New  DBScaledParm(DbType.Decimal, #Time6, %Len(#Time6), %DecPos(#Time6)), +
*New  DBStrParm(DbType.String, Msostg, %Len(Msostg)), +
*New  DBStrParm(DbType.String, @Stg, %Len(@Stg)), +
*New  DBStrParm(DbType.String, Sdusr, %Len(Sdusr)) +
)

Example 6 Host Variable

This:

C/EXEC SQL
C+ INSERT INTO WFMDET
C+ ( Select :#TgtDate, :F4EMP#, :F4SEQ#, WDORD#, WDACTW,
C+         WDTIME, WDSTIM, WDETIM, WDSDTE, WDEDTE, WDASWU,
C+         ' ', WDCTIM, WDCLDT, WDCLTM, WDASBY, WDASDT,
C+  . . .
C+ FROM WFMDET
C+ WHERE WDDATE = :#ODATE and WDEMP# = :F1EMP# and
C+       WDORD# = :F1ORD# and WDSEQ# = :F1SEQ#
C/END-EXEC

Migrates as: 

ExecuteSQL_Statement ( + 
"INSERT INTO WFMDET" + + 
" (Select " + %Char(#TgtDate) + ", " +%Char(F4EMP#) + ", " + %Char(F4SEQ#) + ", WDORD#, WDACTW," + +
"         WDTIME, WDSTIM, WDETIM, WDSDTE, WDEDTE, WDASWU," + +
"         ' ', WDCTIM, WDCLDT, WDCLTM, WDASBY, WDASDT," + +
" . . .
" FROM WFMDET" + +
" WHERE WDDATE = @sql_parm_1 and WDEMP# = @sql_parm_2 and" + + 
"       WDORD# = @sql_parm_3 and WDSEQ# = @sql_parm_4;" +
*New  DBParm(DbType.Time, #ODATE), +
*New  DBScaledParm(DbType.Decimal, F1EMP#, %Len(F1EMP#), %DecPos(F1EMP#)), +
*New  DBStrParm(DbType.Decimal, F1ORD#, %Len(F1ORD#)), +
*New  DBScaledParm(DbType.String, 1SEQ#, %Len(F1SEQ#), %DecPos(F1SEQ#)), +
)

Framework Support

Classes Description and Members of Note
DBParm Used for constructing SQL parameters where the attributes of the parameter can be determined by its DbType.
DBStrParm Used for constructing SQL parameters where the parameter is DbType.String requiring the length (%Len) to be specified.
DBScaledParm Used for constructing SQL parameters where the parameter is DbType.Decimal requiring the length (%Len) and decimal positions (%DecPos) to be specified.
Program ExecuteSQL_Query, ExecuteSQL_Statement, ExecuteSQL_QueryVerbatim, and ExecuteSQL_StatementVerbatim for the execution of SQL commands and SqlQueryResults to contain the results.
SQL_CommunicationsArea Represents the global SQLCA to trap and report run-time errors for a SQL statement. Main properties of note are SQLCOD and SQLSTT used to test the status of the last executed SQL statement.
SqlCursor Class Represents a SQL cursor for a multi-row dataset on which the cursor methods operate. It has its own SQLCA and SqlQueryResults properties as well as the SqlQueryResultsByIndex method for result set index access.
SqlPreparedStatement Class Represents a prepared executable version of a SQL command statement having its own SQLCA.

Next: Multimember File Support

Previous: Message Files

See Also

What's New
Reference Library