Obtaining Driver parameter for TODBCConnection
lazarus / 16-Databases

----------------------------------------------------------------------
konikula · 2023-07-18 11:00

I am trying to use MDB file format for my 32 bit desktop application
as an regular output files. Application simply produces user backups
as MDB files and is able to restore valuable informations from MDB
files if needed. On different machines there is different Driver
available. I use following code to check available Driver and connect
with my file:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  
 connAccess.Driver:='Microsoft Access Driver (*.mdb, *.accdb)'; 
 connAccess.Params.Add('DBQ='+dbpath); 
 connAccess.Params.Add('DSN=test');   connAccess.Params.Add('Locale
Identifier=1031');   connAccess.Params.Add('ExtendedAnsiSQL=1'); 
 connAccess.Params.Add('CHARSET=ansi');   try     
connAccess.Connected := True;   except      on E: Exception do begin 
     connAccess.Driver:='Microsoft Access Driver (*.mdb)';       
connAccess.Connected := True;      end;   end;  
 connAccess.KeepConnection := True;    trans :=
TSQLTransaction.Create(pForm);
It would be more practical to obtain available Drivers and select
some. Like this I am now successfull in establishing connection, but
even if I used try-except mechanism, some error alert occurs when
"Microsoft Access Driver (*.mdb, *.accdb)" wasn't correct Driver. It
shows up with some prompt when trying to "connAccess.Connected :=
True;"

Does anyone have solution without error prompt? I would welcomed also
if someone has a way to find available Drivers programatically.

----------------------------------------------------------------------
VB_Lazarus · 2023-07-18 11:15

Hi,

as I know you use the driver for 64 bit desktop application.
You have to use the following driver:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
connAccess.Driver:='Microsoft Access Driver (*.mdb)'; 
best regards

----------------------------------------------------------------------
konikula · 2023-07-18 11:24

Thank you for your suggestion. I try both Drivers, 64 bit and 32 bit,
because I want to reach as many users as possible. What is real
problem, that statement "connAccess.Connected := True;" is showing
error prompt even if inside try-except...

best regards

----------------------------------------------------------------------
rvk · 2023-07-18 11:26

Quote from: konikula on July 18, 2023, 01:24:59 pmThank you for your
suggestion. I try both Drivers, 64 bit and 32 bit, because I want to
reach as many users as possible. What is real problem, that statement
"connAccess.Connected := True;" is showing error prompt even if inside
try-except...
That's only inside the IDE.
If you run it outside the IDE you won't get the dialog.
You can disable the dialog for that exception in the options in the
IDE if you want.

----------------------------------------------------------------------
Zvoni · 2023-07-18 11:34

or Query the Registry
Code-Snippet
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i Uses
Registry; const  {$ifdef WIN32}    ThisExeIs32bit=true;   
ODBCKey='Software\WOW6432Node\ODBC\ODBCINST.INI\ODBC Drivers';   
KEY_ODBC=KEY_WOW64_32KEY;  {$else}    ThisExeIs32bit=false;   
ODBCKey='Software\ODBC\ODBCINST.INI\ODBC Drivers';   
KEY_ODBC=KEY_WOW64_64KEY;  {$endif} Procedure GetODBCDrivers(var
DriverList:TStringList);Var   Reg:TRegistry;begin 
Reg:=TRegistry.Create(KEY_READ Or KEY_QUERY_VALUE Or
KEY_ENUMERATE_SUB_KEYS Or KEY_ODBC);  DriverList:=TStringList.Create; 
DriverList.Sorted := True;  Try    Reg.RootKey := HKEY_LOCAL_MACHINE; 
  If Reg.OpenKeyReadOnly(ODBCKey) Then      Begin       
Reg.GetValueNames(DriverList);        //DriverList.Add('(None)');    
end;    Reg.CloseKey;  finally    Reg.Free;  end;end;

----------------------------------------------------------------------
konikula · 2023-07-20 07:36

For me it works, once compiled to 32bit application, on Windows XP,
and on Windows 11. I had only to rewrite your ifdef section to
"Software\ODBC\ODBCINST.INI\ODBC Drivers", so that it looks like this:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i const 
{$ifdef WIN32}    ThisExeIs32bit=true;   
ODBCKey='Software\ODBC\ODBCINST.INI\ODBC Drivers';   
KEY_ODBC=KEY_WOW64_32KEY;  {$else}    ThisExeIs32bit=false;   
ODBCKey='Software\ODBC\ODBCINST.INI\ODBC Drivers';   
KEY_ODBC=KEY_WOW64_64KEY;  {$endif}
I made a research and it looks like both, windows - XP and 11 use same
address in registry to store list of drivers.

When I said I want to reach more users, I didn't mean compiling my
application for 32bit and also 64bit systems, I rather wanted to
produce 32 bit application, which would run on most of windows
systems.

I appreciate your help. I think this topic is already solved.

Best regards

----------------------------------------------------------------------
Zvoni · 2023-07-20 08:54

Quote from: konikula on July 20, 2023, 09:36:08 amFor me it works,
once compiled to 32bit application, on Windows XP, and on Windows 11.
I had only to rewrite your ifdef section to
"Software\ODBC\ODBCINST.INI\ODBC Drivers", so that it looks like this:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i const 
{$ifdef WIN32}    ThisExeIs32bit=true;   
ODBCKey='Software\ODBC\ODBCINST.INI\ODBC Drivers';   
KEY_ODBC=KEY_WOW64_32KEY;  {$else}    ThisExeIs32bit=false;   
ODBCKey='Software\ODBC\ODBCINST.INI\ODBC Drivers';   
KEY_ODBC=KEY_WOW64_64KEY;  {$endif}
I made a research and it looks like both, windows - XP and 11 use same
address in registry to store list of drivers.

When I said I want to reach more users, I didn't mean compiling my
application for 32bit and also 64bit systems, I rather wanted to
produce 32 bit application, which would run on most of windows
systems.

I appreciate your help. I think this topic is already solved.

Best regards
Uh, No!
My Path with WOW6432Node is for Windows-OS which exist in Both
bitnesses.
If you have to check for XP, i'd go with checking OS-Version

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i {$ifdef
WIN32}    ThisExeIs32bit=true;   
ODBCKey='Software\WOW6432Node\ODBC\ODBCINST.INI\ODBC Drivers';   
KEY_ODBC=KEY_WOW64_32KEY;  {$else}    ThisExeIs32bit=false;   
ODBCKey='Software\ODBC\ODBCINST.INI\ODBC Drivers';   
KEY_ODBC=KEY_WOW64_64KEY;  {$endif}This will work on both, e.g.
Windows 7 32Bit and Windows 7 64Bit
resp. on Win64Bit, but your compile Target is 32-Bit
The goal is: If you have 32-Bit App, use the 32-Bit driver
(independent if the OS is 32 or 64 Bit), if App is 64 Bit (and the OS
must be 64Bit), use the 64Bit driver

----------------------------------------------------------------------
konikula · 2023-07-20 09:14

But if I compile and run it on 32bit Windows XP,
"Software\WOW6432Node" is searched and not found.

----------------------------------------------------------------------
Zvoni · 2023-07-20 09:17

Quote from: konikula on July 20, 2023, 11:14:11 amBut if I compile and
run it on 32bit Windows XP, "Software\WOW6432Node" is searched and not
found.
That's why i said to check of OS-Version additionally!
https://forum.lazarus.freepascal.org/index.php?topic=15390.0
https://wiki.freepascal.org/WindowsVersion
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i     const 
   {$ifdef WIN32}        //Add here further Conditions to check if XP 
      ThisExeIs32bit=true;       
ODBCKey='Software\ODBC\ODBCINST.INI\ODBC Drivers';       
KEY_ODBC=KEY_WOW64_32KEY;      {$else}        ThisExeIs32bit=false;  
    ODBCKey='Software\ODBC\ODBCINST.INI\ODBC Drivers';       
KEY_ODBC=KEY_WOW64_64KEY;      {$endif} 
btw: {$IFDEF Win32} checks for the Compiler-Target, not the OS-
Bitness!
You can develop on 64-Bit, but your target is 32-Bit.
On a 64-Bit Windows, the WOW6432Node is the Registry-Location for
32-Bit stuff
Yes, you're right, if the OS is 32Bit, there is no WOW6432Node.
That's why i said, to check for OS-Version/Bitness

----------------------------------------------------------------------
rvk · 2023-07-20 09:21

Quote from: Zvoni on July 20, 2023, 10:54:57 amThis will work on both,
e.g. Windows 7 32Bit and Windows 7 64Bit
resp. on Win64Bit, but your compile Target is 32-Bit
The goal is: If you have 32-Bit App, use the 32-Bit driver
(independent if the OS is 32 or 64 Bit), if App is 64 Bit (and the OS
must be 64Bit), use the 64Bit driver
As I understand it, you SHOULDN'T use WOW6432Node directly.

Note:
QuoteThe Wow6432Node and WowAA32Node keys are reserved. For
compatibility, applications should not use these keys
directly.https://learn.microsoft.com/en-
us/windows/win32/winprog64/accessing-an-alternate-registry-view

If you want the value inside the WOW6432Node you pass KEY_WOW64_32KEY
so the value from that key is taken.
Otherwise you pass KEY_WOW64_64KEY (which always takes the base key).

KEY_WOW64_32KEY accesses the Wow6432Node automatically so if you go
looking there in Wow6432Node with that flag, you won't find it.

----------------------------------------------------------------------
rvk · 2023-07-20 09:33

Can ODBC 64 bit be accessed by a 32 bit program?

In that case you would possibly need two checks on a 32 bit program on
64 bit system.

{OS 64 bit, 32 bit app} check 32 bit ODBC and check 64 bit ODBC
{OS 64 bit, 64 bit app} check 64 bit ODBC and check 32 bit ODBC
(although this last one would be silly)
{OS 32 bit, 32 bit app} check 32 bit ODBC

----------------------------------------------------------------------
Zvoni · 2023-07-20 09:46

Quote from: rvk on July 20, 2023, 11:33:40 amCan ODBC 64 bit be
accessed by a 32 bit program?AFAIK, no. I remember the same question
from another forum i'm a member of, and the consens was: You can
access a 64-Bit DLL from a 32-Bit app with a "bridge" in between.
The Bridge itself would have to be 64-Bit, (since it needs to "launch"
the 64-Bit target-dll), and it would be something like an IPC
"translator"

QuoteIn that case you would possibly need two checks on a 32 bit
program on 64 bit system.

{OS 64 bit, 32 bit app} check 32 bit ODBC and check 64 bit ODBCThat's
exactly what my snippet above is doing. OS64, App32 --> return 32Bit-
Driver, which are in the WOW6432Node

Quote{OS 64 bit, 64 bit app} check 64 bit ODBC and check 32 bit ODBC
(although this last one would be silly)
{OS 32 bit, 32 bit app} check 32 bit ODBC
In both cases the Value in ODBCKey would be the same: the one without
the WOW6432Node

----------------------------------------------------------------------
rvk · 2023-07-20 09:57

Quote from: Zvoni on July 20, 2023, 11:46:37 amQuoteIn that case you
would possibly need two checks on a 32 bit program on 64 bit system.

{OS 64 bit, 32 bit app} check 32 bit ODBC and check 64 bit ODBCThat's
exactly what my snippet above is doing. OS64, App32 --> return 32Bit-
Driver, which are in the WOW6432Node
Yes, but when you use KEY_WOW64_32KEY (which you did for the 32 bit
program) you shouldn't look in WOW6432Node for the 32 bit version.
The Software\ODBC already points to the WOW6432Node version.

QuoteKEY_WOW64_32KEY = Access a 32-bit key from either a 32-bit or
64-bit application.
If you pass KEY_WOW64_64KEY in a 32 bit program THEN you can access
WOW6432Node.
So either change the key to KEY_WOW64_64KEY for 32 bit and access
WOW6432Node.
Of pass KEY_WOW64_32KEY and DON'T look in WOW6432Node (because
Software already points to that).

