Is there any way to connect toad for mysql without using database toolbox?

connecting the database without using database toolbox

Answer 1

[Walter Roberson]

Yes.

If you are using MS Windows then you can use activexserver() to connect with a database program.

If you are not using Windows then you can use loadlibrary() to call into a .so (shared library) such as libsql or libmysql

[Guillaume]

You probably could use loadlibrary on windows as well but actxserver is probably easier.

I've always used actxserver to connect to databases (I don't have the database toolbox and I've never had the need for it).

Using ADO, the code would go something like:

connection = actxserver('ADODB.Connection');
connection.provider = '...';  %correct provider for your database. No idea what that is for mysql
connection.Open(yourdatabase);
recordset = connection.Execute('SELECT * FROM SomeTable');

[Sandeep Kumar Radha Krishnan]

@ Walter Roberson Thanks for your answer. I think the function ''actxserver'' is a function used to create the server. Is there any function to access/connect the existing database server especially for TOAD ? Or could you elaborate about this function with sample syntex?

[Sandeep Kumar Radha Krishnan]

@Guilaume Thanks for your input. Is the code ADO is a generic function?

[Guillaume]

actxserver is the matlab function to talk to any kind of activeX/COM provider. One of such provider written by microsoft is ADO (Activex Data Object). It is (one of) the APIs provided on Windows to communicate with any database. Full documentation of the API.

actxserver has nothing to do with database server. It can be used to talk with Excel, Word, database servers, and many other program. In my example, it is used to start the ADO library.

To connect with a mysql database, you probably have to use the ODBC provider (see Appendix A) which is the default. You then use the various ADO methods to manipulate your database.

I'm not sure what you're asking about TOAD. As far as I know it is a set of tools to manage your database which is irrelevant for talking to the database from other programs.

[Sandeep Kumar Radha Krishnan]

@Guilaume thanks for your kind support. I am getting * No public field database exists for class COM.ADODB_Connection* error when I am opening my database by using your command.

[Guillaume]

Looks like you wrote something like

connection.database = '...'

which is not what I wrote in my example and is not the way to specify the database.

