The "Custom Driver" is used to add the ADO.NET data provider which has not been predefined or preprocessed by PowerBuilder.
So far, PowerBuilder has enhanced its ADO.NET database interface for the following data providers: Oracle, PostgreSQL, SQL Server, and ODBC. For other data providers (such as MySQL, Teradata etc.), you can configure them as a custom driver in PowerBuilder. Custom driver means the driver has not been predefined or preprocessed by PowerBuilder, and PowerBuilder will try to load and parse the driver according to ADO's standard interfaces and common data types. Therefore, PowerBuilder may not fully work with the custom driver if: 1) The driver is not implemented according to established standards; 2) The driver and its required dependencies are not fully provided. For example, if the data set returned by the driver differs from the expected data set standards, parsing may fail, resulting in incorrect data being returned.
ADO.NET data providers are typically available for download from NuGet source servers, though not all versions are free. You can download or purchase the driver according to actual needs and comply with the third-party provider’s licensing agreements.
When choosing the driver package, please pay attention to the following:
-
Ensure the driver is an ADO.NET Data Provider.
-
Pay attention to the supported platform (32-bit or 64-bit) and operating system of the driver.
Note that PowerBuilder IDE is 32-bit, so it requires 32-bit driver; and the application executable can be 32-bit or 64-bit depending on the build option.
-
Ensure the target framework supports .NET 8.0 (.NET 6.0, .NET Standard 2.1, and .NET Standard 2.0 are also supported).
If a driver package contains libraries for multiple target frameworks, it is recommended that you manually extract the NuGet package and select the version of the library you wish to use. Otherwise, the program will automatically attempt to match the most suitable version, prioritizing .NET 8.0, followed by .NET 6.0, .NET Standard 2.1, and .NET Standard 2.0.
-
Some driver packages may not contain readily available driver library files. And the library file become available only when compiled or referenced via a .NET application or other methods. Loading the driver via such a NuGet package is NOT supported.
PowerBuilder supports to load a custom driver either offline or online:
-
Offline: You can download the ADO database driver library file to the local machine and PowerBuilder loads the driver from the local directory.
-
Online: You can provide the download URL of the NuGet package, and PowerBuilder automatically downloads and extracts the NuGet package to the local machine, and then loads the driver.
You can obtain the NuGet package (and its dependencies) from the NuGet web site, database official sites, or other platforms, and then specify the local directory of the files.
In the Open dialog box, you can change the file type filter to .nupkg (①) and then select the NuGet package file on the local machine (②). PowerBuilder will extract the contents of the NuGet package in the directory where it is stored (③), and then search for the driver library file within the extracted folder. The full path of the driver library will then be added to the Driver specification section (④) and the database connection string.
When connecting to the database, the program loads the driver from this path. It is crucial that you provide the full path to the custom driver file, as the program does not support relative paths, current directory paths, or environment variables.
You can also directly specify the download URL of the NuGet package. The program will automatically download the package and load the appropriate driver library file.
After you input the download URL (①), you may want to change the default package name to a meaningful name (②), before clicking OK to initiate the download. Once the download is complete, PowerBuilder will extract the contents of the NuGet package in the directory where it is stored, and then search for the driver library file within the extracted folder. The full path of the driver library will then be added to the database connection parameters. Note: If a download is in progress and canceled, the downloaded NuGet package might be incomplete.
Any third-party dependency libraries must also be downloaded manually and stored in the same directory as the driver library file. Dependencies can be obtained in the following ways:
-
Download the dependencies listed on the Dependencies tab on the NuGet web site.
-
Obtain the dependencies from the driver provider.
-
Add dependencies as prompted during runtime.
Some driver packages may include nested dependency libraries (i.e., the dependencies themselves may rely on other libraries). In such cases, you may need to manually adjust the locations of the driver files and dependencies based on the output information during the connection process, ensuring that the program can successfully load the required files.
You can use Visual Studio to publish an empty application that references the driver package to gather all required dependency libraries. If unsure about the platform (32-bit or 64-bit), you can specify Target Runtime as x86 or x64 during the publish process, to generate the corresponding platform-specific libraries. If there is no matching platform, Visual Studio will generate a warning or error.
If the dependencies are available as NuGet packages, you will need to obtain the DLL files from the NuGet package first and then place the DLL files in the same location as the driver DLL file.
A NuGet package is essentially a .nupkg file, which is a ZIP archive. You can extract it using any standard unzip tool (e.g., WinRAR, 7-Zip).
-
Manually download the dependencies to the local machine.
The following figure shows the dependencies required by MySQL on the NuGet web site.
-
Change the file extension from .nupkg to .zip , then use your unzip tool to extract it.
After extraction, you will see a folder containing the package contents.
-
Open the extracted folder. Look for the lib folder. Inside it, you will see subfolders corresponding to different .NET versions, such as lib/net8.0, lib/netstandard2.1, etc. In these subfolders, you will find the actual DLL files.
The following figure shows the DLL in the lib/net8.0 folder extracted from the MySQL dependencies.
-
Copy the .dll file(s) to the same directory as the driver library file.
Besides the driver file, you will also need to specify the data source, user name, password, etc. which are required for the database connection.
You can obtain the connection string via the following ways:
-
Request it from the driver provider.
-
Refer to the driver’s official documentation.
-
Look up the ADO connection string.
Notes:
1) In the Driver specification section (or in the DBParm DriverFile parameter), make sure to provide the full path to the driver DLL file. PowerBuilder does not support relative paths, current directory paths, or environment variables for the custom driver. (When distributing the application, you can distribute the driver file in the same directory as the application and then concatenate the full path of the driver using the GetCurrentDirectory method. A code example is provided later in the Distributing the custom driver section.)
2) If you configure the User name and Password fields, and the connection fails, you can try adding the user name and password as parameters in the Driver-specific parameters section, and then connect again.
Currently, Database Painter only supports displaying basic tables, views, and stored procedures, along with basic data operations. For some unknown database types, the interface may display incorrectly or fail to operate as expected, as explained earlier.
To ensure your application connects properly with the database using the ADO.NET custom driver, you must package and distribute the .NET Desktop Runtime, the PowerBuilder runtime files, and the custom driver files (and dependencies) along with your application.
-
.NET Desktop Runtime 8.0
.NET Desktop Runtime 8.0 must be installed to the client to support the ADO.NET database connection.
You will have to manually package and distribute .NET Desktop Runtime. (Make sure the platform (32-bit or 64-bit) of .NET Desktop Runtime matches that of the application executable file.)
-
PowerBuilder runtime files
You can package and distribute the PowerBuilder runtime files, either in an automatic or manual way.
To package the files manually, go to the PowerBuilder Runtime location and get the files according to the table in this section.
To package the files automatically, select the "Custom Driver" in the Runtime Packager so that the packager can automatically package the files for you.
-
Custom driver files (and dependencies)
The custom driver library files and dependencies should be placed in the location specified in the connection string. (Make sure the platform (32-bit or 64-bit) of the driver libraries matches that of the application executable file.)
When distributing the driver files with the application, you can put and distribute the driver files in the same directory as the application and then concatenate the full path of the driver using the GetCurrentDirectory method.
Below is a code example for how to concatenate the full path of the MySQL driver with the current directory of the application and then connect with the database.
String ls_DriverPath String ls_CurrentDirectory //Get the current directory of the application and cancatenate the driver path ls_CurrentDirectory = GetCurrentDirectory ( ) If Right(ls_CurrentDirectory,1) <> '\' And Right(ls_CurrentDirectory,1) <> '/' Then ls_DriverPath = ls_currentDirectory + '\Driver_net8.0\MySqlConnector.dll' Else ls_DriverPath = ls_currentDirectory + 'Driver_net8.0\MySqlConnector.dll' End If // Profile ADO_Mysql SQLCA.DBMS = "ADO.Net" SQLCA.LogPass = "test123!@#" SQLCA.LogId = "root" SQLCA.AutoCommit = False SQLCA.DBParm = "Provider='Custom Driver',DriverFile='"+ls_DriverPath+"',PROVIDERSTRING='server=172.12.3.45;database=mysql'" Connect; If SQLCA.SQLCode <> 0 Then Messagebox("Connect Database Failed","SQLCode:"+String(SQLCA.SQLCode)+"~r~nSQLErrText:"+SQLCA.SQLERRText) Return End If Open(w_main)In the above example, the "Driver_net8.0" folder should be distributed in the same directory as the application, as shown below.
And the "Driver_net8.0" folder should contain the MySQL driver and dependencies, as shown below.
1. Database Connection Stability: Some drivers may have instability issues when connecting in Database Painter. These issues will be optimized in later versions.
2. MySQL Driver Connection Issue: When connecting via mysql.data.9.1.0.nupkg, failing to configure the SSL mode parameter may cause connection crashes on Windows Server 2022 or Windows 11.
3. Missing Dependency Library: In some MySQL connection scenarios, "missing googlecnXX dependency libraries" may appear. The solution is to copy the missing dependency libraries into the same directory as the driver library.
