1. Louis Arena
  2. PowerBuilder
  3. Monday, 27 June 2022 20:09 PM UTC

I am upgrading my application from PB2019 to PB2021.  I would like to compile under 64 bit.  When I compile I cannot connect to the database.  I am using the MSOLEDBSQL SQL Server drivers.  I am using the following runtime path when compiling;

C:\Program Files (x86)\Appeon\Common\PowerBuilder\Runtime 21.0.0.1506\x64

Why does the application connect when I run in the development environment but not when I compile under 64 bit?  It works fine when I compile under 32 bit. using C:\Program Files (x86)\Appeon\Common\PowerBuilder\Runtime 21.0.0.1506.  

Any ideas?

Thanks

mike S Accepted Answer Pending Moderation
  1. Monday, 27 June 2022 20:36 PM UTC
  2. PowerBuilder
  3. # 1

did you install the 64 bit version of the MSOLEDBSQL database driver?

 

what is the error message you get?

Comment
There are no comments made yet.
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Monday, 27 June 2022 20:36 PM UTC
  2. PowerBuilder
  3. # 2

Hi Louis;

   You are probably missing the 64 bit SS DB Client drivers from Microsoft. The PB SS driver in the "X64" folder is only an "interface" to the real SS DB Client drivers. HTH

Regards ... Chris

Comment
There are no comments made yet.
Louis Arena Accepted Answer Pending Moderation
  1. Monday, 27 June 2022 21:29 PM UTC
  2. PowerBuilder
  3. # 3

I have installed the 64 bit drivers, I used the download from this page.

https://docs.microsoft.com/en-us/sql/connect/oledb/download-oledb-driver-for-sql-server?view=sql-server-ver16

still the same thing.  I downloaded the 64 bit driver, and for good measure recompiled and tried again.  Is there anything else I might be missing?

 

 

Comment
There are no comments made yet.
Louis Arena Accepted Answer Pending Moderation
  1. Monday, 27 June 2022 22:07 PM UTC
  2. PowerBuilder
  3. # 4

The error I get when running the EXE is Connect to Database: Unable to Connect to Database either the ID or Password is incorrect.  But this works when I run the program from within PB.  This is also a generic issue if it cannot find a server.

Comment
  1. mike S
  2. Monday, 27 June 2022 22:17 PM UTC
change your code to provide the actual error so you know what the problem is
  1. Helpful 3
There are no comments made yet.
John Fauss Accepted Answer Pending Moderation
  1. Tuesday, 28 June 2022 01:58 AM UTC
  2. PowerBuilder
  3. # 5

Are you using the 64-bit PB runtime DLL's when running the 64-bit compiled app? These reside in a different directory than the runtime DLL's used by a 32-bit compiled app and the PB IDE (which is also 32-bit).

Comment
There are no comments made yet.
Louis Arena Accepted Answer Pending Moderation
  1. Tuesday, 28 June 2022 14:03 PM UTC
  2. PowerBuilder
  3. # 6

Mike, this is really funny.  I started this program in 2001, and realized I wrote this error message.  Once I investigated the REAL error I was getting I discovered I was getting SQLSTATE = 08001.  This lead me to look into the cliconfig application.

The cliconfig application I was using was for 32 bit.  I did not have the cliconfig 64 bit version TCP enabled.  I now learned that the 32 bit version of cliconfig is in the windows\syswow64 and the 64 bit version is in windows\system32!  Makes perfect sense laughing.

One weird thing is the network libraries in the 64 bit version for TCP/IP is c:\window\system32\dbnetlib.dll. Again this is confusing because it obviously is a 64 bit version residing in a 32 bit folder.

Thanks for all the help, at times you just need someone to help point you in some direction.

Comment
  1. mike S
  2. Tuesday, 28 June 2022 14:32 PM UTC
glad you got it figured out!
  1. Helpful
  1. Armeen Mazda @Appeon
  2. Tuesday, 28 June 2022 14:46 PM UTC
Thanks for sharing the solution!
  1. Helpful
  1. John Fauss
  2. Tuesday, 28 June 2022 15:24 PM UTC
Happy to learn you got it working, Louis! I agree the names of the 32/64-bit Windows system folders don't make sense, initially... but there is an explanation why they are named non-intuitively. "WOW64" stands for "Windows (32), On Windows 64" - This folder is where the emulator software than executes 32-bit applications in Windows 64 finds the 32-bit Windows system files. The "System32" folder is where the O/S looks for its "native" O/S resources, regardless of the bitness of the O/S. Here's a link that explains this in greater detail: https://www.samlogic.net/articles/32-64-bit-windows-folder-x86-syswow64.htm.
  1. Helpful
There are no comments made yet.
Louis Arena Accepted Answer Pending Moderation
  1. Tuesday, 5 July 2022 21:01 PM UTC
  2. PowerBuilder
  3. # 7

