1
$\begingroup$

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

$\endgroup$
  • $\begingroup$ Please don't use the bugs tag when you post questions. This is a special tag that is meant to be added by someone else than the original poster, after the bug has been verified by the community. This way we ensure that the use of the tag is always correct. I removed the tag—waiting for a NET/Link expert to evaluate if it should be re-added. $\endgroup$ – Szabolcs Feb 8 '17 at 12:11
  • $\begingroup$ You can ignore the red highlighting. Take it as a warning, not an error: "did I do something wrong?" The other issue, i.e. that it tries to evaluate Read, is entirely separate (has nothing to do with syntax highlighting), and that is the real problem. $\endgroup$ – Szabolcs Feb 8 '17 at 12:16
  • $\begingroup$ I can't test your code because I'm not on Windows and don't have SQL Server. Just to clarify: are you saying that this runs fine in a notebook, but fails if you put it in a package? Can you give a full example, i.e. show the source of a small package and show what you do to trigger the error (i.e. load package, invoke package function)? $\endgroup$ – Szabolcs Feb 8 '17 at 12:26
  • 1
    $\begingroup$ The error in GetAllGuids could occur if NETObjectQ[results] returns false, i.e. results does not contain a .NET object. This, in turn, could occur if cmdGetAllMeasurementIds 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. using Echo) to see if this has happened. $\endgroup$ – WReach Feb 8 '17 at 16:59
  • $\begingroup$ Thanks for the heads up on the bug tag. Yes it runs in a notebook fine, but fails in a package. $\endgroup$ – Richard Scott Feb 9 '17 at 12:33
2
$\begingroup$

As pointed out by @Szabolcs, the syntax highlighting in the editors is misleading and can be ignored.

To answer the question how to get the .Net assembly methods to run in a package, prefix NETLink methods in a package with the namespace. i.e. use

NETLink`InstallNET[];

rather than

InstallNET[];

See LoadNETAssembly[], LoadNETType[] and NETNew[] in following example.

(* Wolfram Language package *)
(* ::Package:: *)

BeginPackage["VasomonDatabase`"]

OpenSDFConnection::usage = "OpenSDFConnection[sdfFilename_]" 
GetAllGuids::usage = "GetAllGuids[] returns all guids in database"

Begin["`Private`"]

TABLEDATA = "Data";
TABLEPROP = "Properties";
FIELDMEASID = "MeasID";
FIELDPROPNAME = "PropName";
FIELDPROPVAL = "PropVal";
FIELDDATANAME = "DataName";
FIELDDATAVAL = "DataVal";