----------------------------------------------------------------------
konikula · 2023-07-20 10:55

Here is illustration of what I have in project:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i Procedure
GetODBCDrivers(var DriverList:TStringList);Var   Reg:TRegistry;begin 
Reg:=TRegistry.Create(KEY_READ Or KEY_QUERY_VALUE Or
KEY_ENUMERATE_SUB_KEYS Or KEY_ODBC);  DriverList:=TStringList.Create; 
DriverList.Sorted := True;  Try    Reg.RootKey := HKEY_LOCAL_MACHINE; 
  If Reg.OpenKeyReadOnly(ODBCKey) Then      Begin       
Reg.GetValueNames(DriverList);        //DriverList.Add('(None)');    
end;    Reg.CloseKey;  finally    Reg.Free;  end;end;    function
IsODBCDriver(driver:String):boolean;Var  i:Integer; drivers:
TStringList;begin  GetODBCDrivers(drivers);  for i:=0 to
drivers.Count-1 do begin    if LowerCase(driver)=LowerCase(drivers[i])
then begin      result:=True; exit;    end;  end; 
result:=False;end; Function RegistryGetAccessDriver():String;  begin 
 if IsODBCDriver('Microsoft Access Driver (*.mdb, *.accdb)') then
begin      result:='Microsoft Access Driver (*.mdb, *.accdb)';    end 
  else if IsODBCDriver('Microsoft Access Driver (*.mdb)') then       
 result:='Microsoft Access Driver (*.mdb)';  end;