Hey everyone, as stated I have this working on my local development PC.  I am stuck rolling this out to the clients.  I am definitely compiling using the 64 bit folder in PB.  I have also instaleld the 64 bit version of MSOLEDBSQL drivers.  After I install this driver I am still getting the error that this is missing.  I have a clean laptop that has all the most current MS Windows updates for version 10 and this is not working.

 

Thanks

Comment
  1. John Fauss
  2. Tuesday, 5 July 2022 21:49 PM UTC
You may need to run the Microsoft-supplied MSI that installs the 64-bit version of MSOLEDBSQL client/drivers as Administrator. Don't know for sure it this is the cause of your issue.
  1. Helpful
There are no comments made yet.
Louis Arena Accepted Answer Pending Moderation
  1. Wednesday, 6 July 2022 19:33 PM UTC
  2. PowerBuilder
  3. # 8

Ok, so here is an update.  The latest version from MS does NOT work.  this version uses msoledbsql19.dll, which is a name change for the library.  We got this to work with version 18.2.3.0 of msoledbsql.msi.  When we run this install on a computer it works.

I would like to not have to install this on all computers.  So by copying the following 2 files into the folder that contains the application you can connect to the SQL server WITHOUT having to do individual install.  The 2 files are, msoledbsql.dll and msolesdbsql.rll.  msoledbsql.dll can be found in Windows\System32 and msolesdbsql.rll is located in Windows\System32\1033.

 

I hope this helps others.

Comment
  1. Roland Smith
  2. Thursday, 7 July 2022 15:33 PM UTC
Version 19 is supported by PB 2022 which is in beta.
  1. Helpful
There are no comments made yet.
Louis Arena Accepted Answer Pending Moderation
  1. Thursday, 7 July 2022 14:19 PM UTC
  2. PowerBuilder
  3. # 9

I do not believe the MSOLEDBSQL driver needs/requires a registry entry, although they do make them during the install.  What I have found so far is that the file msoledbsql.dll MUST be in the windows\system32 folder, even if it EXISTS in the folder where the application resides.  This does not make sense to me as by default libraries are searched in the directy the application resided and then in the folders defined in the PATH environment.

 

Anyone have an idea why the DLL is not recognized in the application folder?  All of PB's DLLs are recognized in the application folder.

 

Thanks

Comment
There are no comments made yet.
Roland Smith Accepted Answer Pending Moderation
  1. Thursday, 7 July 2022 15:38 PM UTC
  2. PowerBuilder
  3. # 10

MSOLEDBSQL version 19 is supported by PB 2022 (in beta) even though the help file doesn't mention it as a new feature. 

Comment
  1. Armeen Mazda @Appeon
  2. Thursday, 7 July 2022 15:43 PM UTC
  1. Helpful
There are no comments made yet.
John Fauss Accepted Answer Pending Moderation
  1. Thursday, 7 July 2022 15:52 PM UTC
  2. PowerBuilder
  3. # 11

One can only guess as to why Windows only uses this driver when it resides in the Windows System folder. Maybe because Microsoft considers it to be an "add-on" to the Windows O/S? What a PITA.

A clarifying note to others who may at some point refer to this thread: The following documentation on installing the OLE DB Driver for SQL Server:

   https://docs.microsoft.com/en-us/sql/connect/oledb/applications/installing-oledb-driver-for-sql-server?view=sql-server-ver16

clearly notes in the 2nd paragraph the different names of the system folder used for 64-bit apps and 32-bit apps:

The files for OLE DB Driver for SQL Server (msoledbsql19.dll/msoledbsql.dll, msoledbsqlr19.dll/msoledbsqlr.rll) are installed in %SYSTEMROOT%\system32\ . Additionally, the x64 msoledbsql.msi installs 32-bit binaries in %SYSTEMROOT%\SysWOW64\.

Of course, the current online documentation quoted above refers to Version 19 of MSOLEDBSQL, which will not be supported in PB until PB 2022 is released later this year. "%SYSTEMROOT%" refers to what on typical Windows installations is C:\Windows.

 A user requires Administrator privileges to manually copy files into the system folder(s). Presumably, the MSIEXEC utility can do so without Admin rights, but I've not verified this. The same URL listed above also describes how the MSIEXEC utility can be run silently, should you need to or wish to.

Comment
There are no comments made yet.
Louis Arena Accepted Answer Pending Moderation
  1. Thursday, 7 July 2022 18:02 PM UTC
  2. PowerBuilder
  3. # 12

Ok, so this is how I solved this problem.  When the user hits the login button, at the time I make the connect statement I check for the error of the missing drivers.

 

