(* :Title: Database Access Definitions for ISQL and MS SQLServer *) (* :Author: Jack Courtney *) (* :Summary: This package provides definitions that facilitate the use of a MS SQLServer database. They assume the availability of the MS utility ISQL.exe (whose path must be specified below, in isqlPath). There are similar utilities available for all serious database products, and the code included in this package should be adaptable to these other utilities. *) (* :Context: ISQL` *) (* :Package Version: 0.1 *) (* :Mathematica Version: 2.x, 3.0 *) (* :Copyright: Copyright (c) 1997, by Jack Courtney. All Rights Reserved. *) (* :History: 2/97 Created 6/97 Revised 8/97 Revised again Added db info query funvctions 1/98 Cleaned up SELECT query data return Added db Date to Mma Date conversion Added options to db info query functions Began data type handling - INCOMPLETE! *) (* :Keywords: Database, SQLServer, ISQL *) (* :Current Restrictions: Fully tested only with SQL Server 6.0 & 6.5 *) (* :ToDo: Encapsulate data type handling completely, so that conversions are transparent to the user. Add specific data design and data update functions. *) BeginPackage[ "ISQL`Common`" ]; Off[General::spell1]; (************************ ** Usage declarations: ** ************************) Trim::usage="Trim[ s_String ] returns s with leading and trailing whit\ e space removed."; StringToDate::usage="StringToDate[ dbDate_ String ] accepts a date str\ ing in the default db format and returns it as a Date."; (********************************************************************** ** All databases are referenced by the use of the following pattern: ** **********************************************************************) db={ Server_String , Database_String , UserID_String , Pwd_String , LineLength_Integer , PacketSize_Integer , FieldSeparator_String (* Must be a single character *) } /; StringLength[FieldSeparator]==1 ; isqlBatch::usage="isqlBatch[ database:db, in_String, out_String ] read\ s query from 'in' to database:db, prints the query result to 'out' and\ returns the result code (=0 for no errors)."; isqlStringTableBatch::usage="isqlStringTableBatch[ database:db, in_Str\ ing, out_String ] reads query from 'in' to database:db, prints the que\ ry result to 'out' and returns the result as a 2D array of Strings (on\ e String for each field of each record)."; isql::usage="isql[ database:db, qry_String ] passes 'qry' to 'database\ ' and returns the result as a list of Strings (one String for each rec\ ord). The records are delimited by '\n'."; isqlStringTable::usage="isqlStringTable[ database:db, qry_String ] pas\ ses 'qry' to 'database' and returns the result as a 2D array of String\ s (one String for each field of each record)."; isqlTable::usage="isqlTable[ database:db, qry_String ] passes 'qry' to\ 'database' and returns the result as a 2D array of Expression (one Ex\ pression for each field of each record)."; isqlTableList::usage="isqlTableList[database:db, opts___] returns a li\ st of the tables in the database."; TableOwner::usage="Option for isqlTableList. If All, then all table n\ ames are returned; otherwise, only tables owned by the specified owner\ are returned. For example, TableOwner->\"MatthiasGrunewald\" will ret\ urn only tables owned by Herr Grunewald."; TableTypes::usage="Option for isqlTableList. If All, then all table t\ ypes are returned; otherwise, only tables of the listed type are retur\ ned. For example, TableTypes->All is equivalent to TableTypes->{Syst\ emTables, OtherTables, Views}."; Remarks::usage="Option for isqlTableList. If true, then table comment\ s are returned."; Options[isqlTableList]={ TableOwner->All , TableTypes->{SystemTables,OtherTables,Views} , Remarks->False }; isqlColumnList::usage="isqlColumnList[ database:db, obj_String, opts__\ _ ] returns a list of the fields in obj (a table or view), together wi\ th detailed information about the datatypes."; ObjectOwner::usage="Option for isqlColumnList. For example, ObjectOwn\ er=>\"MatthiasGrunewald\" will return only tables owned by Herr Grunew\ ald. ObjectOwner->All is not allowed."; Options[isqlColumnList]={ ObjectOwner->{"dbo"} }; (***************************** ** Local path for isql.exe: ** *****************************) isqlPath="d:/mssql/binn/isql.exe"; (* The complete path on the client machine. *) (***************** ** Other stuff: ** *****************) (* Not currently in use: DefaultDb = { "127.0.0.1" , "pubs" , "sa" , "" , LINELENGTH=10000; (* ... to avoid various formatting problems *) , PACKETSIZE=8192; (* ... likely depends on OS and on network protocol. *) , FIELDSEPARATOR="`" (* Choose a single character that is unlikely to appear within your data. *) }; *) Begin["`Private`"] (*********************************** ** String Manipulation Utilities: ** ***********************************) DropLeading[str_String]:= Module[ {ss,s,c} , If[ StringLength[str]==0, Return[""] ]; ss=StringToStream[str]; s=Read[ ss, Word, WordSeparators->{" ","\t"} ]; Close[ss]; If[ (s==="")||(s==EndOfFile), Return[""] ]; Return[ StringDrop[ str, {1,StringPosition[str,s][[1,1]]-1}] ] ]; DropTrailing[ str_String ]:=StringReverse[DropLeading[StringReverse[str]]]; Trim[ str_String ]:=DropTrailing[ DropLeading[str] ]; (***************** ** public stuff ** *****************) isql::aborted="ISQL error code <`1`>."; isqlBatch[ db, in_String, out_String ]:= Module[ {rc,s, f} , rc=Run[ isqlPath , "-S", Server , "-d", Database , "-U", UserID , "-P", Pwd , "-w", LineLength , "-i", in , "-a", PacketSize , "-s", FieldSeparator , "-n" (*, "-r"*) , "-o", out ]; Return[ rc ] ]; isql[ db, qry_String ]:= Module[ {rc,s, f=OpenTemporary[], tmpname} , tmpname = StringReplace[ f[[1]], {$PathnameSeparator->"/"} ]; Close[f]; rc=Run[ isqlPath , "-S", Server , "-d", Database , "-U", UserID , "-P", Pwd , "-w", LineLength , "-Q", StringJoin["\"",qry,"\""] , "-a", PacketSize , "-s", FieldSeparator (*, "-r"*) , "-o", tmpname ]; If[ rc!=0 , s=""; Message[ isql::aborted, rc ] , s=ReadList[ f=OpenRead[tmpname], String ]; If[ Length[s]>=2 , s=Drop[s,-2] ]; Close[f] ]; DeleteFile[tmpname]; Return[ s ] ]; (***************************************** ** The following is a private function: ** *****************************************) isqlST[ ii_List, FSChar_String ]:= Module[ {data} , data=(Trim /@ ReadList[ StringToStream[#], String, NullRecords->False])& /@ (StringReplace[ #, {FSChar->"\n"}]& /@ Drop[ii,{2}]); If[ data=="", data={{}}]; Return[ data ] ]; (********************** ** These are public: ** **********************) isqlStringTableBatch[ database:db, in_String, out_String ]:= isqlST[ isqlBatch[database,in,out], database[[-1]] ]; isqlStringTable[ database:db, qry_String ]:= isqlST[ isql[database,qry], database[[-1]] ]; isqlTable[ database:db, qry_String ]:= Module[ {ii, data, FSChar=database[[-1]]} , If[ (Max[ StringLength /@ (ii=isql[database,qry]) ]==0) , data={{}} , data=Prepend[ ReadList[StringToStream[#], NullRecords->False]& /@ (StringReplace[ #, {FSChar->"\n"}]& /@ Drop[ii,2]) , Trim /@ ReadList[ StringToStream[StringReplace[ ii[[1]],{FSChar->"\n"}]], String] ] ]; Return[ data ] ]; StringToDate[ dbDate_String ]:= Module[ { MONTHS={"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"} , M,D,Y,h,m, date=Trim[dbDate] } , M=Position[MONTHS,StringTake[date,3]][[1,1]]; D=ToExpression[StringTake[date,{5,6}]]; Y=ToExpression[StringTake[date,{8,11}]]; h=ToExpression[StringTake[date,{13,14}]]; m=ToExpression[StringTake[date,{16,17}]]; If[ StringTake[date,{18}]=="P", h=h+12]; Return[ {Y,M,D,h,m,0} ] ]; (************************************ ** Database Information Functions: ** ************************************) isql::BadOption="Bad `2` option (`1`) for isqlTableList."; isqlTableList[ database:db, opts___Rule ]:= Module[ { d, to, tt, rm, tos } , to = TableOwner/.{opts}/.Options[isqlTableList]; tt = TableTypes/.{opts}/.Options[isqlTableList]; rm = Remarks/.{opts}/.Options[isqlTableList]; If[ (Head[tt]!=List) && (tt!=All) , Message[ isql::BadOption, tt, "TableType" ]; Abort[] ]; If[ to==All , tos="'%'" , If[ Head[to] != String , Message[ isql::BadOption, to, "TableOwner" ]; Abort[] , tos = to ] ]; d=Drop[#,1]& /@ isqlStringTable[database,StringJoin["sp_tables '%',",tos]]; If[ Head[tt]==List , If[ !MemberQ[ tt, SystemTables ], d=DeleteCases[ d, {___,"SYSTEM TABLE",___} ] ]; If[ !MemberQ[ tt, OtherTables ], d=DeleteCases[ d, {___,"TABLE",___} ] ]; If[ !MemberQ[ tt, Views ], d=DeleteCases[ d, {___,"VIEW",___} ] ]; ]; If[ !rm, d=Drop[#,-1]& /@ d ]; Return[d] ]; isqlColumnList[ database:db, obj_String, opts___Rule ] := Module[ {oo} , oo = ObjectOwner/.{opts}/.Options[isqlColumnList]; If[ !((Head[oo]==List) && (Length[oo]==1) && (Head[oo[[1]]]==String)) , Message[ isql::BadOption, oo, "ObjectOwner" ]; Abort[] ]; Return[isqlStringTable[database, StringJoin["sp_columns '",obj,"','",oo,"' "]]]; ]; (*************************** ** Data type conversions: ** ***************************) (* The basic type conversions are defined by type name MmaType 34 image 37 varbinary 45 binary 50 bit Boolean 45 timestamp (Date) !!! Really a binary integer 58 smalldatetime Date 61 datetime Date 111 datetimn Date 38 intn Integer 48 tinyint Integer 52 smallint Integer 56 int Integer 55 decimal Real 59 real Real 60 money Real 62 float Real 63 numeric Real 106 decimaln Real 108 numericn Real 109 floatn Real 110 moneyn Real 122 smallmoney Real 35 text String 39 id String 39 sysname String 39 tid String 39 varchar String 47 char String 47 empid String *) Numbers = {38,48,52,55,56,59,60,62,63,106,108,109,110,122}; Dates = {45,58,61,111}; Strings = {35,39,47}; Unknowns = {34,37,45}; Booleans = {50}; isqlColumnList[ database:db, obj_String, opts___Rule ] := Module[ {oo} , oo = ObjectOwner/.{opts}/.Options[isqlColumnList]; If[ !((Head[oo]==List) && (Length[oo]==1) && (Head[oo[[1]]]==String)) , Message[ isql::BadOption, oo, "ObjectOwner" ]; Abort[] ]; Return[isqlStringTable[database, StringJoin["sp_columns '",obj,"','",oo,"' "]]]; ]; End[]; (* Protect[ initDB, isql, isqlStringTable, isqlTable ]; *) On[ General::spell1 ]; EndPackage[];