SynDBExplorer use

array81 · 2012-06-07 22:38:47

I don't have expirence with OLEDB but I need use the framework to open a mdb (MS Access) databse protected by password.
To start understand how the framework works I have try to open the databse with your SynDBExplorer.

I have set Ident, Connection (to Generic OLEDB) and Server:

Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\mydb.mdb;Jet OLEDB:Database Password=123456;Persist Security Info=False;

However I get an error "OLEDB Error 80040E21".

Do you have some ideas about it?

ab · 2012-06-08 06:31:04

You did well: the "Server" field shall contain the OleDB connection string, in case of a "Generic OLEDB" connection.

If you click on "Update connection settings", it should display the OLEDB connection box, and you'll be able to tune the connection string.

I never used a mdb database, so I could not help you here.

array81 · 2012-06-08 07:31:00

When click on "Save" button I see "OLEDB connection box". But when I click on "OK" button (of "OLEDB connection box") I get the error...

ab · 2012-06-08 08:47:32

AFAIK you should be able to set and test the properties in the OLEDB connection box.

If the properties are wrong, pressing OK will get the error.

So review the properties in this OLEDB connection window before pressing "OK".
Something is wrong in your connection string.

array81 · 2012-06-08 09:20:06

If I test the connection from "OLEDB connection box" (in there a button) is all OK, but when I click on OK SynDBExplorer get the error.

ab · 2012-06-08 09:24:51

Could you step the debugger within ConnectionStringDialogExecute method and check the connection string returned by DataInitialize.GetInitializationString(), into tmp/fConnectionString.

If it sounds OK, then please try with another mdb file.

array81 · 2012-06-08 12:27:37

I have make the debug, fConnectionString is same my string on Server.
However I get an error on line 1855 of SynOleDB.pas.

So I have try another database (without password), but I have the same error. I don't understand if I make some error or if there is a problem on framework.

You can download the new database here: http://dl.dropbox.com/u/19900180/Nwind.mdb
And this is the string:

Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Nwind.mdb;Persist Security Info=False;

Another think, is possible avoid to see the "OLEDB connection box"?

ab · 2012-06-08 12:33:00

The "OLEDB connection" dialog is only shown in SynDBExplorer, if you check the "Update connection settings" at startup.
If you do not check this "Update connection settings", you won't let it appear.

About the connection string, I do not know Jet/MSAcess but it sounds right.
- Where is the exact error triggered (with call stack: at connection opening, at SQL execution...)?
- Are you able to access it using ADO via the same connection string, for instance?

array81 · 2012-06-08 12:58:28

I have make a test. I have download this small and freeware tool http://www.torry.net/dbtools/ado/query/ … ep2008.zip (ADO Query Tool v.2.1.1.0 Beta).
So I have test my "server string" and my database. It works. I can connect with database and see the tables. So the "server string" works.

All works both with first database (password protected) and second database (no protected).

So I think is there same problem on framework.

Can I help you to find the problem? How?

ab · 2012-06-08 15:05:41

Where is the exact error triggered (with call stack: at connection opening, at SQL execution...)?

array81 · 2012-06-08 15:14:52

I think on "at connection opening" because I get the error after click on "OK" button of "OLEDB connection box". I hope I have understand your question.
I get error on OleDBCheck procedure.

ab · 2012-06-08 15:47:10

Could you step in with the debugger, and see exactly where the error is?

When you press on the OK button, SynDBExplorer does:
- Initiate a connection;
- Launch a SQL statement for retrieving the tables layout of the DB.

Perhaps the 2nd item is failing.

array81 · 2012-06-08 16:53:57

I think the problem is Props.GetTableNames(C.fTableNames); of SynDBExplorer.
I hope this can help you.

ab · 2012-06-08 17:49:12

Yes this is what I said..

Issue is within SynDBExplorer implementation, not SynDB itself.

I'll fix it ASAP.

array81 · 2012-06-08 18:07:34

Thank you. I will wait for it.

ab · 2012-06-09 16:17:21

SQL queries were working as expected.
But OleDB metadata has issues with non MS SQL DB.

With the following fix SynDBExplorer is able to work with such files.
http://synopse.info/fossil/info/5c0148d3e8