GetAllGuids[] := Module[{results},
results = cmdGetAllMeasurementIds@ExecuteResultSet[ResultSetOptions`None];
 Reap[
   While[results@Read[],
    Sow[results@GetGuid[0]@ToString[]]
    ]
   ][[2, 1]]
 ]

OpenSDFConnection[sdfFilename_] := Module[{connectionString},
    (* Activate NETLink if not yet loaded *)
    Needs["NETLink`"];
    NETLink`InstallNET[];
    (* get instance of SQL CE assembly and required types *)
    sqlceAssembly = NETLink`LoadNETAssembly["C:\\Program Files\\Microsoft SQL Server Compact Edition\\v3.5\\Desktop\\System.Data.SqlServerCe.dll"];
    NETLink`LoadNETType["System.Data.SqlServerCe.ResultSetOptions", sqlceAssembly];
    NETLink`LoadNETType["System.Data.SqlDbType"];

    connectionString = "Data Source='" <> sdfFilename <> "'; Password='NIBPLOGGER'; Encrypt = FALSE; File Mode=Read Only; SSCE:Temp File Directory='C:\\Temp';";
    connection = NETLink`NETNew[{"System.Data.SqlServerCe.SqlCeConnection", sqlceAssembly}, connectionString];

    (* Open a connection to the database *)
    connection@Open[];

    (* Construct commands to query the database *)
    cmdGetAllMeasurementIds = connection@CreateCommand[];
    cmdGetAllMeasurementIds@CommandText = "SELECT DISTINCT " <> FIELDMEASID <> " FROM " <> TABLEDATA;

    cmdGetMeasurementProperties = connection@CreateCommand[];
    cmdGetMeasurementProperties@CommandText = "SELECT " <> FIELDPROPNAME <> "," <> FIELDPROPVAL <> " FROM " <> TABLEPROP <> " WHERE " <> FIELDMEASID <> " = @id";
    prmGetMeasurementPropertiesId = NETLink`NETNew[{"System.Data.SqlServerCe.SqlCeParameter", sqlceAssembly}, "@id", SqlDbType`UniqueIdentifier];
    cmdGetMeasurementProperties@Parameters@Add[prmGetMeasurementPropertiesId];

    cmdGetMeasurementData = connection@CreateCommand[];
    cmdGetMeasurementData@CommandText = "SELECT " <> FIELDDATANAME <> "," <> FIELDDATAVAL <> " FROM " <> TABLEDATA <> " WHERE " <> FIELDMEASID <> " = @id";
    prmGetMeasurementDataId = NETLink`NETNew[{"System.Data.SqlServerCe.SqlCeParameter", sqlceAssembly}, "@id", SqlDbType`UniqueIdentifier];
    cmdGetMeasurementData@Parameters@Add[prmGetMeasurementDataId];

    (* Return the filename *)
    sdfFilename
  ]

End[]


EndPackage[]

You can then use the package simply from a notebook by supplying the path to the sdf file (example here for simplistic database schema with a GUID in first column)

SdfFilename = FileNameJoin[{NotebookDirectory[], "pt2.sdf"}];
OpenSDFConnection[SdfFilename];
guids = GetAllGuids[] 

Hopefully future versions of the editors will avoid the syntax highlighting errors for Methods with the same name as builtin functions. Thanks for the help.

$\endgroup$
  • $\begingroup$ You could start with BeginPackage["VasomonDatabase`", {"NETLink`"}]. Alternatively put Needs["NETLink`"] before Begin["`Private`"], which will prevent the NETLink namespace from being made public when loading your package. Please do not put Needs inside of a function because it can make it difficult for your users to control when and whether that namespace gets made public. I am very frustrated with the GraphUtilities package loading Combinatorica this way ... I can't keep Combinatorica out f the context path because of it $\endgroup$ – Szabolcs Feb 9 '17 at 14:46
  • $\begingroup$ If you load NETLink the way I suggest above, then it will not be necessary to use fully qualified names for LoadNETType, etc. $\endgroup$ – Szabolcs Feb 9 '17 at 14:48
  • $\begingroup$ @Szabolcs. Thank you for your suggestions. Tested with Needs["NETLink"];` in the notebook and BeginPackage["VasomonDatabase", {"NETLink"}] and it runs fine. I have decided to keep the fully qualified names as the syntax highlighting in Eclipse shows the fully qualified name in black as it does for other built in functions. Shows up in blue if not fully qualified. $\endgroup$ – Richard Scott Feb 9 '17 at 15:05
  • $\begingroup$ Yes I agree about avoiding Needs in packages. In my limited experience I have resorted to using Get to load my custom external packages when executing, rather than an unknown version cached by Needs. Too easy to accidentally be using an old version when doing lots of edits. Helps reduce unloading of Kernel. $\endgroup$ – Richard Scott Feb 9 '17 at 15:11
  • $\begingroup$ If you're using Eclipse, also take a look at wlplugin.halirutan.de It doesn't have all the features of the Eclipse plugin and vice versa. When I don't need specific Eclipse features I much prefer the IDEA plugin. You can always talk to the author of the IDEA plugin in this chatroom. $\endgroup$ – Szabolcs Feb 9 '17 at 15:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.