Need CLR translation

Started by stanl, January 05, 2014, 08:26:21 AM

Previous topic - Next topic

stanl

How would I translate (probably 'appbase' or something Tony previously wrote, but I'm still a little confused)

[System.Reflection.Assembly]::LoadFrom("C:\Powershell\System.Data.SQLite.dll")

in WB CLR code, so that I might continue with a script like

Code (WINBATCH) Select

ObjectClrOption("use", "System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089")
cn = ObjectClrNew("System.Data.SQLite.SQLiteConnection")
cn.ConnectionString = "Data Source=E:\RA\SQLITE\SMS.SQLITE"
cn.Open()

cm = ObjectClrNew("System.Data.SQLite.SQLiteCommand")
cSQL = "SELECT SMS, Carrier FROM Carriers"
cm.Connection = cn
cm.CommandText = cSQL
dr = cm.ExecuteReader()


td

Two simple rules to remember. The 'appbase' option sets the directory that the CLR will use to look for 'non-GAC' assemblies.  The CLR does not use path environment variable searches like traditional Windows applications and DLLs often do. If you want to use 'non-GAC' assembles, you must either place those assemblies in the directory containing the WIL interpreter DLL or you must use the 'appbase' option. So you can do the following
Code (winbatch) Select

ObjectClrOption("AppBase", "C:\Powershell")

The second rule is that for what I hope are obvious reasons WinBath does not automagically load assemblies into the AppDomain of your script. The only exception is the 'mscorlib' assembly which the CLR automatically loads when it is loaded into a process.   You use the 'use' option to load assemblies into your WinBatch script AppDomain so you need the following before you can make use of your assembly's classes.
Code (winbatch) Select

ObjectClrOption("use","System.Data.SQLite")


Together these statements are equivalent to your Powershell  "[System.Reflection.Assembly]::LoadFrom("C:\Powershell\System.Data.SQLite.dll")" line.

The SQLite assembly works well in WinBatch.  In fact, we use it as part of the CLR subsystem regression test.
"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade

stanl

Thanks Tony. Now just need to read the data. Code is below and I attached the SQLite db (just have to change the appbase and db location to test). I get 'unknown name' error on While dr.read()

Code (WINBATCH) Select

ObjectClrOption("AppBase", "C:\Powershell")
ObjectClrOption("use","System.Data.SQLite")
cn = ObjectClrNew("System.Data.SQLite.SQLiteConnection")
cn.ConnectionString = "Data Source=E:\RA\SQLITE\SMS.SQLITE"
cn.Open()

cm = ObjectClrNew("System.Data.SQLite.SQLiteCommand")
cSQL = "SELECT SMS, Carrier FROM Carriers"
cm.Connection = cn
cm.CommandText = cSQL
dr = ObjectClrNew("System.Data.SQLite.SQLiteDataReader",cm.ExecuteReader())

;While dr.read()
;   data=data:dr.getstring(0):@TAB
;   data=data:dr.getstring(1):@CRLF
;EndWhile

dr=0
cn.Close()

Exit

stanl

as a P.S.

there was
data="" ;missing in earlier post
;While dr.read()
;   data=data:dr.getstring(0):@TAB
;   data=data:dr.getstring(1):@CRLF
;EndWhile

and I tried the script with both the 1.07 and 1.09 (.NET 3.5) versions of the .dll

All the C# and VB code I've seen use the .read() method after creating the reader.

....IFICantBYTE

Hi Stan,
I may be wrong, (I have only limited experience with CLR stuff), but it could possibly be CaSe sensitive issues....
Try changing your dr.read() to dr.Read() and the dr.getstring(x) to dr.GetString(x) and see if that error goes away.
Regards,
....IFICantBYTE

Nothing sucks more than that moment during an argument when you realize you're wrong. :)

stanl

Quote from: ....IFICantBYTE on January 05, 2014, 05:07:35 PM
Hi Stan,
I may be wrong, (I have only limited experience with CLR stuff), but it could possibly be CaSe sensitive issues....
Try changing your dr.read() to dr.Read() and the dr.getstring(x) to dr.GetString(x) and see if that error goes away.

Excellent suggestion, but the error only changes from Unknown Name to a general COM/OLE error - extended error has Exception: Non-Static Method requires a Target.

I tried closing the command object prior to the read() based on issues another user had on StackOverflow but the error persists.

stanl

Now for Plan-B, combined both the SQLite dll and .NET System to create a DataSet and DbAdapter. Still having issues getting at the data.

Code (WINBATCH) Select

ObjectClrOption("AppBase", "C:\Powershell\bin")
ObjectClrOption("use","System.Data.SQLite")
ObjectClrOption("use","System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089")
ds = ObjectClrNew("System.Data.DataSet")
dt = ObjectClrNew("System.Data.DataTable")
db = ObjectClrNew("System.Data.SQLite.SQLiteDataAdapter","SELECT SMS, Carrier FROM Carriers","Data Source=C:\RA\SQLITE\SMS.SQLITE")

db.Fill(ds)

Message("",ObjectTypeGet(db))
                         
;dt = ds.Tables[0]  ; AAARRRGGGHHH

db.Dispose()
Exit


td

There are many ways to accomplish the task and MSFT's surprisingly good FCL documentation is the a good place to start an exploration of those possibilities, since the SQLite classes are mostly derived from the FCL data classes. Being generally lazy on a Monday morning, I simply plugged your query and db into a existing script and got the expected result.
Code (winbatch) Select

strFile = DirScript():"SMS.SQLITE"
lRecords = ""
ObjectClrOption("appbase", "C:\Reinstalls\SQLite\sqlite-netFx20-binary-bundle-Win32-2005-1.0.84.0")
ObjectClrOption("use", "System.Data.SQLite")

objSqlLiteCon = objectClrNew("System.Data.SQLite.SQLiteConnection", "Data Source=%strFile%;Pooling=true")
ObjectClrOption("use", "System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089")
objDataState = ObjectClrNew( "System.Data.ConnectionState")
objSqlLiteCon.Open()
if objSqlLiteCon.State == objDataState.Open 

   ; Create a query.
   objSqlLiteCmd = objectClrNew("System.Data.SQLite.SQLiteCommand")
   objSqlLiteCmd.Connection  = objSqlLiteCon
   objSqlLiteCmd.CommandText = "SELECT SMS, Carrier FROM Carriers"
   objReader = objSqlLiteCmd.ExecuteReader()
   
   ; Create a data list.
   while objReader.Read
      lRecords = lRecords:objReader.GetValue(0):@tab:objReader.GetValue(1):@CRLF
   endwhile
   
   ; Cleanup
   objReader.Close()
   objSqlLiteCon.Close()
   objReader     = 0
   objSqlLiteCmd = 0
endif
objDataState  = 0
objSqlLiteCon = 0

Message("Result", lRecords)
"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade

stanl

Got it. Just for my own records, creating a System.Data.SQLite.SQLiteDataReader object is not required;  .read or .read() will both work; in this example GetValue/GetString are equivalent.

There is also a devart SQLite build which has a FetchSize property. Have you played with that build?

Thanks: enjoy your lazy day.... it was 61 degrees when I drove to work at 7am, by 7pm it will be in the 20's then slide to 10 overnight.

JTaylor

Good stuff.   Thanks for the post.


-10F here this morning.

Jim

stanl

It was -37 when I called my brother (Watertown, NY - new home of the 10th Mountain)

FYI:  I got around to pulling up some old code Deana helped me with when I first posted about MSAccess and the CLR - adjusted my dbadapter code by inserting her code to output to reportview:

Code (WINBATCH) Select

isdata=1
ObjectClrOption("AppBase", "C:\Powershell\bin")
ObjectClrOption("use","System.Data.SQLite")
cn = ObjectClrNew("System.Data.SQLite.SQLiteConnection")
cn.ConnectionString = "Data Source=C:\RA\SQLITE\SMS.SQLITE"

ObjectClrOption("use", "System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089")
oState = ObjectClrNew( "System.Data.ConnectionState")
cn.Open()
If cn.State == oState.Open 

   cm = ObjectClrNew("System.Data.SQLite.SQLiteCommand")
   cSQL = "SELECT SMS, Carrier FROM Carriers"
   cm.Connection = cn
   cm.CommandText = cSQL
   db = ObjectClrNew("System.Data.SQLite.SQLiteDataAdapter")
   dt = ObjectClrNew( 'System.Data.DataTable') 

   db.SelectCommand = cm
   rowcount = db.Fill(dt)
   colcount = dt.Columns.Count

   If rowcount==0
      isData=0
      goto end
   Endif

   arrData = ArrDimension(rowcount+1,colcount)

   oCols = dt.Columns
   x=0
   ForEach col in oCols
      arrData[0,x]=col.ColumnName
      x=x+1
   Next

   ;Read Data into an Array
   For _row = 1 To dt.Rows.Count-1
      objRow = dt.Rows.Item(_row)
      For _col = 0 To dt.Columns.Count-1
        objColumn = dt.Columns.Item(_col)
        arrData[_row,_col] = objRow.Item(objColumn)
      Next
   Next

   goto showit       


Endif

:end
cn.Close()
Exit



:showit
; Display in Reportview
MyDialogFormat=`WWWDLGED,6.2`

MyDialogCaption=`%cSQL%`
MyDialogX=090
MyDialogY=090
MyDialogWidth=374
MyDialogHeight=147
MyDialogNumControls=003
MyDialogProcedure=`DEFAULT`
MyDialogFont=`DEFAULT`
MyDialogTextColor=`DEFAULT`
MyDialogBackground=`DEFAULT,DEFAULT`
MyDialogConfig=0

MyDialog001=`101,127,036,012,PUSHBUTTON,"PushButton_OK",DEFAULT,"OK",1,10,32,DEFAULT,DEFAULT,DEFAULT`
MyDialog002=`237,127,036,012,PUSHBUTTON,"PushButton_Cancel",DEFAULT,"Cancel",0,20,DEFAULT,DEFAULT,DEFAULT,DEFAULT`
MyDialog003=`001,005,366,110,REPORTVIEW,"ReportView_1",arrData,DEFAULT,DEFAULT,30,7340032,DEFAULT,DEFAULT,"192|192|192"`

ButtonPushed=Dialog("MyDialog")

Goto end