Services & ResourcesWolfram ForumsMathGroup Archive

[Date Index] [Thread Index] [Author Index]

odbc vs jdbc for database access in Mathematica

• To: mathgroup at smc.vnet.net
• Subject: [mg38039] odbc vs jdbc for database access in Mathematica
• From: alexander.vorobiev at usa.net (Alexander Vorobiev)
• Date: Tue, 26 Nov 2002 00:50:35 -0500 (EST)
• Sender: owner-wri-mathgroup at wolfram.com

I have a Mathematica program that uses DatabaseAccess package to
retrieve large amount of data from Sybase database. DatabaseAccess is
available for Windows only since it uses odbc. Now, I wanted to run
the program under Linux so I used JLink for jdbc-based database
access. At the end of this message I'll insert the code that emulates
DataSourceEvaluate and OpenDataSource from DatabaseAccess but uses
jdbc. The implementation is simple and straightforward. The problem is
the code works MUCH slower than the equivalent odbc-based one on NT. I
retrieve A LOT of data so this is really annoying. So I have two
questions:

1. Is it one of the Mathematica functions that make it slow (the
conversion of the result of the sql query into Mathematica table) or
is it some fundamental java-related problem (JLink, jdbc etc)?

2. How can I make it run faster?

Thanks in advance and best regards,
Alexander

PS My correct email address is
"rze90bdsv02\[RawAt]sneakemail\[RawDot]com"

InstallJava[];
AddToClassPath[
    "jconn2.jar", "jdbc2_0-stdext.jar"];

LoadJavaClass["java.sql.Types"];
LoadJavaClass["java.util.Calendar"];

Clear[GetColumn];
GetColumn[i_, type_, rs_] := Switch[type, 
    Types`CHAR,rs@getString[i],
    Types`DOUBLE,rs@getDouble[i],
    Types`DATE,rs@getDate[i],
    Types`INTEGER,rs@getInt[i],
    Types`VARCHAR,rs@getString[i],
    Types`TIME,rs@getDate[i],
    Types`TIMESTAMP,JavaBlock[
      Module[{cal = Calendar`getInstance[]},cal@setTime[rs@getDate[i]];
        ToString[cal@get[Calendar`MONTH] + 1]<>"/"<>
          ToString[cal@get[Calendar`DATE]]<>"/"<> 
          ToString[cal@get[Calendar`YEAR]]]
      ],
    Types`NUMERIC,rs@getInt[i]]

ClearAll[DataSourceEvaluate];
DataSourceEvaluate[conn_,sql_String] :=
  JavaBlock[
    Module[{st = conn@createStatement[],rs,md, types,res},
      res = If [st@execute[sql], rs = st@getResultSet[];
          md = rs@getMetaData[];
          types = Array[md@getColumnType[#]&,{md@getColumnCount[]}];
          Rest[
            NestWhileList[
              Array[GetColumn[#,types[[#]], rs] &,{Length@types}] &,
              1,
              rs@next[] &]],
          {}];
      st@close[];
      res]]

Clear[OpenDataSource];
OpenDataSource[database_String, user_String,password_String] :=
    Module[{driver = JavaNew["com.sybase.jdbc2.jdbc.SybDriver"],
        dbUrl = "jdbc:sybase:Tds:"<>$DBServer<>":"<>$DBPort<>"/"<>database,
        prop = JavaNew["java.util.Properties"]},
      prop@setProperty["user",user];
      prop@setProperty["password",password];
      driver@connect[dbUrl,prop]];