Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (3)

Resolved MS OLE DB driver - deploying connection strings

  1. How-to
0
Votes
Undo
  1. Aron Cox
    Aron Cox
  2. PowerBuilder
  3. Thursday, 8 April 2021 13:28 PM UTC

We've been using the old SQL Server ODBC driver (SQLSRV32) for a long, long time now, and now it's finally time to update to a new driver. We originally looked at replacing it with the ODBC Driver 17 and that seems to work well and supports all the modern features of SQL Server.

However, at the last Elevate conference it seems Appeon think the Microsoft OLE DB Driver (MSOLEDBSQL) is the driver of choice, and no-one at Appeon seem to have even heard of the ODBC Driver 17. So I'm about to have a look at the OLE DB driver and see how well it works for us.

My first issue is I am used to using ODBCAD32.EXE to setup ODBC data sources on PCs, but it doesn't seem like there's an equivalent thing for the OLE DB driver. So what do people do to get the connection settings out to users?

I could of course just add my own registry stuff, or create an INI or config file, but I wonder if there is some standard way of doing this that I'm missing???

Any thoughts appreciated!

  1. OLEDB
Issue with PBDOM_Builder creation after upgrade to...
PFC classes for pb 2019 R3
Who is viewing this page
Torgeir Lund
Responses (3)
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Thursday, 8 April 2021 18:18 PM UTC
  2. PowerBuilder
  3. # 1

Hi Aron;

   No external utility is required for the new SS driver in PB 2019 R3 - not like JDBC, ODBC, etc. Here is an example of my settings for the new PB based  SS driver that I use ...

// Profile SS2019_MSOLEDB_Chris
SQLCA.DBMS = "MSOLEDBSQL SQL Server"     // or just set it to "MSO"
SQLCA.LogPass = <*****>
SQLCA.ServerName = "localhost"
SQLCA.LogId = "Chris"
SQLCA.AutoCommit = False
SQLCA.DBParm = "Database='Chris',AppName='PB2019R3',Host='STD-CHRIS-LT',TrimSpaces=1"

FWIW: I personally keep all the DB connection settings in an INI file but, I have them encrypted

HTH

Regards ... Chris

 

Comment
Aron Cox
  1. Aron Cox
  2. Thursday, 8 April 2021 21:06 PM UTC
Thanks Chris. Encrypting the INI file sounds like a good idea
  1. Helpful
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Thursday, 8 April 2021 21:14 PM UTC
Always worked for me in getting IT approval for production use. The only thing that EDP Auditors were sticky about is that the password must be a one way encryption. So I just read the PW from the INI file encrypted, apply the one way encryption to what the user entered and if the encrypted PW in memory matches the INI one .. the App user is "good to go". Food for thought. ;-)
  1. Helpful
There are no comments made yet.
mike S
mike S Accepted Answer Pending Moderation
  1. Thursday, 8 April 2021 13:59 PM UTC
  2. PowerBuilder
  3. # 2

I've always used ini files to store connection info regardless of type.  I support odbc, oledb, native, and now MSOLEDB for sql server.  

I store the ini file in programdata directory (C:\ProgramData\<myapp>\<myapp>.ini) . 

I prefer an ini file because a simple copy of an ini file between machines as well as editing it is a lot easier than doing similar with registry.  If the ini file does NOT exist when my app is run, then a screen pops up asking for the connection info (driver to use, server, database, misc parms, binds).  That screen can also be used to modify the connection info if need be.

 

Comment
Aron Cox
  1. Aron Cox
  2. Thursday, 8 April 2021 14:21 PM UTC
Thanks Mike, I guess my expectation of somewhere where OLE DB connection string data is supposed to go, as there is with ODBC connection strings, was wrong. At least it wasn't a failure in my Google search skills! I guess INI / Config files are the way to go
  1. Helpful
There are no comments made yet.
Armeen Mazda @Appeon
Armeen Mazda @Appeon Accepted Answer Pending Moderation
  1. Thursday, 8 April 2021 13:41 PM UTC
  2. PowerBuilder
  3. # 3

This is all covered in the documentation.  Please at least watch this Webcast recording: https://youtu.be/tSaUqXHQ85Y

Anyway, to move forward...

1. Make sure you have upgraded to PB 2019 R3.  Older versions of PB do not support the new driver.

2. Make sure SQL Server is upgraded to 2012 or newer.  Older versions of SQL Server do not support the new driver.

3. Install the updated drivers from Microsoft: https://docs.microsoft.com/en-us/sql/connect/oledb/download-oledb-driver-for-sql-server?view=sql-server-ver15

4. Update the connection string in PowerScript to make use of the new driver, which you can get from the DB Painter.

5. Make sure to deploy the new PB runtime file PBMSOLEDBSQL.dll with your app.  We developed this specifically for the new driver.

Comment
Load more comments
Armeen Mazda @Appeon
  1. Armeen Mazda @Appeon
  2. Thursday, 8 April 2021 13:56 PM UTC
I don't believe there is DSN/ODBC settings to setup in ODBC administrator since this is OLE DB. If you watch the Webcast recording I provided, you will see the connection info is in your app. So all you should need to do is recompile your app with the new connection strings and distribute the new runtime DLL with the updated app to your users. By the way, PowerClient is a great new feature to update this process of installing and updating app to users: https://www.appeon.com/products/power-client
  1. Helpful
Aron Cox
  1. Aron Cox
  2. Thursday, 8 April 2021 14:15 PM UTC
Thanks Armeen, yeah we distribute to many customers all with varied connection settings to multiple databasses, so we tend to want to keep them external to our app. It seems like there is no standard place, I remember there used to be file ODBC settings as well as an ODBC area in the registry where they could all be found, I guess that kind of thing just doesn't exist for non-ODBC drivers.
  1. Helpful
Armeen Mazda @Appeon
  1. Armeen Mazda @Appeon
  2. Thursday, 8 April 2021 14:21 PM UTC
I don't recommend storing the value in the registry. It requires admin rights to touch the registry. The most common way to do it if you want external to your app is in an .INI file as Mike has suggested.
  1. Helpful
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.