And here is further usage of those functions:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TMyThread2.Execute;var cmd:TSQLQuery; i: Integer; attr: DWord; sql,
md5code, filePath:String;    lastProgress: Integer;
trans:TSQLTransaction; c, m, a:FileTime;    newPath:String;
fileHandle:THandle; dirPath: String; equalSource: String;
excludedFiles:TStringList;begin   excludedFiles:=TStringList.Create; 
 connAccess:=TODBCConnection.Create(pForm); 
 connAccess.Driver:=RegistryGetAccessDriver(); 
 connAccess.Params.Add('DBQ='+dbpath); 
 connAccess.Params.Add('DSN=test');   connAccess.Params.Add('Locale
Identifier=1031');   connAccess.Params.Add('ExtendedAnsiSQL=1'); 
 connAccess.Params.Add('CHARSET=ansi');    connAccess.Connected :=
True;   connAccess.KeepConnection := True;    trans :=
TSQLTransaction.Create(pForm);   trans.DataBase := connAccess;  
For me only problem was to distinguish between two Drivers, one having
*.accdb and one not having it. And if all windows (I recently checked
even windows 98) use same registry path to store Drivers, I may
suffice without ifdef, and without further recognising version of OS.
Am I right?

----------------------------------------------------------------------
Zvoni · 2023-07-20 11:00

Quote from: rvk on July 20, 2023, 11:57:27 am
Yes, but when you use KEY_WOW64_32KEY (which you did for the 32 bit
program) you shouldn't look in WOW6432Node for the 32 bit version.
The Software\ODBC already points to the WOW6432Node version.
Hmm..... i think i see where the confusion comes from.
KEY_WOW64_32KEY defines the Access-Rights i request when accessing the
Registry.
If i'm lazy i could just request KEY_ACCESS_ALL (or whatever it's
called)

