MATLAB Answers

0

How do I connect JDBC with compiled application?

Asked by Adrien Corbin on 1 Apr 2019
Latest activity Commented on by Kojiro Saito on 9 Apr 2019
Hi,
We are using Matlab to process some data and want to output the result in a database.
To do so, we tried with the Database Toolbox. After some troubleshooting with JDBC Driver path which didn't seem to work out of the box, we have been able to run the script in Matlab this way:
conn = database(databasename, username, password, 'Server', server, 'PortNumber', port, 'Vendor', 'Microsoft SQL Server', 'AuthType', authType);
We are using the Matlab Compiler to produce an exe, the Compiler state that it should support the toolbox here.
When we do so, we still have the following error, which tell us the function does not seem to exists, an error we do not have in Matlab.
We also found this bug, but it is maked as fixed.
What can we do to troubleshoot this issue?
Thanks
For reference here a sample of the full script.
  • writetable were added only for debugging
  • datainsert is deprecated, sqlwrite should be used, but I could would not let me insert data only and was always generating a create table.
function [] = tampon(server, port, databasename, authType, username, password)
writetable(cell2table({'2'}),'log.txt','WriteVariableNames',false)
conn = database(databasename, username, password, 'Server', server, 'PortNumber', port, 'Vendor', 'Microsoft SQL Server', 'AuthType', authType);
writetable(cell2table({'3'}),'log.txt','WriteVariableNames',false)
load('output.mat');
writetable(cell2table({'4'}),'log.txt','WriteVariableNames',false)
output = output(:,1:5);
writetable(cell2table({'5'}),'log.txt','WriteVariableNames',false)
datainsert(conn,'dbo.test', cellstr(output.Properties.VariableNames), output);
writetable(cell2table({'6'}),'log.txt','WriteVariableNames',false)
close(conn);
writetable(cell2table({'7'}),'log.txt','WriteVariableNames',false)
end

  6 Comments

I have the following path in the log.txt
C:\Program Files\MATLAB\MATLAB Runtime\v96\mcr\toolbox\database\database\database.m
So, database command is pointing to the right m file.
How did you set up JDBC driver in MATLAB and how do you call compiled exe in Command Prompt?
We are using a console application.
We actually have 2, for testing purpose :
run.m -> run.exe
tampon('myserver',1234, 'mydatabase','Windows', 5)
tampon.m -> tampon.exe
function [] = tampon(server, port, databasename, authType, idEnvironment)
which database
conn = database(databasename, '', '', 'Server', server, 'PortNumber', port, 'Vendor', 'Microsoft SQL Server', 'AuthType', authType);
load('output.mat');
output = output(:,1:5);
datainsert(conn,'dbo.tampon', cellstr(output.Properties.VariableNames), output);
close(conn);
end
We are calling the exe directly for run.exe and tampon.exe with the following command in powershell
.\tampon.exe 'myserver' 1234 'mydatabase' 'Windows' 5
After a few changes with your "which" recommendation we have been able to proceed through missing "database" and "datainsert" references.
However we still can't connect, here is the log.txt output
C:\Program Files\MATLAB\MATLAB Runtime\v96\mcr\toolbox\database\database\database.m
Error using database.jdbc.connection/datainsert (line 35)
Invalid connection.
Error in tampon (line 11)
Error in run (line 2)
Note that the same parameters work in matlab. We might be facing our first problem with JDBC Driver. Where can I specify the driver to use in the Matlab Runtime as mentioned here at step 3.
Thanks for your help

Sign in to comment.

1 Answer

Answer by Kojiro Saito on 5 Apr 2019
 Accepted Answer

Regarding your last comment, from Powershell and Command Prompt, number is passed to compiled MATLAB function as characters, so, converting to double (str2double) is necessary.
function [] = tampon(server, port, databasename, authType, idEnvironment)
which database
if ischar(port)
port = str2double(port);
end
conn = database(databasename, '', '', 'Server', server, 'PortNumber', port, 'Vendor', 'Microsoft SQL Server', 'AuthType', authType)
If you still gets an error, add disp(conn) and confirm JDBC is included properly to compiled exe.
If conn says "JDBC not found error", please try adding "mssql-jdbc-6.2.1.jre8.jar" and "sqljdbc_auth.dll" in "Files required for your application to run" in Application Compiler Window.

  4 Comments

Show 1 older comment
Updated the JDBC version to 7.2.1.0 in the compiled version. Same error.
We have identified though that the default calalog is 'master' instead of 'mydatabase'. Something which does not happen in matlab.
In Matlab:
connection with properties:
DataSource: 'mydatabase'
UserName: ''
Driver: 'com.microsoft.sqlserver.j ...'
URL: 'jdbc:sqlserver://myserver: ...'
Message: ''
Type: 'JDBC Connection Object'
Database Properties:
AutoCommit: 'on'
ReadOnly: 'off'
LoginTimeout: 0
MaxDatabaseConnections: 32767
Catalog and Schema Information:
DefaultCatalog: 'mydatabase'
Catalogs: { 'mydatabase' ... and 72 more}
Schemas: {'db_accessadmin', 'db_backupoperator', 'db_datareader' ... and 31 more}
Database and Driver Information:
DatabaseProductName: 'Microsoft SQL Server'
DatabaseProductVersion: '14.00.2002'
DriverName: 'Microsoft JDBC Driver 7.2 ...'
DriverVersion: '7.2.1.0'
In compiled version:
connection with properties:
DataSource: 'mydatabase'
UserName: ''
Driver: 'com.microsoft.sqlserver.j ...'
URL: 'jdbc:sqlserver://myserver: ...'
Message: ''
Type: 'JDBC Connection Object'
Database Properties:
AutoCommit: 'on'
ReadOnly: 'off'
LoginTimeout: 0
MaxDatabaseConnections: 32767
Catalog and Schema Information:
DefaultCatalog: 'master'
Catalogs: {'mydatabase' ... and 72 more}
Schemas: {'db_accessadmin', 'db_backupoperator', 'db_datareader' ... and 10 more}
Database and Driver Information:
DatabaseProductName: 'Microsoft SQL Server'
DatabaseProductVersion: '14.00.2002'
DriverName: 'Microsoft JDBC Driver 7.2 ...'
DriverVersion: '7.2.1.0'
Is there anyway to set the default catalog explicitly?
Thanks
We added a simple query to change the default database. This way everything seems to be working.
sqlquery = ['USE ' databasename];
execute(conn,sqlquery)
Thanks for your help

Sign in to comment.