Using database without ODBC

[rolfk]

---

@IDILIO wrote:

In this case It's not the way i retrieve data that has a lot more influence on the speed of the data retrieval, because in the reality i m not doing queries, but yes calling stored procedures/functions in the oracle database like Dennis suggests. So the problem isn't on the algorithm, but yes on the connection mode. All the code is totally optimized and there is no way to do fewer calls to the procedures, the time consumed is on the ODBC access time. I also try to use the Oracle ODBC drivers, but is exactly the same that the Microsoft driver.

By example i have a vi in LabView using the ODBC that takes around 4 minutes to do all the work, and the same algorithm in Java using JDBC takes just around 40 seconds.

And the ODBC that is necessarily slow comparing to a direct database access. Calling the same procedure, in LabView using ODBC takes around 150ms and directly on SQLplus just 20ms.

I m not familiarly with ADO, and i really don't know how to use them, but do you think that can help in this connection speed issue?

---

Well if you use the Database Connectivity Toolkit you are in fact already using ADO. Having directly a native ADO driver would probably be a bit faster than going throught the ADO to ODBC bridge to use the ODBC driver, but I can't seem to find a native ADO driver on the Oracle site.

That ODBC is almost always somewhat slower than using a native database interface is a quite safe assumption. So your next bet would be to use one of the direct Oracle drivers but that is a bit of serious work. You can probably use the Oracle Objects for OLE driver by interfacing its provided Oracle Data Control object with LabVIEWs ActiveX interface.

Or you could instead go for the ODP.NET driver and interface it with LabVIEW's .Net interface.

Both solutions will be close to the native Oracle database API and get you the best possible performance for a specific type of query or other SQL operation but you will need to do some serious work to create the interface in LabVIEW. Also since this is an Oracle specific interface, if you ever will have to change database provider you will have to adapt the entire interface accordingly whereas with using ADO or ODBC changes are often limited to minor SQL statements adaption.

As to the duration of your SQL operations if you do not parametrized insert/update or retrieve data (even stored procedures can and often do produce recordsets that then get retrieved) I have trouble to understand what you could otherwise do that would incur large performance hits caused by the ODBC driver itself.

Rolf Kalbermatter

Message Edited by rolfk on 01-27-2007 05:57 PM

[Patrick.Frank]

Data Source Names and System Data Sources were introduced with ODBC drivers but are not limited to them. ADO also can use directly ADO drivers to communicate with databases. From a users point of view it does not really matter if the connection ist done through ADO directly or through the ADO built in ODBC bridge through an ODBC driver.

---

Hello Rolf, can you give me a example how i can directly communicate with ADO drivers. At the moment i create a DSN with the ODBC driver and use the Connection String "DSN=myDB;" in the LAB-SQL Vi`s How i can directly connect to my .mde databse over ADO, without ODBC? Thank you Patrick

[mikeporter]

To develop a connection string for the Jet database engine (which is the database that Access is built on top of) do the following:

1. Create an empty text file on your desktop.
2. Change the extension of the file name from .txt to .udl - Windows will advise you that the file may become unusable but say ok. You will notice that the icon changes.
3. Double click on the file and click on the Provider tab.
4. In the list, select the Microsoft Jet 4.0 OLE DB Provider and click the Next >> button.
   
5. In the database name field, enter the path to your MDE file - there is also a button that allows you to brows to it.
6. Enter, as appropriate, the user name and password associated with the file, and click the Test Connection button to make sure everything works.
7. If all is well, click the OK button to close the window.
8. Now change the extension of the udl file back to txt, and double click on it again. This time it opens with notepad and the last line is your connection string...
   

By the way this basic trick works with any database...

Mike...

Message Edited by mikeporter on 06-28-2007 05:13 AM

[Patrick.Frank]

Hi,
i use a Microsoft Access Database (*.mde).

Patrick

[mikeporter]

Check my edited response. I spotted the reference to the MDE file after asking the question...

Mike...

[Patrick.Frank]

Thank You Mike,
it works fine.

That`s really a easy way to connect a database.

Best Regards
Patrick

[mikeporter]

No thanks are necessary -- just throw money... 

Mike...

[Benoit]

This is a litlle VI very cool to have!!