Lazarus

Programming => Databases => Topic started by: shobits1 on April 19, 2017, 09:37:51 pm

Title: Database Path contains special characters like ! (
Post by: shobits1 on April 19, 2017, 09:37:51 pm
I use TODBCConnection to connect/open MS-Access database (.mdb)... everything works great until I try to open Database inside directory having special characters in it's name.
like 'D:\The Path to DB )\DB.mdb'

After searching and searching, I can't find way to make this work. (the same DB can be opened by MS-Access 2010 with no problem)

any help.
Title: Re: Database Path contains special characters like ! (
Post by: HeavyUser on April 19, 2017, 10:09:22 pm
Quote from: shobits1 on April 19, 2017, 09:37:51 pm
I use TODBCConnection to connect/open MS-Access database (.mdb)... everything works great until I try to open Database inside directory having special characters in it's name.
like 'D:\The Path to DB )\DB.mdb'

After searching and searching, I can't find way to make this work. (the same DB can be opened by MS-Access 2010 with no problem)

any help.
Access does not use odbc try to use ADO instead.
Title: Re: Database Path contains special characters like ! (
Post by: shobits1 on April 19, 2017, 10:56:21 pm
How to use ADO in lazarus?
Is it possible to use TDataSource, TSQLQuery?
How much it defer from TODBCConnection, it term of code changes?

I have minimal knowledge of Database programing, and I have already done so much in my current project (almost completed), and I don't want make big changes.

Afaik, Zeos is the only component that uses ADO and it doesn't support (*.mdb) files - maybe I'm wrong -
Title: Re: Database Path contains special characters like ! (
Post by: LacaK on April 20, 2017, 07:38:32 am
There is no TADOConnection for FreePascal/Lazarus as ADO is Windows specific and not cross-platform.
TODBCConnection should work. It will help if you can debug your application and catch connection string which is passed to SQLDriverConnect() or at least give error message which you get plus properties of your TODBCConnection object.
Title: Re: Database Path contains special characters like ! (
Post by: shobits1 on April 20, 2017, 06:44:49 pm
I tried to debug TODBCConnection and catch the value of ConnectionString,, but for some reason the debugger won't let me -even thought, I rebuilt lazarus with debug profile-.

anyway, this is the message I get:
Quote
Could not connect with connection string "DRIVER={Microsoft Access Driver (*.mdb)};Dbq={D:\_Projects\SeesHelper\DATA\!SECG-ECO-2016.mdb};Uid=admin;Pwd=password;". ODBC error details: LastReturnCode: SQL_ERROR; Record 1: SqlState: HY000; NativeError: -1044; Message: [Microsoft][ODBC Microsoft Access Driver] Not a valid file name.; Record 2: SqlState: 01000; NativeError: 1; Message: [Microsoft][ODBC Microsoft Access Driver]General Warning Unable to open registry key 'Temporary (volatile) Jet DSN for process 0x2154 Thread 0x230c DBC 0x677b4c4 Jet'.;

and this is a cut down to how I open the DB:
Code: Pascal  [Select][+][-]
  1. procedure TfrmMain.OpenDataBase(ADBPath: string);
  2. begin
  3.   if not FileExists(ADBPath) then
  4.   begin
  5.     ShowMessage(msgFileNotFound);
  6.     Exit;
  7.   end
  8.  
  9.   //
  10.   // Close all TSQLQuery
  11.   //
  12.   dbConnection.Close(True);
  13.   dbConnection.Driver := 'Microsoft Access Driver (*.mdb)';
  14.   dbConnection.Params.Add('Dbq=' + UTF8ToCP1256(ADBPath) );
  15.   //This gives the same error
  16.   //dbConnection.Params.Add('Dbq=' + ADBPath );
  17.   dbConnection.Params.Add('Uid=admin');
  18.   dbConnection.Params.Add('Pwd=password');
  19.   dbConnection.Open
  20. end;
  21.  // No other parameters are changed in the ObjectInspector for dbConnection
  22.  

if I rename the file from !SECG-ECO-2016.mdb to SECG-ECO-2016.mdb,, the Database opens successfully.


[EDIT]
BTW, changing the driver to Microsoft Access Driver (*.mdb, *.accdb) gives this error message:
Quote
Could not connect with connection string "DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};Dbq={D:\_Projects\SeesHelper\DATA\!SECG-ECO-2016.mdb};Uid=admin;Pwd=password;". ODBC error details: LastReturnCode: SQL_ERROR; Record 1: SqlState: HY000; NativeError: 63; Message: [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0xb0c Thread 0x2104 DBC 0x13817434                                                              Jet'.; Record 2: SqlState: HY000; NativeError: 63; Message: [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0xb0c Thread 0x2104 DBC 0x13817434                                                              Jet'.; Record 3: SqlState: HY000; NativeError: -1044; Message: [Microsoft][ODBC Microsoft Access Driver] Not a valid file name.;
Title: Re: Database Path contains special characters like ! (
Post by: miab3 on April 20, 2017, 08:59:37 pm
@shobits1,

Try ZEOS and ADO.
Here I wrote about it:
http://zeoslib.sourceforge.net/viewtopic.php?f=40&t=67800#p86611 (http://zeoslib.sourceforge.net/viewtopic.php?f=40&t=67800#p86611)

Michal
Title: Re: Database Path contains special characters like ! (
Post by: shobits1 on April 20, 2017, 10:58:39 pm
Thank you, miab3
Yes it works with Zeos 7.2-beta but not the stable 7.1.4 (the one I was trying before); also I can use "Provider=Microsoft.Jet.OLEDB.4.0" which comes with WinXP and later. the only down side I see for now is that I have to rewrite all my TSQLQuery and use (I don't know) maybe TZQuery? TZSQLProcessor?! also there is the question of performance when using nested SQL

anyway,, thank you again... I'm interested if there is way to continue using ODBC is my case (do not want to do big changes at least for now).
Title: Re: Database Path contains special characters like ! (
Post by: miab3 on April 21, 2017, 12:17:35 am
@shobits1,

It also works with:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\Dane\ADDemo.mdb;Persist Security Info=False

I advise to use ZEOS 7.2 svn 3986:
https://sourceforge.net/p/zeoslib/code-0/HEAD/tree/branches/testing-7.2/ (https://sourceforge.net/p/zeoslib/code-0/HEAD/tree/branches/testing-7.2/)

Michal
Title: Re: Database Path contains special characters like ! (
Post by: shobits1 on April 21, 2017, 12:30:47 am
Quote from: miab3 on April 21, 2017, 12:17:35 am
It also works with:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\Dane\ADDemo.mdb;Persist Security Info=False
Yes, I know... I was making statement, not asking; sorry for the confusion.

Also, as side note for others; if DB has password, use Jet OLEDB:Database Password=mypassword;
Title: Re: Database Path contains special characters like ! (
Post by: LacaK on April 25, 2017, 07:48:38 am
Only tip: Try enclose path in double quotes: "":
  dbConnection.Params.Add('Dbq="' + UTF8ToCP1256(ADBPath) + '"');
Title: Re: Database Path contains special characters like ! (
Post by: shobits1 on April 25, 2017, 08:52:32 pm
Quote from: LacaK on April 25, 2017, 07:48:38 am
Only tip: Try enclose path in double quotes: "":
  dbConnection.Params.Add('Dbq="' + UTF8ToCP1256(ADBPath) + '"');
thank you,, but unfortunately it doesn't work.
TinyPortal © 2005-2018