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 16 Next »

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


See Also: For an example of how to implement this migration using our C# API see this article.

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) Make 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 a 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) Remove rdoResultSet as a variable and replace later with a temporary variable in a using statement
		OLD:	RDO.rdoResultset Results = null;

4) Remove the following (due to EOF -> Read() conversion)
		OLD:	Results.MoveNext();
		
5) Replace ? with incremented generic @# type parameters OR insert a runtime parser that replaces ? with @# typed parameters. 
Some care must 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 correspond 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 removed 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 corresponding using statement in VB.NET is expressed as follows:  
			  
			  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) The 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 (msrdo20Transform.gmsl)

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;
}
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;
}
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;
}
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_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,sizeof(OPC.PAT) - sizeof(OPC.COL));
   Opcode.SetLength(nCode);
   Opcode.SetOperation(codptr,iEnd,OPC.PAT,parameters);
   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_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 __rdoResultset_EOF(int subRoot,int iStart,int iRefer)
{
   int        nCode;
   tCodeBlock codptr;
   int        opcd;
   int        subcd;
   int        icode;
   nCode = Opcode.GetLength();
   codptr = Opcode.GetCode();
   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.NOT) return 0; 
   nCode = Opcode.DeleteCode(icode,nCode,sizeof(OPC.NOT));
   Opcode.SetLength(nCode);
   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,sizeof(OPC.LLP) + sizeof(OPC.MEM));
   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,sizeof(OPC.LLP) + sizeof(OPC.MEM));
   Opcode.SetLength(nCode);
   return iRefer;
}
int __rdoResultset_MoveNext(int subRoot,int icode,int iRefer)
{
   iRefer = Opcode.CommentOut(iRefer,OPC.CMT.Delete);
   return iRefer;
}


RDO API description changes

The following are the changes made in the interface description file for RDO to direct the API upgrade and activate the gmSL migration routines to do deeper transformations of the application code.

GreatMigrations LLC Regression Testing
Produced: 4/26/2013 04:38:22 PM
   
Mode:  Differences  
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" migPattern="%1d.Open()\c">

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()" >

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="msrdo20Transform.gmsl" />



1082</Refactor>

Additional Readings

gmSLIntroduction

/wiki/spaces/STAG/pages/1442122


  File Modified



  • No labels