Page tree
Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 3 Next »

The following sample that illustrates using gmSL to do complex transformations.   In this case a simple SQL query in RDO is migrated to use SqlClient classes.

VB6

 
Public Sub connectDB()
    
    Set conn = New rdoConnection
    Dim dbs As String
    dbs = _
    "UID=stocks_login;PWD=password;Database=stocks;" _
        & "Server=GMI-CS-01.gmi.local;Driver={SQL Server};" _
        & "DSN='';"
        
    conn.Connect = dbs
    conn.EstablishConnection
End Sub
Public Sub execQuery()
    Dim SQL As String
    Dim SP As rdoQuery
    Dim Results As rdoResultset
    Dim f As String
   
    SQL = "select * from accounts where accountID < ? and FirstName like ?"
    
    Set SP = conn.CreateQuery("QueryAcct", SQL)
    SP.rdoParameters(0).Value = "5005"
    SP.rdoParameters(1).Value = "Test%"
    Set Results = SP.OpenResultset(rdOpenForwardOnly)
    While Not Results.EOF
        f = Results.rdoColumns("FirstName").Value
        writeLog f
        f = Results.rdoColumns("eMail").Value
        writeLog f
        Results.MoveNext
    Wend
    SP.Close
End Sub

Desired C#

public static void connectDB()
{
   conn = new System.Data.SqlClient.SqlConnection();
   string dbs = "";
   dbs = "UID=stocks_login;PWD=password;Database=stocks;Server=GMI-CS-01.gmi.local;";
   conn.ConnectionString = dbs;
   conn.Open();
}
public static void execQuery()
{
   string SQL = "";
   System.Data.SqlClient.SqlCommand SP = null;
   string f = "";
   SQL = "select * from accounts where accountID < @0 and FirstName like @1";
   (SP = conn.CreateCommand()).CommandText = SQL;
   SP.Parameters.Add(new System.Data.SqlClient.SqlParameter("@0", null)).Value = "5005";
   SP.Parameters.Add(new System.Data.SqlClient.SqlParameter("@1", null)).Value = "Test%";
   using (System.Data.SqlClient.SqlDataReader Results = SP.ExecuteReader())
   {
      while (Results.Read())
      {
         f = Convert.ToString(Results["FirstName"]);
         writeLog(f);
         f = Convert.ToString(Results["eMail"]);
         writeLog(f);
      }
   }
}

Specifications

1) Various type pattern replacements from MSRDO20.dll.xml for example 
		OLD:	static RDO.rdoConnection conn = null;
		NEW:	static System.Data.SqlClient.SqlConnection conn = null;

		OLD:	conn = new RDO.rdoConnection();
		NEW:	conn = new System.Data.SqlClient.SqlConnection();

		OLD:	conn.Connect = dbs;
		NEW:	conn.ConnectionString = dbs;		

		OLD:	conn.EstablishConnection(null,null,null);
		NEW:	conn.Open();		

		OLD:	RDO.rdoQuery SP = null;
		NEW:	System.Data.SqlClient.SqlCommand SP = null;		

2) transform the connection string (difficult to generalize; easily  done with authorfix or runtime method)
		OLD:	dbs = "UID=stocks_login;PWD=password;Database=stocks;" + "Server=GMI-CS-01.gmi.local;Driver={SQL Server};" + "DSN='';";
		NEW:	dbs = "UID=stocks_login;PWD=password;Database=stocks;" + "Server=GMI-CS-01.gmi.local;";

3) rdoResultSet is removed as a variable and replaced later with a temporary variable in a using statement
		OLD:	RDO.rdoResultset Results = null;

4) Removed due to EOF -> Read() conversion
		OLD:	Results.MoveNext();
		
5) The tool needs to replace ? with incremented generic @# type parameters OR
 the tool could insert a runtime parser that replaces ? with @# typed parameters which need compensated for when adding the parameters
 Some consideration needs to be taken when considering that SQL strings can be built using various concat statements and variables.
 Our current sample and the issue provided by client did not include that scenerio.
		OLD:	SQL = "select * from accounts where accountID < ? and FirstName like ?";
		NEW:	SQL = "select * from accounts where accountID < @0 and FirstName like @1";