i still have to provide the path in the Registry when trying to open
the Key (Line 23 in Post 4)

Or i'm missing something completely obvious

----------------------------------------------------------------------
rvk · 2023-07-20 11:20

Quote from: Zvoni on July 20, 2023, 01:00:24 pmHmm..... i think i see
where the confusion comes from.
KEY_WOW64_32KEY defines the Access-Rights i request when accessing the
Registry.
If i'm lazy i could just request KEY_ACCESS_ALL (or whatever it's
called)No, KEY_WOW64_xxKEY and KEY_ACCESS_ALL are two different
things.

If you have a 32 bit application and you want total access to the
entire registry tree (including the 64 bit ones in SOFTWARE) then you
pass KEY_WOW64_64KEY. In that case you have the 32 bit registry in the
WOW6432Node. But you passed KEY_WOW64_32KEY for 32 bit app in your
example. Then you only have access to the 32 bit and you don't get a
WOW6432Node node (konikula confirmed that).

But... directly accessing WOW6432Node (via KEY_WOW64_64KEY) isn't
advised supported (see that article from Microsoft).
So you should pass KEY_WOW64_32KEY if your searching for 32 bit but
then you don't have the WOW6432Node because you are already in that.

BTW. In my program I do KEY_WOW64_64KEY and access both Software and
Software/WOW6432Node because then you can do it in one go. If you want
to check for 32 AND 64 bit then you would officially need to close and
reopen the registry (which with KEY_WOW64_64KEY isn't nessecary
because you have that 'forbidden' WOW6432Node).