I'm afraid you're going to have to read the documentation for ADODB (which I've linked to twice) and the documentation for whichever ODBC driver you use (e.g. mysql odbc driver) to figure out exactly how to connect to your database. From the above two, it looks like

connection = actxserver('ADODB.Connection');
connection.Open('DRIVER={MySQL ODBC 3.51 Driver};SERVER=LOCALHOST;DATABASE=yourdatabase;UID=username;PWD=password');

or something like that. See the mysql doc for an example of using ADO from VB, which is not much different than using it from matlab.

[Sandeep Kumar Radha Krishnan]

Hello, I am getting this following error when it trying to connect the database.

Error using COM.ADODB_Connection/Open
Invoke Error, Dispatch Exception:
Source: Microsoft OLE DB Provider for SQL Server
Description: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not
exist or access denied.

How to find the provider for the mysql-database?

[Walter Roberson]

Did you install MySQL ? https://dev.mysql.com/downloads/installer/

[Sandeep Kumar Radha Krishnan]

No.. but I am having TOAD for MySQL..

[Walter Roberson]

They just removed TOAD for MySQL; https://www.toadworld.com/products/toad-for-mysql

It appears to me that TOAD for MySQL is a package to manage MySQL databases, but that it is not the MySQL server itself. I think you still need to install the MySQL server.

[Sandeep Kumar Radha Krishnan]

If the MySQL server is installed, how the matlab script will communicate with the database especially after this syntax * 'connection.Open()'*? Can you explain with screenshot?

[Walter Roberson]

I cannot show a screenshot. I normally run on a Mac, which cannot use ActiveX from MATLAB. I could start up a Windows Virtual Machine, but I do not have the appropriate Microsoft products to test this -- Microsoft's licensing policies for Virtual Machines is not fun, and I have confirmed with Microsoft and an authorized Microsoft license partner that I would have to pay over $US4000 per year to access the products through my virtual machines -- which is better than the Microsoft site would suggest, as pricing through Microsoft says I would have to pay over $US11000 for two years.

With MySQL Server installed, you should no longer get the message

"SQL Server does not exist or access denied."

[Sandeep Kumar Radha Krishnan]

How to find out the provider name for MySQL? I was using * SQLOLEDB* and which will be provider for MicrosoftSQL server. Is SQLOLEDB will be same for both MySQL and MSSQL?

[Guillaume]

You're going to have to put some effort here and read the docs I've linked. If you're not willing to, you're better off buying the database toolbox.

I've already told you that the provider is the OLE DB provider for ODBC. As I said it's the default, meaning you don't even have to specify it.

To connect to a mysql database you obviously need mysql running and the mysql odbc driver I've linked to previously properly installed.

Then as I've said, in matlab:

connection = actxserver('ADODB.Connection');
connection.Open('DRIVER={MySQL ODBC 3.51 Driver};SERVER=LOCALHOST;DATABASE=yourdatabase;UID=username;PWD=password');

The following is a translation of the beginning of the VB example that I've also previously linked to

%connect to MySQL server using Connector/ODBC
conn = actxserver('ADODB.Connection');
conn.ConnectionString = 'DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=test;UID=venu;PWD=venu; OPTION=3';

conn.Open;

%create table
conn.Execute('DROP TABLE IF EXISTS my_ado');
conn.Execute('CREATE TABLE my_ado(id int not null primary key, name varchar(20), txt text, dt date, tm time, ts timestamp)');

%direct insert
conn.Execute('INSERT INTO my_ado(id,name,txt) values(1,100,''venu'')');
conn.Execute('INSERT INTO my_ado(id,name,txt) values(2,200,''MySQL'')');
conn.Execute "INSERT INTO my_ado(id,name,txt) values(3,300,''Delete'')');

rs = actxserver('ADODB.Recordset');
rs.CursorLocation = 'adUseServer';

%fetch the initial table ..
rs.Open('SELECT * FROM my_ado', conn);
fprintf('rs.RecordCount: %d\n', rs.RecordCount);
rs.MoveFirst;
fprintf([repmat('-', 1, 50), ' Initial my_ado Result Set ', repmat('-', 1, 50), '\n']);
for flditer = 1:rs.Fields.Count
   fld = rs.Fields.Item(flditer);
   fprintf('%s\t', fld.Name);
end
fprintf('\n');

while ~rs.EOF
   for flditer = 1:rs.Fields.Count
      fld = rs.Fields.Item(flditer);
      fprintf('%s\t', fld.Value);  
   end
   rs.MoveNext;
   fprintf('\n');
end
rs.Close;

Mistakes may have been made in the above. It's completely untested. I don't have mysql installed.

[Walter Roberson]

Thanks, Guillaume.

[Sandeep Kumar Radha Krishnan]

Thank you for your kind supports.

[George Corliss]

GREAT! You saved me a couple days' figuring out.

My slightly polished version of your Matlab code:

% See: Is there any way to connect to mysql without using database toolbox?
% https://www.mathworks.com/matlabcentral/answers/362076-is-there-any-way-to-connect-toad-for-mysql-without-using-database-toolbox


driver = '{MySQL ODBC 8.0 ANSI Driver}';
server = 'localhost';
dbName = 'test';
user   = 'venu';
passwd = 'venu';

% Connect to MySQL server using Connector/ODBC
conn = actxserver('ADODB.Connection');
conn.ConnectionString ...
    = [ 'DRIVER=' driver '; ' ...
        'SERVER=' server '; ' ...
        'DATABASE=' dbName '; ' ...
        'UID=' user '; ' ...
        'PWD=' passwd '; ' ...
        'OPTION=3' ];
fprintf('ConnectionString: %s\n', conn.ConnectionString);
conn.Open;

% Create table
conn.Execute('DROP TABLE IF EXISTS my_ado');
conn.Execute('CREATE TABLE my_ado(id int not null primary key, name varchar(20), txt text, dt date, tm time, ts timestamp)');

% Direct insert
conn.Execute('INSERT INTO my_ado(id, name, txt) values(1, 100, ''venu'')');
conn.Execute('INSERT INTO my_ado(id, name, txt) values(2, 200, ''MySQL'')');
conn.Execute('INSERT INTO my_ado(id, name, txt) values(3, 300, ''Delete'')');

rs = actxserver('ADODB.Recordset');
rs.CursorLocation = 'adUseServer';

% Fetch the initial table
query = 'SELECT * FROM my_ado;';
fprintf('Query: %s\n', query);
rs.Open(query, conn);
fprintf('rs.RecordCount: %d\n', rs.RecordCount);
rs.MoveFirst;
fprintf(['\n', repmat('-', 1, 10), ' Initial my_ado Result Set ', repmat('-', 1, 10), '\n']);
for flditer = 0 : rs.Fields.Count - 1
   fld = rs.Fields.Item(flditer);
   fprintf('%s\t', fld.Name);
end
fprintf('\n');
while ~rs.EOF
   flditer = 0;
   fld = rs.Fields.Item(flditer);
   fprintf('%2d\t', fld.Value);  
   for flditer = 1 : rs.Fields.Count - 1
      fld = rs.Fields.Item(flditer);
      fprintf('%s\t', fld.Value);  
   end
   rs.MoveNext;
   fprintf('\n');
end
rs.Close;
conn.Close;