6) Params are not created by default.  We must add a new one, then assign its value. The parameter name 
 needs to corrospond to the param index such that 0 becomes "0" and in the query ? becomes @0
		OLD:	SP.rdoParameters[0].Value = "5005";
		NEW:	SP.Parameters.Add(new System.Data.SqlClient.SqlParameter("0", null)).Value = "5005";

		OLD:	SP.rdoParameters[1].Value = "Test%";
		NEW:	SP.Parameters.Add(new System.Data.SqlClient.SqlParameter("1", null)).Value = "Test%";
		
7) CreateQuery must use the connection to create a command and then set its query statement.  
 This can be authored as 2 statements, but we felt it would be cleaner to migrate from 1 statement to 1 statement.
		OLD:	SP = conn.CreateQuery("QueryAcct",SQL);
		NEW:	(SP = conn.CreateCommand()).CommandText = SQL;


8) The results variable declaration that was removedin in operation 4), is instead declared in a C# 'using' block. 
That block ends after the last statement that uses the results variable: operation 9).
 
Also, rdoResultSet is type changed to SqlDataReader and the OpenResultSet call is changed to ExecuteReader
 
		OLD:	Results = SP.OpenResultset(RDO.ResultsetTypeConstants.rdOpenForwardOnly,null,null);
		NEW:	using (System.Data.SqlClient.SqlDataReader Results = SP.ExecuteReader())
		NEW:	{
		
			  By the way, the corrosponding using statement in VB.NET is prepresented  
			  
			  Using Results As System.Data.SqlClient.SqlDataReader = SP.ExecuteReader()
				 ...
			  End Using

9) Command.Close is not needed with a using block; just closing the block in .NET does a close/dispose implicitly.
		OLD:	SP.Close();
		NEW:	}
		
10) Results EOF property becomes the Read method.  The Boolean logic is reversed requiring the removal of the !.  Because of the Read method the Results.MoveNext method is removed
		OLD:	while (!Results.EOF)
		NEW:	while (Results.Read())

11a) rdoColumns collection reference is replaced with indexer this[string column] of the reader.
11b) The .Value is removed.  
		OLD:	f = Convert.ToString(Results.rdoColumns["FirstName"].Value);
		NEW:	f = Convert.ToString(Results["FirstName"]);
		
		OLD:	f = Convert.ToString(Results.rdoColumns["eMail"].Value);
		NEW:	f = Convert.ToString(Results["eMail"]);
 