Quote from: konikula on July 20, 2023, 12:55:50 pm I may suffice
without ifdef, and without further recognising version of OS. Am I
right?
Yes.

If you only target 32 bit ODBC because you have a 32 bit application,
you don't even need to do anything special. Your 32 bit application
will only search for 32 bit ODBC in Software (which is
Software/WOW6432Node on 64 bit OS and Software on a 32 bit OS). You
only need the special KEY_WOW if you want to detect another bitness of
the ODBC (which you don't need to).

----------------------------------------------------------------------
Zvoni · 2023-07-20 13:15

Quote from: rvk on July 20, 2023, 01:20:52 pmQuote from: Zvoni on July
20, 2023, 01:00:24 pmHmm..... i think i see where the confusion comes
from.
KEY_WOW64_32KEY defines the Access-Rights i request when accessing the
Registry.
If i'm lazy i could just request KEY_ACCESS_ALL (or whatever it's
called)No, KEY_WOW64_xxKEY and KEY_ACCESS_ALL are two different
things.

If you have a 32 bit application and you want total access to the
entire registry tree (including the 64 bit ones in SOFTWARE) then you
pass KEY_WOW64_64KEY. In that case you have the 32 bit registry in the
WOW6432Node. But you passed KEY_WOW64_32KEY for 32 bit app in your
example. Then you only have access to the 32 bit and you don't get a
WOW6432Node node (konikula confirmed that).

But... directly accessing WOW6432Node (via KEY_WOW64_64KEY) isn't
advised supported (see that article from Microsoft).
So you should pass KEY_WOW64_32KEY if your searching for 32 bit but
then you don't have the WOW6432Node because you are already in that.

BTW. In my program I do KEY_WOW64_64KEY and access both Software and
Software/WOW6432Node because then you can do it in one go. If you want
to check for 32 AND 64 bit then you would officially need to close and
reopen the registry (which with KEY_WOW64_64KEY isn't nessecary
because you have that 'forbidden' WOW6432Node).
Ah...OK, finally got you.
Yeah, but in that case couldn't/shouldn't the Variable "ODBCKey" be
set outside the conditional, since it stays the "same"?
in the Conditional you'd just need to choose which KEY_WOW64_XXKEY
value you have to use

----------------------------------------------------------------------
rvk · 2023-07-20 13:28

Quote from: Zvoni on July 20, 2023, 03:15:57 pmAh...OK, finally got
you.
Yeah, but in that case couldn't/shouldn't the Variable "ODBCKey" be
set outside the conditional, since it stays the "same"?
in the Conditional you'd just need to choose which KEY_WOW64_XXKEY
value you have to use
It can. Otherwise you have two same lines.

But for 32 bit application you wouldn't even need to pass
KEY_WOW64_32KEY because that's default for a 32 bit application.

If you have a 64 bit application and you want to see it 32 bit ODBC is
installed, then you would need KEY_WOW64_32KEY so you don't need to
use the WOW6432Node.

So:
32 bit application is KEY_WOW64_32KEY default and only sees the
WOW6432Node (but without the actual WOW6432Node).
64 bit application is KEY_WOW64_64KEY default and sees it's own
Software AND the WOW6432Node (but it shouldn't access WOW6432Node
according to MS).

You only need KEY_WOW64_32KEY on 64 bit app to access to 32 bit
registry WOW6432Node via \Software.
You only need KEY_WOW64_64KEY on 32 bit app to access to 64 bit
registry via \Software.

You don't need any key to access 32 bit app -> 32 bit registry or 64
bit app -> 64 bit registry.

Regsitry-fun 
QuoteBy default, a 32-bit application running on WOW64 accesses the
32-bit registry view and a 64-bit application accesses the 64-bit
registry view. The following flags enable 32-bit applications to
access redirected keys in the 64-bit registry view and 64-bit
applications to access redirected keys in the 32-bit registry view.
These flags have no effect on shared registry keys. For more
information, see Registry Keys Affected by
WOW64.https://learn.microsoft.com/en-
us/windows/win32/winprog64/accessing-an-alternate-registry-view