adodb_tools
- 17 files
- 208 downloads
Package adodb_toolbox allows communication with different types of databases through Microsoft's ADO (ActiveX Data Objects) OLEDB component. The package was designed to work on Microsoft SQL Server, Oracle, Microsoft Access, MySQL and other databases.
This package can connect to dozen different database types, perform wide range of different query types and convert results to MATLAB Struct data structures as well as regular cell tables. Matlab struct output uses similar format as used by xml_io_tools and csv2struct libraries. Reading and writing BLOB objects is supported.
This package can be studied, modified, customized, rewritten and used in other packages without any limitations. All code is included and documented. Software is distributed under BSD Licence (included).
Cite As
Jaroslaw Tuszynski (2020). adodb_tools (https://www.mathworks.com/matlabcentral/fileexchange/29615-adodb_tools), MATLAB Central File Exchange. Retrieved .
Comments and Ratings (28)
Updates
| 1.1.0.0 | update MS Access connection strings and file formats to the ones used in Office 2010 |
MATLAB Release Compatibility
Platform Compatibility
Windows macOS LinuxCategories
Tags
Acknowledgements
Inspired by: database connection mfiles, Myblob, ADO OLE Database Connection
Discover Live Editor
Create scripts with code, output, and formatted text in a single executable document.
adodb/
adodb/html/
Select a Web Site
Choose a web site to get translated content where available and see local events and offers. Based on your location, we recommend that you select: .
Select web siteYou can also select a web site from the following list:
Americas
- América Latina (Español)
- Canada (English)
- United States (English)
Europe
- Belgium (English)
- Denmark (English)
- Deutschland (Deutsch)
- España (Español)
- Finland (English)
- France (Français)
- Ireland (English)
- Italia (Italiano)
- Luxembourg (English)
- Netherlands (English)
- Norway (English)
- Österreich (Deutsch)
- Portugal (English)
- Sweden (English)
- Switzerland
- United Kingdom (English)
Andrew Lawrenson (view profile)
Works a treat.
A useful wrapper around the adodb_query for cases were you frequently access the same database could be....
sqlQuery='SELECT * FROM xxx.dbo.xxx'
data=sqlQueryXXX_Function(sqlQuery) or [data, structure]=sqlQueryXXX_Function(sqlQuery)
function [Table, Struct]=sqlQueryXXX_Function(sqlQuery)
% MATLAB version R2019a
% PURPOSE: Extract data based on the given SQL query from the specified SQL database
%%
DB = adodb_connect('Provider=sqloledb;Data Source=xxx ;Initial Catalog=xxx ;User Id= xxx ;Password= xxx ;');
[Struct, TableCells] =adodb_query(DB,sqlQuery);
DB.release; %Access: Close DB connection
Table = cell2table(TableCells);
fieldList=fieldnames(Struct)';
Table.Properties.VariableNames=fieldList; %adds headers to the table
end
Sampath Vanimisetti (view profile)
Great tool.
Kavitha Guntaka (view profile)
My script always errors at this line:
if (ado_recordset.State && ado_recordset.RecordCount>0)
The recordset.RecordCount is not reading any value (empty), although my database shows the existence of data (executed the Sql query at the database).
Could you please provide me any suggestions.
Benjamin Grichting (view profile)
Nice and well working tool! One issue: JSON fields in MySQL/MariaDB seems not to be possible to read out. Is there some workaround?
Source: Microsoft Cursor Engine
Description: Multiple-step operation generated errors. Check each status value.
Eli Ashkenazy (view profile)
Sandeep Kumar Radha Krishnan (view profile)
Jaroslaw Tuszynski (view profile)
@ Darin McCoy: Sorry but I did not used this code in last 4-5 years, so I do not know.
Darin McCoy (view profile)
I've gotten this error a couple times... is there any way around this?
Invoke Error, Dispatch Exception:
Source: Microsoft Cursor Engine
Description: Out of memory.
javier cruz (view profile)
Good tool, works great with Windows 7 64 bits and Matlab R2011a!!
Karamos (view profile)
Hello Jaroslaw, could you please point me how to insert dates into SQLExpress.
I have tried the follow but is not working
sql=(['INSERT INTO ' tablename ' (Bid,DateTime) VALUES ('num2str(variable1) ',' char({datestr(now,'yyyy-mm-dd HH:MM:SS')})
')']);
adodb_query(DB, sql);
thanks
Jaroslaw Tuszynski (view profile)
Reply to Guillaume: Those functions do not use any toolboxes and rely on ActiveX Microsoft's ADO OLEDB tools which should be preinstalled on all Windows machines. May be you are running on non-windows machine? Or maybe there are some issues with your ADO OLEDB tools: I do not know much about them.
Guillaume (view profile)
Thank you for your reply. I understand for the 64-bit issue. Maybe I am not using the right string and that is what I am missing I think. There seems to be a lot of different connection strings for mySQL but none seems to work for me. Another point I wanted to ask, do I need the Matlab database toolbox to make this work and also a specific version of Matlab?
Thank you!
Jaroslaw Tuszynski (view profile)
Reply to Guillaume: I did not play with this code for some years now so all I can suggest is to:
1) verify format of the string using http://www.connectionstrings.com/ or other website.
2) Make sure all the components are either 64-bit or 32-bit. See my comment from 15 Jan 2014
Guillaume (view profile)
Hello, I ma new to linking matlab to a SQL database and I am getting what I think is a beginner error :
Error using COM.ADODB_Connection/Open
Invoke Error, Dispatch Exception:
Source: Microsoft OLE DB Provider for ODBC Drivers
Description: [Microsoft][Gestionnaire de pilotes ODBC] Source de
données introuvable et nom de pilote non spécifié
when I enter :
DB =
adodb_connect('Server=192.168.30.147;Database=****;Uid=****;Pwd=****;');
Could anyone help me configure my connection?
Thank you!
Elad (view profile)
Very nice toolbox. In some cases, I get the following error (on an mySQL database):
Error using Interface.Microsoft_ActiveX_Data_Objects_6.1_Library._Recordset/GetRows
Invoke Error, Dispatch Exception:
Source: Microsoft Cursor Engine
Description: Multiple-step operation generated errors. Check each status value.
Error in adodb_query (line 77)
table = ado_recordset.GetRows'; % retrieve data from recordset
Any ideas how I can debug this?
Leah (view profile)
Very useful tool. As Peter suggested it would be nice to have a insert for a table or structure for multiple records
I went ahead and made one since I needed it.
function adodb_insert(conn,tablename,colnames,data)
w=whos('data');
colnames_char=strjoin(colnames,', ');
switch w.class
case 'table'
rdata=table2cell(data);
case 'struct'
rdata=struct2cell(data);
end
B=cellfun(@isnumeric,rdata(1,:));
C=cellfun(@num2str,rdata(:,B),'UniformOutput',0);
rdata(:,B)=C;
%% MS SQL: Insert two new records
[m,~]=size(data);
for r=1:m
write_str=strjoin(rdata(r,:),''',''');
adodb_query(conn, ['INSERT INTO ' tablename ' (' colnames_char ') VALUES (''' write_str ''' ); ']);
end
Jaroslaw Tuszynski (view profile)
Reply to Bruce:
I had the same problem lately after upgrading to 64-bit computer and installing 64-bit MATLAB. Apparently my MS Office was 32-bit and neither this toolbox nor MATLAB Database Toolbox was able to work with it. I solved the problem by uninstalling 32-bit office and reinstalling 64-bit version.
Bruce (view profile)
Has anyone got this to work with an access2010 on a 64 bit system? Recently upgraded from 32 to 64 and can no longer connect to my access DBs.
Bruce (view profile)
Problem solved... Keywords are a pain :-)
Bruce (view profile)
Mostly great. But just tried a query that works in access itself, but fails with "Error using COM.ADODB_Connection/Execute
Invoke Error, Dispatch Exception: Unspecified error" when executed with adodb_query(sql)
Any thoughts on how to figure out what went wrong?
Dan Golding (view profile)
Fantastic tool, simple and fast!
Dennis (view profile)
Dana (view profile)
I have been using these tools for a while now and they have been working great. Unfortunately, they just gave me a new computer and now adodb_connect gives me an error. It was working fine on a 32bit Windows XP computer with MATLAB2010a. Now on a 64bit Windows 7 machine with MATLAB2010b I get the error:
??? Invoke Error, Dispatch Exception:
Source: Microsoft OLE DB Provider for ODBC Drivers
Description: [Microsoft][ODBC Driver Manager] Data source name not found and no default
driver specified
My connection string DOES specify the correct data source and the driver (which IS installed on the new machine).
Any suggestions?
Thanks
Jaroslaw Tuszynski (view profile)
Reply to Pete:
I was using this script to add a lot (~60k) of records to access DB, and use access as temporary memory storage to get some MATLAB codes to run on machined with 2GB or RAM. I did not notice any "extreme" slowness, but that might be relative.
The error you see might be due to MS access not supporting multiple queries.
Pete sherer (view profile)
This is a great script. However it is extremely slow when trying to multiple records to the accessDB. Is there a way to do insert into for multiple records?
Pref.MultipleQuery = true; is not working!
sql = 'INSERT INTO xport (ACCNT,Name) VALUES ("Pers","02")
INSERT INTO xport (ACCNT,Name) VALUES ("Cars","1008") ';
Got error:
??? Invoke Error, Dispatch Exception:
Source: Microsoft JET Database Engine
Description: Missing semicolon (;) at end of SQL statement.
Error in ==> adodb_query at 54
ado_recordset = ado_connection.Execute(sql);
Julian (view profile)
Thanks for this useful contribution -- I have been using ADO similarly for quite a while now. But I have found it broke in MATLAB R2010b onwards.
Have you tried your toolbox in a later version of MATLAB than R2010a?
Tony (view profile)
Great little script, much faster than the database toolbox supplied by matlab. Better documentation would be nice though.
Tony (view profile)