gmSL Rules (rdoHandlers.gmls)

 
/*
;/doc/ ****************************************************************************************
;
; The method RefactorCode_FindAssign is used to find an assignment to a variable that is being
; used later in the code to supply a value. It is this value that needs to be modified.
;
;/doc/ ****************************************************************************************
*/
int RefactorCode_FindAssign(int varRoot,int iEnd)
{
   tCodeBlock codptr;
   int        lastLev0;
   int        icode;
   int        addr;
   int        opcd;
   int        subcd;
   codptr = Opcode.GetCode();
   lastLev0 = 0;
   for(icode = 0; icode >= 0; icode = Opcode.GetNext(codptr,icode,iEnd))
   {
      opcd = Opcode.GetOperation(codptr,icode,subcd);
      if(opcd == OPC.LEV && subcd == 0) lastLev0 = icode;
      else if(opcd == OPC.LDA && subcd == varRoot)
      {
         opcd = Opcode.GetOperation(codptr,icode+sizeof(OPC.LDA),subcd);
         if(opcd == OPC.STR) return lastLev0;
      }           
   }
   return 0;
}
/*
;/doc/ ****************************************************************************************
;
; The method RefactorCode_RelaceAssign replaces a string argument with an constant relacement
; string.
;
;/doc/ ****************************************************************************************
*/
int RefactorCode_ReplaceAssign(int iAssign, string replacement)
{
   tCodeBlock codptr;
   int        nCode;
   int        iEnd;
   int        nDelete;
   int        addr;
   codptr = Opcode.GetCode();
   nCode = Opcode.GetLength();
   iEnd = Opcode.FindArgumentEnd(codptr,iAssign,nCode);
   iAssign = iAssign + sizeof(OPC.LEV);
   nDelete = iEnd - iAssign - sizeof(OPC.LSC) - sizeof(OPC.ARG);
   if(nDelete > 0)
   {
      nCode = Opcode.DeleteCode(iAssign,nCode,nDelete);
      Opcode.SetLength(nCode);
   }
   addr = Store.String(replacement);
   Opcode.SetOperation(codptr,iAssign,OPC.LSC,addr);
   return nDelete;
}
/*
;/doc/ ****************************************************************************************
;
; transform the connection string (difficult to generalize; easily  done with authorfix or
;    runtime method)
; OLD: dbs = "UID=stocks_login;PWD=password;Database=stocks;" + "Server=GMI-CS-01.gmi.local;
;             Driver={SQL Server};" + "DSN='';";
; NEW: dbs = "UID=stocks_login;PWD=password;Database=stocks;" + "Server=GMI-CS-01.gmi.local;";
;
;/doc/ ****************************************************************************************
*/
int __rdoConnection_Connect(int subRoot,int iStart,int iRefer)
{
   tCodeBlock codptr;
   int        nCode;
   int        opcd;
   int        subcd;
   int        icode;
   int        iEnd;
   int        localVar;
   int        iAssign;
   string     connect;
   int        iPos;
   int        semi;
   int        nDelete;
   int        addr;
/*
   Step 1: Verify that this is a local variable assignment to a property.
*/
   codptr = Opcode.GetCode();
   nCode = Opcode.GetLength();
   opcd = Opcode.GetOperation(codptr,iStart,localVar);
   if(opcd != OPC.LDA) return 0;
   icode = iRefer + sizeof(OPC.LLP);
   opcd = Opcode.GetOperation(codptr,icode,subcd);
   if(opcd != OPC.MEM) return 0;
   icode = icode + sizeof(OPC.MEM);
   opcd = Opcode.GetOperation(codptr,icode,subcd);
   if(opcd != OPC.STR) return 0;
/* 
   Step 2: Look for a preceeding string assignent to this local variable.
*/
   iAssign = RefactorCode_FindAssign(localVar,iStart);
   if(iAssign == 0) return 0;
/*   
   Step 3: Obtain the actual string value being assigned to the local variable.
*/
   iEnd = Opcode.FindArgumentEnd(codptr,iAssign,nCode);
   connect = Opcode.GetString(iAssign,iEnd);
   if(!connect) return 0;
/*
   Step 4: Remove the atrribute-values pairs that are not to be used.
*/
   iPos = Character.FindFirst(connect,0,"Driver=");
   if(iPos)
   {
      iPos = iPos - 1;
      semi = Character.FindFirst(connect,iPos,";");
      if(semi)
      {
         connect = Character.Remove(connect,iPos,semi);
      }
   }
   iPos = Character.FindFirst(connect,0,"DSN=");
   if(iPos)
   {
      iPos = iPos - 1;
      semi = Character.FindFirst(connect,iPos,";");
      if(semi)
      {
         connect = Character.Remove(connect,iPos,semi);
      }
   }
/*
   Step 5: Replace the old string expression with the revised string.
*/
   nDelete = RefactorCode_ReplaceAssign(iAssign,connect);
   iRefer = iRefer - nDelete;
   return iRefer;
}
/*
;/doc/ ****************************************************************************************
;
; The tool needs to replace ? with incremented generic @# type parameters OR
; the tool could insert a runtime parser that replaces ? with @# typed parameters which need
; compensated for when adding the parameters Some consideration needs to be taken when considering
; that SQL strings can be built using various concat statements and variables.
; Our current sample and the issue provided by client did not include that scenerio.
;  OLD: SQL = "select * from accounts where accountID < ? and FirstName like ?";
;  NEW: SQL = "select * from accounts where accountID < @0 and FirstName like @1";
;
;/doc/ ****************************************************************************************
*/
int __rdoConnection_CreateQuery(int subRoot,int iStart,int iRefer)
{
   tCodeBlock codptr;
   int        nCode;
   int        opcd;
   int        subcd;
   int        icode;
   int        sqlString;
   int        sqlVar;
   string     query;
   int        iAssign;
   int        index;
   int        iPos;
   int        lPos;
   int        nDelete;
   int        createCommand;
   int        iEnd;
/*
   Step 1: Make certain that this is a valid CreateQuery call and obtain the variable that contains the
   SQL Query 
*/
   codptr = Opcode.GetCode();
   nCode = Opcode.GetLength();
   icode = iRefer;
   opcd = Opcode.GetOperation(codptr,icode,subcd);
   if(opcd != OPC.LLP) return 0;
   icode = icode + sizeof(OPC.LLP);
   opcd = Opcode.GetOperation(codptr,icode,subcd);
   if(opcd != OPC.MEM) return 0;
   icode = icode + sizeof(OPC.MEM);
   opcd = Opcode.GetOperation(codptr,icode,subcd);
   if(opcd != OPC.LEV) return 0;
   sqlString = Opcode.FindArgumentEnd(codptr,icode,nCode);
   opcd = Opcode.GetOperation(codptr,sqlString,subcd);
   if(opcd != OPC.LEV) return 0;
   sqlString = sqlString + sizeof(OPC.LEV);
   opcd = Opcode.GetOperation(codptr,sqlString,sqlVar);
   if(opcd != OPC.LDA) return 0;
   sqlString = sqlString+sizeof(OPC.LDA);
   opcd = Opcode.GetOperation(codptr,sqlString,subcd);
   if(opcd != OPC.ARG) return 0;
   sqlString = sqlString+sizeof(OPC.ARG);
/* 
   Step 2: Look for a preceeding string assignent to this local variable.
*/
   iAssign = RefactorCode_FindAssign(sqlVar,iRefer);
   if(iAssign == 0) return 0;
/*   
   Step 3: Obtain the actual string value being assigned to the local variable.
*/
   iEnd = Opcode.FindArgumentEnd(codptr,iAssign,nCode);
   query = Opcode.GetString(iAssign,iEnd);
   if(!query) return 0;
/*
   Step 4: If there is a constant query string replace the ?'s with @index and
   if necessary relace it in the code.
*/ 
   if(iAssign)
   {
      iPos = 0;
      for(index = 0; index < 10; index = index + 1)
      {
         lPos = Character.FindFirst(query,iPos,"?");
         if(!lPos) break;
         iPos = iPos + lPos - 1;
         query = Character.Remove(query,iPos,1);
         query = Character.Insert(query,iPos,"@" + index);
      }
      if(iPos != 0)
      {   
         nDelete = RefactorCode_ReplaceAssign(iAssign,query);
         iRefer = iRefer - nDelete;
         sqlString = sqlString - nDelete;
      }
   }
/*
   Step 5: Replace the CreateQuery call with a CreateCommannd call.
*/ 
   iEnd = sqlString;
   opcd = Opcode.GetOperation(codptr,iEnd,subcd);
   if(opcd != OPC.CUF) return 0;
   iEnd = iEnd + sizeof(OPC.CUF);
   opcd = Opcode.GetOperation(codptr,iEnd,subcd);
   if(opcd != OPC.REF) return 0;
   iEnd = iEnd + sizeof(OPC.REF);
   opcd = Opcode.GetOperation(codptr,iEnd,subcd);
   if(opcd != OPC.ARG) return 0;
   iEnd = iEnd + sizeof(OPC.ARG);
   opcd = Opcode.GetOperation(codptr,iEnd,subcd);
   if(opcd != OPC.CMD) return 0;
   iEnd = iEnd + sizeof(OPC.CMD);
   nDelete = iEnd - sqlString - sizeof(OPC.PAT);
   createCommand = Symbol.FindIdentifier("RDO.DotNet.CreateCommand");
   nCode = Opcode.DeleteCode(sqlString,nCode,6);
   Opcode.SetLength(nCode);
   Opcode.SetOperation(codptr,sqlString,OPC.PAT,createCommand);
   return iRefer;
}
int rdoPreparedStatement_OpenResultset(int subRoot,int iStart,int iRefer)
{
   tCodeBlock codptr;
   int        nCode;
   int        opcd;
   int        subcd;
   int        icode;
   int        varRoot;
   tVariable  varInfo;
/*
   Step 1: Make certain the reference is present and obtain the root of the results
   variable.
*/
   codptr = Opcode.GetCode();
   nCode = Opcode.GetLength();
   icode = iRefer;
   opcd = Opcode.GetOperation(codptr,icode,subcd);
   if(opcd != OPC.REF) return 0;
   icode = icode + sizeof(OPC.REF);
   opcd = Opcode.GetOperation(codptr,icode,subcd);
   if(opcd != OPC.ARG) return 0;
   icode = icode + sizeof(OPC.ARG);
   opcd = Opcode.GetOperation(codptr,icode,subcd);
   if(opcd != OPC.CMD) return 0;
   opcd = Opcode.GetOperation(codptr,iStart,varRoot);
   if(opcd != OPC.LDA) return 0;
/*
   Step 2: Set the DEADCODE property of the results variable to True to block its declaration.
*/
   varInfo = Store.DeltaVector(varRoot);
   varInfo.DeadCode = True;
/*
   Step 3: Change the SET command to an using command and insert the type declaration code in it
   for the results variable.
*/
   Opcode.SetOperation(codptr,icode,OPC.IFS,OPC.IFS.Using);
   nCode = Opcode.ExpandCode(icode,nCode,sizeof(OPC.TYV));
   Opcode.SetLength(nCode);
   Opcode.SetOperation(codptr,icode,OPC.TYV,varRoot);
   return iRefer;
}
int rdoPreparedStatement_rdoParameters(int subRoot,int icode,int iRefer)
{
   int         nCode;
   tCodeBlock  codptr;
   int         iEnd;
   int         opcd;
   int         subc;
   int         parameters;  
   nCode = Opcode.GetLength();
   codptr = Opcode.GetCode();
   iEnd = Opcode.FindArgumentEnd(codptr,iRefer+7,nCode);
   opcd = Opcode.GetOperation(codptr,iEnd,subc);
   if(opcd != OPC.COL || subc != OPC.COL.Item) return 0;
   parameters = Symbol.FindIdentifier("RDO.DotNet.Parameters");
   nCode = Opcode.ExpandCode(iEnd,nCode,3);
   Opcode.SetLength(nCode);
   Opcode.SetOperation(codptr,iEnd,OPC.PAT,parameters);
   return iRefer;
}
int rdoPreparedStatement_Close(int subRoot,int icode,int iRefer)
{
   int         nCode;
   tCodeBlock  codptr;
   iRefer = Opcode.CommentOut(iRefer,OPC.CMT.Delete);
   iRefer = iRefer + sizeof(OPC.CMT);
   nCode = Opcode.GetLength();
   codptr = Opcode.GetCode();
   nCode = Opcode.ExpandCode(iRefer,nCode,sizeof(OPC.IFS));
   Opcode.SetLength(nCode);
   Opcode.SetOperation(codptr,iRefer,OPC.IFS,OPC.IFS.EndUsing);
   return iRefer;
}
int __rdoColumn_Value(int subRoot,int icode,int iRefer)
{
   int         nCode;
   tCodeBlock  codptr;
   nCode = Opcode.GetLength();
   codptr = Opcode.GetCode();
   nCode = Opcode.DeleteCode(iRefer,nCode,7);
   Opcode.SetLength(nCode);
   return iRefer;
}
int __rdoResultset_rdoColumns(int subRoot,int icode,int iRefer)
{
   int         nCode;
   tCodeBlock  codptr;
   nCode = Opcode.GetLength();
   codptr = Opcode.GetCode();
   nCode = Opcode.DeleteCode(iRefer,nCode,7);
   Opcode.SetLength(nCode);
   return iRefer;
}
int __rdoResultset_EOF(int subRoot,int icode,int iRefer)
{
   int         nCode;
   tCodeBlock  codptr;
   nCode = Opcode.GetLength();
   codptr = Opcode.GetCode();
   nCode = Opcode.DeleteCode(iRefer+7,nCode,2);
   Opcode.SetLength(nCode);
   return iRefer;
}
int __rdoResultset_MoveNext(int subRoot,int icode,int iRefer)
{
   int         nCode;
   tCodeBlock  codptr;
 
   nCode = Opcode.GetLength();
   codptr = Opcode.GetCode();
   iRefer = iRefer - 5;
   nCode = Opcode.DeleteCode(iRefer,nCode,14);
   Opcode.SetLength(nCode);
   return iRefer;
}

 

