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
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()
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
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.
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.
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()
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
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.
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.
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.
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.
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
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.
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)
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.
Good stuff. Thanks for the post.
-10F here this morning.
Jim
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:
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