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

array81
Member
From: Italy
Registered: 2010-07-23
Posts: 411

SynDBExplorer use

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?

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: SynDBExplorer use

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.

Offline

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

array81
Member
From: Italy
Registered: 2010-07-23
Posts: 411

Re: SynDBExplorer use

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...

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: SynDBExplorer use

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.

Offline

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

array81
Member
From: Italy
Registered: 2010-07-23
Posts: 411

Re: SynDBExplorer use

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.

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: SynDBExplorer use

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.

Offline

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

array81
Member
From: Italy
Registered: 2010-07-23
Posts: 411

Re: SynDBExplorer use

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"?

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: SynDBExplorer use

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?

Offline

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

array81
Member
From: Italy
Registered: 2010-07-23
Posts: 411

Re: SynDBExplorer use

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?

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: SynDBExplorer use

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

Offline

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

array81
Member
From: Italy
Registered: 2010-07-23
Posts: 411

Re: SynDBExplorer use

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.

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: SynDBExplorer use

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.

Offline

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

array81
Member
From: Italy
Registered: 2010-07-23
Posts: 411

Re: SynDBExplorer use

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

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: SynDBExplorer use

Yes this is what I said..

Issue is within SynDBExplorer implementation, not SynDB itself.

I'll fix it ASAP.

Offline

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

array81
Member
From: Italy
Registered: 2010-07-23
Posts: 411

Re: SynDBExplorer use

Thank you. I will wait for it.

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: SynDBExplorer use

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.

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: SynDBExplorer use

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.

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: SynDBExplorer use

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

Offline

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

array81
Member
From: Italy
Registered: 2010-07-23
Posts: 411

Re: SynDBExplorer use

Thanks.

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

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: SynDBExplorer use

I did not try it.

But i guess yes!

Offline

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

array81
Member
From: Italy
Registered: 2010-07-23
Posts: 411

Re: SynDBExplorer use

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

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: SynDBExplorer use

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

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

Offline

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

array81
Member
From: Italy
Registered: 2010-07-23
Posts: 411

Re: SynDBExplorer use

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?

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: SynDBExplorer use

Until the TOleDBJetConnectionProperties class is fixed.

Offline

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

array81
Member
From: Italy
Registered: 2010-07-23
Posts: 411

Re: SynDBExplorer use

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?

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: SynDBExplorer use

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

This not a bug,but bydesign.

Offline

Board footer

Powered by FluxBB