But it is still unable to retrieve the list of available tables... sounds like if it does not like the fact that MSAccess database tables do not have schema/owner.
I'll investigate further.

Sounds like if MDB files over OleDB is more or less on the same speed than SQLite3, at least for small requests.

ab · 2012-06-09 20:42:28

OK - Jet/MSAccess kind of database did raise some unexpected issues.

I think the root causes have been identified and fixed:
* fix issue (depending on OleDB Provider) about ordinal binding error
* fix retrieval of table and field metadata, for tables without schema (e.g. Jet/MSAccess database)
See http://synopse.info/fossil/info/32430cc1e7

Now you can use MSAccess/mdb databases within SynDBExplorer, therefore also within mORMot ORM.
I'll probably add explicit connection properties for MSAccess in SynOleDB.

ab · 2012-06-10 13:38:25

I've added TOleDBJetConnectionProperties kind of connection to direct access of Microsoft Jet databases (.mdb files).
It should be easier than ever to handle such kind of DB with mORMot / SynDB units.

See http://synopse.info/fossil/info/eaa62aaf89

array81 · 2012-06-10 22:25:52

Thanks.

A question Can I use "Jet - mdb OLEDB" with a database protected by password? (see ADO like connect string of first post).

ab · 2012-06-11 05:55:29

I did not try it.

But i guess yes!

array81 · 2012-06-11 07:26:12

And where I set the password? I have try to set on password field but it doesn't work.

ab · 2012-06-11 13:50:01

Did you try setting the User (Admin?) + Password fields?

I think the Jet syntax is not the OleDB standard.
Need to fix it.

array81 · 2012-06-11 14:01:28

ab wrote:

Did you try setting the User (Admin?) + Password fields?
I think the Jet syntax is not the OleDB standard.
Need to fix it.

Yes.

So I need use "Generc OLEDB", right?

ab · 2012-06-11 14:11:29

Until the TOleDBJetConnectionProperties class is fixed.

array81 · 2012-06-11 22:04:43

I don't understand if I have found a bug.
TSQLDBStatement.TotalRowsRetrieved return always 0 until I call TSQLDBStatement.Step, how can I know the row number?

ab · 2012-06-11 23:32:55

Before Step there is no result available.
Statement is prepared but not yet executed.

This not a bug,but bydesign.

mORMot Open Source

#1 2012-06-07 22:38:47

SynDBExplorer use

#2 2012-06-08 06:31:04

Re: SynDBExplorer use

#3 2012-06-08 07:31:00

Re: SynDBExplorer use

#4 2012-06-08 08:47:32

Re: SynDBExplorer use

#5 2012-06-08 09:20:06

Re: SynDBExplorer use

#6 2012-06-08 09:24:51

Re: SynDBExplorer use

#7 2012-06-08 12:27:37

Re: SynDBExplorer use

#8 2012-06-08 12:33:00

Re: SynDBExplorer use

#9 2012-06-08 12:58:28

Re: SynDBExplorer use

#10 2012-06-08 15:05:41

Re: SynDBExplorer use

#11 2012-06-08 15:14:52

Re: SynDBExplorer use

#12 2012-06-08 15:47:10

Re: SynDBExplorer use

#13 2012-06-08 16:53:57

Re: SynDBExplorer use

#14 2012-06-08 17:49:12

Re: SynDBExplorer use

#15 2012-06-08 18:07:34

Re: SynDBExplorer use

#16 2012-06-09 16:17:21

Re: SynDBExplorer use

#17 2012-06-09 20:42:28

Re: SynDBExplorer use

#18 2012-06-10 13:38:25

Re: SynDBExplorer use

#19 2012-06-10 22:25:52

Re: SynDBExplorer use

#20 2012-06-11 05:55:29

Re: SynDBExplorer use

#21 2012-06-11 07:26:12

Re: SynDBExplorer use

#22 2012-06-11 13:50:01

Re: SynDBExplorer use

#23 2012-06-11 14:01:28

Re: SynDBExplorer use

#24 2012-06-11 14:11:29

Re: SynDBExplorer use

#25 2012-06-11 22:04:43

Re: SynDBExplorer use

#26 2012-06-11 23:32:55

Re: SynDBExplorer use

Board footer