RDO API description changes

The following are the changes made in the interface description file to direct the upgrade and activate the gmSL migration routines

Left file: C:\gmSpec\COM\RDOToNET\gmProj\idf\FromIdl\MSRDO20.DLL.xml  
Right file: C:\gmSpec\COM\RDOToNET\gmProj\usr\MSRDO20.DLL.xml  

  -+2<!-- RDO to ADO.NET System.Data.SqlClient -->
 
7         location="%library%\Interop.RDO.dll"<>8         location="DoNotDeclare"
8         migName="RDO" 9         migName="System.Data.SqlClient"
 
233      <property id="Connect" type="String" status="InOut"/><>234      <property id="Connect" type="String" status="InOut" migName="ConnectionString" />
 
271      <method id="EstablishConnection" type="Void"><>272      <method id="EstablishConnection" type="Void" migName="Open">
272         <argument id="Prompt" type="Variant" status="ByVal" optional="Default"/> 273         <argument id="Prompt" type="Variant" status="ByVal" optional="Def.Overload"/>
273         <argument id="Readonly" type="Variant" status="ByVal" optional="Default"/> 274         <argument id="Readonly" type="Variant" status="ByVal" optional="Def.Overload"/>
274         <argument id="Options" type="Variant" status="ByVal" optional="Default"/> 275         <argument id="Options" type="Variant" status="ByVal" optional="Def.Overload"/>
 
