Connect issue to SQL Server using x64bit Matlab

All,

I am trying to connect to a SQL Server using x64 bit Matlab. What frustrates me is that both versions see the dBase but only my x32 bit Matlab can connect (same machine Win 7). This holds true even when I load the dBase driver in the 'conn' statement.

conn = database('SeverName','r******er','**','com.microsoft.sqlserver.jdbc.SQLServerDriver')

But when I attempt to connect(using: exec(conn,'sqlquery')) I receive the following error: Message: 'Function requires three or five arguments to be defined'

What is even more frustrating is that I can easily connect to the same dBase and pull data using the x64-bit version of R.

Any suggestions?

Note: I have left certain words purposely for security..and its required by my firm. -------------- O/S = Windows 7 Pro x64 SQL = SQL 2008 R2; driver = SQL Server Native Client 10.0 ver: 2009.100.1600.01

Answer 1

[the cyclist]

I'm on a Mac and I am using postgresql, so not sure how related this is, but I have to add my JAR file to the Java path:

 javaaddpath('<path to file>/postgresql-8.3-604.jdbc3.jar');

Maybe that is different for your x64 than your x32?

[Oleg Komarov]

Windows Server 2008, Matlab 64bit, we had to add the *.jar file.

[R. Scott]

Thanks for the insight Cyclist & Oleg. I will try these solutions out. Q: Oleg this may seem redundant but which .jar file did you add? Was a specific one or standard. Thanks because the dataset is too large to load into the x32 bit version..

Answer 2

[Oleg Komarov]

1. Download ans save somewhere the Microsoft SQL Server JDBC Driver 3.0: the page contains a FAQ section and links to extensive documentation.
2. Add manually the fullpath of the .jar driver (we used sqljdbc4.jar) to matlabroot\toolbox\classpath.txt
3. Check that the path was added correctly (it took us several attempts) with:

javaclasspath

.

If you want to use windows authentication when connecting to MSSQL Server: add the directory of the .dll file supplied with the driver (refer to the link above for more details).

You may also want to try out my wrapper for MSSQL server-to-Matlab connectivity.

Oleg

[R. Scott]

Oleg: After following all of your steps incl adding it to classpath by

C:\Program Files\Microsoft JDBC Driver 4.0 for SQL Server\sqljdbc_4.0\enu\sqljdbc4.jar and even the 3.0 ver

Microsoft SQL Server JDBC Driver 3.0\sqljdbc_3.0\enu\sqljdbc4.jar

conn = database('F***1', 'r*****r', '**','com.microsoft.sqlserver.jdbc.SQLServerDriver','jdbc:microsoft:sqlserver://P-S-F***1:***3", "r****r", "**" ')

I am getting the following error

conn.Message

ans =

No suitable driver found for jdbc:microsoft:sqlserver://P-S-F***1:***3", "r****r", "**" '

did you add it to the 'Program Files' or 'Program Files(x86) folder.

Maybe I will give your wrapper a shot

Also i was incorrect in that it is a x64 bit data base I am connecting to

Thanks for your input as it has gotten me further than before... I may have to write it in x32 bit Maltab... any chance you would know how to sqlQuery in 'chunks'..

[Oleg Komarov]

Are you 100% sure the path was added correctly? (Try also with sqljdbc.jar)

I wrote a fcn to query in chunks, but it is not published on FEX. If you drop me an email I can send it to you.

[R. Scott]

Oleg: sorry for being MIA. I needed to do a few things in x32 fast so I had to sign out for a bit. As far as path the following is the finial lines in classpath.txt

$matlabroot/java/jarext/jogl.jar

mac=/System/Library/Java

maci=/System/Library/Java

maci64=/System/Library/Java

$matlabroot/toolbox/javabuilder/jar/javabuilder.jar

C:\Program Files\Microsoft JDBC Driver 4.0 for SQL Server\sqljdbc_4.0\enu\

C:\Program Files\Microsoft JDBC Driver 4.0 for SQL Server\sqljdbc_4.0\enu\sqljdbc4.jar

C:\Program Files\Microsoft SQL Server JDBC Driver 3.0\sqljdbc_3.0\enu\

C:\Program Files\Microsoft SQL Server JDBC Driver 3.0\sqljdbc_3.0\enu\sqljdbc4.jar

C:\Program Files\Microsoft JDBC Driver 4.0 for SQL Server\sqljdbc_4.0\enu\

C:\Program Files\Microsoft JDBC Driver 4.0 for SQL Server\sqljdbc_4.0\enu\sqljdbc.jar

C:\Program Files\Microsoft SQL Server JDBC Driver 3.0\sqljdbc_3.0\enu\

C:\Program Files\Microsoft SQL Server JDBC Driver 3.0\sqljdbc_3.0\enu\sqljdbc.jar

does this look normal?

[Oleg Komarov]

Yes it does look ok (if you copied it from cmd window after calling javaclasspath).

I think you're mistaking with the URL part, build it as:

URL = ['jdbc:sqlserver://' server ':' port ';database=' dbname '];

Or try my submission on the FEX.