Mathematica can access .net objects easily via NETLink. Works well to custom assemblies.
However, I have run into a problem when trying to use Read() method on a SqlCeResultSet
object returned by System.Data.SqlServerCe.dll
.
Read() method is documented here for MS SQL Compact Edition (v3.5 SP2)
https://msdn.microsoft.com/en-us/library/system.data.sqlserverce.sqlcedatareader.read(v=vs.100).aspx
Below a simple script to demonstrate the problem. You will notice that in the function GetAllGuids[]
the .nb editor will place a red carat on the results@Read[] and flag an error saying the Read command needs more than zero parameters (which is true for Mathematica builtin Read[] method, but not true for the referenced Read() method on the .Net Object which has no parameters.). Workbench will also flag the error in the .m file (see below re package).
The code will run correctly even though the editor flags the line as in error.
Needs["NETLink`"];
InstallNET[];
sqlceAssembly =
LoadNETAssembly[
"C:\\Program Files\\Microsoft SQL Server Compact Edition\\v3.5\\Desktop\\System.Data.SqlServerCe.dll"];
LoadNETType["System.Data.SqlServerCe.ResultSetOptions", sqlceAssembly];
LoadNETType["System.Data.SqlDbType"];
OpenSDFConnection[filename_] := Module[{connectionString},
Print["Open SDF file: ", filename];
(* Open a connection to the database *)
connectionString = "Data Source='" <> filename <> "'; Password='XXX'; Encrypt = FALSE; File Mode=Read Only; SSCE:Temp File Directory='C:\\Temp';";
connection = NETNew[{"System.Data.SqlServerCe.SqlCeConnection", sqlceAssembly}, connectionString];
connection@Open[];
(* Construct commands to query the database *)
cmdGetAllMeasurementIds = connection@CreateCommand[];
cmdGetAllMeasurementIds@CommandText = "SELECT DISTINCT " <> FIELDMEASID <> " FROM " <> TABLEDATA;
(* Return the filename *)
filename
]
GetAllGuids[] := Module[{results},
results = cmdGetAllMeasurementIds@ExecuteResultSet[ResultSetOptions`None];
Reap[
While[results@Read[],
Sow[results@GetGuid[0]@ToString[]]
]
][[2, 1]]
]
SdfFilename = FileNameJoin[{NotebookDirectory[], "pt2.sdf"}];
OpenSDFConnection[SdfFilename];
guids = GetAllGuids[] // TableForm
I wanted to move the database access functions to a Mathematica package to hide the implementation details and facilitate easier source control (diff, merge etc. of .m easier than .nb)
The call to a OpenSDFConnection[fn_]
in a package appears to work, but Mathematica refuses to run the GetAllGuids[]
method, returning the error Read::argt: Read called with 0 arguments; 1 or 2 arguments are expected.
Mathematica appears to apply parsing rules for the built in Read[] method, not the required rules for the referenced .Net Object's Read().
Is this a known issue? Am I doing something incorrect? (I am a newbie to Mathematica)
I will keep the DB script in the .nb as a work-a-round for now as I need to keep moving forward.
I am interested in suggestions how it might be put into a Mathematica package, without coding a custom .Net Assembly to act as a thin facade to System.Data.SqlServerCe.dll
GetAllGuids
could occur ifNETObjectQ[results]
returns false, i.e.results
does not contain a .NET object. This, in turn, could occur ifcmdGetAllMeasurementIds
is not a .NET object and so on up the chain. Unfortunately, a small typo in NETLink code will often yield an inert expression rather than signalling an error. Try printing out the values of all of the intermediate .NET objects (e.g. usingEcho
) to see if this has happened. $\endgroup$