276      <method id="CreateQuery" type="rdoQuery"><>277      <method id="CreateQuery" type="rdoQuery" migName="CreateCommand" >
 
397      <property id="EOF" type="Boolean" status="Out"/><>398      <property id="EOF" type="Boolean" status="Out" migName="Read()"/>
 
548      <property id="rdoParameters" type="rdoParameters" status="Out"/><>549      <property id="rdoParameters" type="rdoParameters" status="Out" migName="Parameters" />
 
575      <method id="OpenResultset" type="rdoResultset"><>576      <method id="OpenResultset" type="rdoResultset" migPattern="%1d.ExecuteReader()" >
 
694      <method id="Item" type="rdoColumn" status="indexer"><>695      <accessor id="Item" type="rdoColumn" status="indexer">
 
696      </method><>697      </accessor>
 
1049   <coclass id="rdoConnection"><>1050   <coclass id="rdoConnection" migName="SqlConnection">
 
1057   <coclass id="rdoResultset" creatable="off"><>1058   <coclass id="rdoResultset" creatable="off" migName="SqlDataReader" >
 
1061   <coclass id="rdoQuery"><>1062   <coclass id="rdoQuery" migName="SqlCommand" >
 
  -+1070<Refactor id="RDO" event="rdoHandlers" >
   1071   <migclass id="DotNet">
   1072      <method id="CreateCommand" type="void" migPattern="(%1d = %2d()).CommandText = %4d\c">
   1073         <argument id="conn" type="Object" status="ByVal" />
   1074         <argument id="Name" type="String" status="ByVal"/>
   1075         <argument id="SqlString" type="Variant" status="ByVal"/>
   1076      </method>
   1077      <Method id="Parameters" type="object" migPattern="%1d.Add(new System.Data.SqlClient.SqlParameter(\S@%2d\S, null))" >
   1078         <argument id="index" type="Integer" status="ByVal" />
   1079      </Method>
   1080   </migClass>
   1081   <gmSL NameSpace="rdoHandlers" Class="Transform" Source="rdoHandlers.gmsl" />
   1082</Refactor>
   1083 

  • No labels