How do I connect JDBC with compiled application?

2019 4 1
1 个回答
回答已采纳
更新时间：2022 2 1
11 次查看（30 天）

0 个投票

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 个评论
显示 4更早的评论 隐藏 4更早的评论

Kojiro Saito
Kojiro Saito 2019-4-2
Could you add the following line in your function
which database
and confirm Database Toolbox function is added compiled application properly?
Adrien Corbin
Adrien Corbin 2019-4-2
May I ask how do I verify that the toolbox is properly compiled into the application?
From what I understand, the "which" command will tell us if the proper package is installed.
How will I know once compiled, that the toolbox have been packaged.
Thanks
Kojiro Saito
Kojiro Saito 2019-4-2
Add "which database" in the first line in function tampon.
Then enable log file by checking in "Create log file" in Application Compiler Window (please see Additional Runtime Settings document). So result of "which database" is written in a log file of the compiled application.
Adrien Corbin
Adrien Corbin 2019-4-2
I have the following path in the log.txt
C:\Program Files\MATLAB\MATLAB Runtime\v96\mcr\toolbox\database\database\database.m
Kojiro Saito
Kojiro Saito 2019-4-3
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?
Adrien Corbin
Adrien Corbin 2019-4-4
编辑：Adrien Corbin 2019-4-5
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

请先登录，再进行评论。

请先登录，再回答此问题。

 采纳的回答

Kojiro Saito
Kojiro Saito 2019-4-5

0 个投票

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.

6 个评论
显示 4更早的评论 隐藏 4更早的评论

Adrien Corbin
Adrien Corbin 2019-4-5
Thanks for your help, we made a few steps forward. As usual, the tampon.m script is working, here is the last version.
function [] = tampon(server, port, databasename, authType, idEnvironment)
% Set path
javaaddpath('mssql-jdbc-6.2.2.jre8.jar');
% db inputs
% driver = 'com.microsoft.sqlserver.jdbc.SQLServerDriver';
which database
if ischar(port)
port = str2double(port);
end
conn = database(databasename, '', '', 'Server', server, 'PortNumber', port, 'Vendor', 'Microsoft SQL Server', 'AuthType', authType);
disp(conn)
load('output.mat');
output = output(:,1:5);
datainsert(conn,'dbo.tampon', cellstr(output.Properties.VariableNames), output);
close(conn);
end
But the compiled version does not work... For some reason the table is not detected properly. Could it be because the version 6.2.2.0 (compiled) vs 7.2.1.0 (in matlab)?
PS C:\Users\corbin\Downloads\tampon (3)> .\tampon.exe 'myserver' 1234 'mydatabase' 'Windows' 5
Warning: "mssql-jdbc-6.2.2.jre8.jar" is already specified on static java path.
> In javaclasspath
In javaclasspath
In javaclasspath
In javaaddpath (line 71)
In tampon (line 4)
C:\Program Files\MATLAB\MATLAB Runtime\v96\mcr\toolbox\database\database\database.m
connection with properties:
DataSource: 'mytable'
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 6.2 ...'
DriverVersion: '6.2.2.0'
Error using database.jdbc.connection/datainsert (line 76)
A processing error "Invalid object name 'dbo.tampon'." occurred.
Error in tampon (line 22)
database:insert:driverError
PS C:\Users\corbin\Downloads\tampon (3)>
I am currently looking into updating the runtime JDBC Driver, but have not found where yet.
Thanks
Adrien Corbin
Adrien Corbin 2019-4-5
编辑：Adrien Corbin 2019-4-5
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
Adrien Corbin
Adrien Corbin 2019-4-8
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
Michaela Konstantinou
编辑：Michaela Konstantinou 2022-2-1
When I try to use a database command in web app (.ctf) I have compiled including .jar .dll files I get the following error
Error using database (line 59)
2022-02-01 02:16:22 JDBC data source does not contain driver location. Use databaseConnectionOptions to specify JDBC driver location.
Do I have to save the datasources again inside the web app using saveAsDataSource(opts)? If so, I have tried it but when I set the connection options I dont know what value I should map to
'JDBCDriverLocation'
parameter in defining opts as shown in the code below
vendor = 'Microsoft SQL Server';
opts = databaseConnectionOptions("jdbc", vendor);
opts = setoptions(opts, 'DataSourceName' , datasourceName , ...
'DatabaseName' , databaseName , ...
'Server' , 'PRDMTLDB.XXXXX.com', ...
'AuthType' , 'Windows');
If I use the opts withouth defining driverPath (as shown above) I get the below error
Error using database.options.jdbc.sqlserver.SQLConnectionOptions/checkTestIsValid (line 40)
2022-02-01 02:22:22 DataSourceName, JDBCDriverLocation, Server and PortNumber are required parameters.
2022-02-01 02:22:22 Error in database.options.jdbc.sqlserver.SQLConnectionOptions/testConnection (line 97)
I have also restarted the server after the apps deployment (i.e., copy to the web apps folder).
Can you please help me with establishing an SQL connection from a web app?
Kojiro Saito
Kojiro Saito 2022-2-1
@Michaela Konstantinou
This document (Deploy Relational Database Application with MATLAB Compiler) will be helpful.
By adding SQL Server's JDBC driver (.jar file) to "Files installed for your end user" in Application Compiler, and "Files required for your app to run" in Web App Compiler, the JDBC driver will be included in the package.
Then, call dabase with database(databasename,username,password,Param1,ParamValue1,...,ParamN,ParamValueN) because it specifies JDBC connection.
conn = database(databaseName, userName, password, ...
'Vendor','Microsoft SQL Server','Server','PRDMTLDB.XXXXX.com', ...
'AuthType','Server','PortNumber', 1433);
Hope this helps.

请先登录，再进行评论。

更多回答（0 个）

类别

在 帮助中心 和 File Exchange 中查找有关 Database Toolbox 的更多信息

产品

版本

R2019a

标签

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by