• You are here:  
  • Home
  • Q&A
  • Q&A
  • PowerBuilder
  • Comparison of methods accessing SQL Server from PowerBuilder

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

Resolved Comparison of methods accessing SQL Server from PowerBuilder

  1. Advice
0
Votes
Undo
  1. Roland Mühlberger
    Roland Mühlberger
  2. PowerBuilder
  3. Tuesday, 16 March 2021 15:33 PM UTC

Dear PowerBuider Programmers,

this might emerge as a philosophical question, but I still hope to get some answers:

PowerBuilder allows many different ways to access Microsoft SQL Server databases from classic (client/server) PB windows applications:

  • ODBC - PBODB.dll + PBODB.INI: this is the one we use, mainly because the "SQL Server" ODBC driver is pre-installed, which allows for xcopy-deployment style applications accessing the database using DSN-less connections.
  • OLE DB - PBOLE.DLL + PBODB.INI: this seems to be based on OBDC because it also uses PBODB.INI

  • With PowerBuilder 2019 R3 there is even a OLE-DB provider specifically for SQL Server. I have no idea why this is necessary, given the fact that there is already a generic OLE DB driver.

  • SQL Server Native Client - PBSNC.DLL: the latest version of the Microsoft SQL Server Native Client is 11, and MS has deprecated it. So this does not seem to have any future.

My question is: Do you know about any advantages of using (the new) OLE DB (or even SNC) over ODBC?

If you use OLEDB or SNC: How did you decide for it (and against ODBC)?

Sidenote: Unfortunately, Microsoft does *not* ship the latest SQL Server ODBC Driver under the name "SQL Server" in a standard windows client installation, but it needs a separate install (currently Microsoft "ODBC Driver 17 for SQL Server"). So if you want to use up-to-date features of the driver, you need to install it. Within the ODBC Administrator, the driver is called "ODBC Driver 17 for SQL Server". The SQL_DBMS_Name setting of the driver is "Microsoft SQL Server", and this is also the section PowerBuilder looks up in PBODB.INI, which is also different from the pre-installed driver which uses "SQL Server".

Thanks for any answers.

Roland

  1. ODBC
  2. OLEDB
  3. SQL Server
  4. MSOLEDBSQL
RibbonBar XML using Picture attribute
Radio button control with Left Text not displaying...
Responses (3)
Roland Mühlberger
Roland Mühlberger Accepted Answer Pending Moderation
  1. Wednesday, 17 March 2021 07:20 AM UTC
  2. PowerBuilder
  3. # 1

Thanks John for your answer. As a matter a fact we support Oracle next to SQL Server in our applications, so we are somewhat limited in DMBS-specific features, but many do not rely on the driver but rather on syntax, and we can manage those differences within the software ok. But my question addresses SQL Server in particular.

So to rephrase my question: if SQL Server is the only DBMS you have to access, what advantage does the MSOLEDBSQL OLE DB access method have over the ODBC access via the current SQL Server ODBC driver?.

Are there any?

Thanks,

Roland

Comment
Armeen Mazda @Appeon
  1. Armeen Mazda @Appeon
  2. Wednesday, 17 March 2021 14:11 PM UTC
Aside from possible feature limitations, theoretically it should be slower because of the extra layer John mentioned. Please not I say theoretically because what you practically experience in real life doesn’t always match the theory. You would have to test and compare for your specific situation.
  1. Helpful
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Wednesday, 17 March 2021 16:57 PM UTC
Added to what others have said: If you'd want to use snapdevelop or powerserver in the future, you'd have none or less support with the odbc driver.
  1. Helpful
Armeen Mazda @Appeon
  1. Armeen Mazda @Appeon
  2. Wednesday, 17 March 2021 17:25 PM UTC
Good point!
  1. Helpful
There are no comments made yet.
John Fauss
John Fauss Accepted Answer Pending Moderation
  1. Tuesday, 16 March 2021 19:52 PM UTC
  2. PowerBuilder
  3. # 2

Hi, Roland -

As I understand it, ODBC is intended to provide a DBMS-independent interface for applications. To accomplish this, an extra "middle-ware" layer (ODBC32.dll) sits between the Appeon/PB ODBC driver and the database-specific driver.

Your application may not be able to utilize the full set of capabilities of the database since ODBC implements a common, standardized collection of functionality across all or most database vendors. This is advantageous if your app needs to work with multiple DBMS vendors, but it might be unduly restrictive if you are using a single vendor.

By using a database-specific driver, be it for SQL Server. Oracle, Sybase ASE, or others, you give your app the best chance of being able to utilize a larger, more complete set of DBMS features and functionality, now and in the future.

As for installation-related concerns, you're going to need to determine the necessary installation steps/process regardless of which DBMS driver you choose, so I view this consideration as largely immaterial.

For what it's worth, my two cents. HTH.

Best Wishes and Regards, John

 

Comment
There are no comments made yet.
Armeen Mazda @Appeon
Armeen Mazda @Appeon Accepted Answer Pending Moderation
  1. Tuesday, 16 March 2021 15:49 PM UTC
  2. PowerBuilder
  3. # 3

 

I just wanted to respond to your point about why there is a new OLEDB driver in PowerBuilder 2019 R3.  All other implementations are now obsolete and not maintained by Microsoft. 

Comment
Roland Mühlberger
  1. Roland Mühlberger
  2. Tuesday, 16 March 2021 16:07 PM UTC
So that boils my question down to "ODBC" versus (the new) "MSOLEDBSQL". Thanks for the update, Armeen.
  1. Helpful
Armeen Mazda @Appeon
  1. Armeen Mazda @Appeon
  2. Tuesday, 16 March 2021 16:18 PM UTC
You’re very welcome.
  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.