connect using SQLCA;
If sqlca.sqlcode < 0 Then
// test to see if the windows 64 msoledbsql.dll is missing, this creates the error sqldbcode = 999
if sqlca.sqldbcode = 999 then
   i = run("move_sql_dll.bat", Minimized!)
   Messagebox("Software Upgrade","Your computer is missing the Microsoft Software to connect to SQL Server. RMS will shut down, please try again AFTER     the MS Installation window disappears.",Information!,Ok!)
   disconnect;
   return
end if

I copied the installation MSI program in a subfolder called sql_drivers.  I call a BAT file located in the same directory as the application EXE.  The bat file has these commands

cd sql_drivers
msiexec /i "msoledbsql_V18.2.3.0.msi" /passive IACCEPTMSOLEDBSQLLICENSETERMS=YES
cd ..

The run function in PB returns a 1 for success in calling the BAT file, but that is it.  There is no way for me to wait for the install to finish and try connecting again, which is why I put up the Popup window.  After the SQL Drivers install the user can hit the login button again and get connected.

Not the cleanest method and I expect my users to call once they get the popup because they will not read it, but this is better than visiting all computers in the company and dealing with their login and such.

Hope this helps someone else.

 

NOTE: I changed the name of the msoledbsql program so I know what version we are loading.

 

Comment
  1. Roland Smith
  2. Thursday, 7 July 2022 20:29 PM UTC
To run the BAT file and wait for it to finish, use this example:

https://www.topwizprogramming.com/freecode_runandwait.html
  1. Helpful
  1. Roland Smith
  2. Thursday, 7 July 2022 20:32 PM UTC
If you use 'runas' for the Shell Verb, it will run the bat file as admin.
  1. Helpful
There are no comments made yet.
Louis Arena Accepted Answer Pending Moderation
  1. Tuesday, 12 July 2022 20:40 PM UTC
  2. PowerBuilder
  3. # 13

Here is an update to this, it worked on some test machines but now is not working on others.  Our network is simple, no Domain Controllers and the users log in with Administrator rights to their computers.  We can reformat the PCs if something is not working, easier this way for us. Anyway I now test for the drivers, if they are no installed I run the following.

ls_sql64 = 'msiexec /a "sql_drivers\msoledbsql.msi" /passive IACCEPTMSOLEDBSQLLICENSETERMS=YES ALLUSERS=1'
i = run(ls_sql64, Minimized!)

This runs the msiexec program as administrator and automatically accepts the terms.  

You see that it program starts and it is gathering data, then it just stops.  You think it is installed but it is not.  

I spent the day researching this and no one has reported this issue.  I even created log files and they report a success of 0 which means no errors according to the documentation.

 

 

Comment
  1. Roland Smith
  2. Wednesday, 13 July 2022 12:55 PM UTC
The Run function doesn't wait for the external program to finish. Try using my RunAndWait object:

https://www.topwizprogramming.com/freecode_runandwait.html
  1. Helpful
  1. Louis Arena
  2. Wednesday, 13 July 2022 13:20 PM UTC
The run and wait has nothing to do with this. When you execute that command from a command line it does the same thing. It terminates without actually loading the drivers.
  1. Helpful
There are no comments made yet.
Louis Arena Accepted Answer Pending Moderation
  1. Thursday, 14 July 2022 17:23 PM UTC
  2. PowerBuilder
  3. # 14

Ok, I wanted to give everyone the final outcome of this.  My last post had an issue regarding using msiexex but that was an issue on the computer, further testing on other machines did not have this problem.

When a user clicks the login button, after supplying ID and password, I check the  sqlca.sqldbcode for results.

 

If  sqlca.sqldbcode = 999 then the 64 bit drivers are missing.  I display an pop up window explaining the issue and telling the user to click Yes on both Regedit and MS OLE SQL Drivers when asked to modify the computer.  If we are missing the 64 bit drivers I run a BAT file that does the following.

rem regedit will only do an import from a REG file on the local drive

xcopy e:\rms\update_rms_cliconfig.reg c:\temp /y /j
c:
cd \temp
regedit.exe /s c:\temp\update_rms_cliconfig.reg

rem this will navigate to where my application is and run the smoledbsql program which is located in a sub folder sql_drivers

e:
cd \rms\rms-64
msiexec /i "sql_drivers\msoledbsql.msi" /passive IACCEPTMSOLEDBSQLLICENSETERMS=YES ALLUSERS=1

 

if sqlc.sqldbcode = 53 that means we have the 64 bit drivers but TCP and the alias for my SQL server was not configured in cliconfg.  In this case I run different BAT file that only has this code.

rem regedit will only do an import from a REG file on the local drive

xcopy e:\rms\update_rms_cliconfig.reg c:\temp /y /j
c:
cd \temp
regedit.exe /s c:\temp\update_rms_cliconfig.reg

 

I hope this will help others who are having this issue.

Comment
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.