mySQL ODBC Connections & PowerBuilder Crashes & DSN Less

MySQL Connections With PowerBuilder – Common Problems and Solutions

Crash in PowerBuilder when selecting data in TEXT columns

If you have tried to connect to mySQL using the ODBC connector with database that has long text columns than you have most likely dealt with the mysterious PB crashes that occur.

Example Offending Data:  4 byte UTF-8 characters like these:
HTML: ɛ
UTF-8 binary: 0xC9 0x9B (c99b)
UTF-8 hex: 11001001:10011011

SOLUTION: Change Character Set in ODBC, use utf8mb4 instead of utf8 (supported in MySql 5.5.3 or greater)

PROBLEM: You may select TEXT columns but get empty results back.

Selecting TEXT Columns in PowerBuilder Come Back Empty

This is another annoyance. You can work around it by using substring on your text columns like substring(my_textcol_name, 1, 10000) to get the text in select statement.

WORKAROUND: Do a substring(post_content, 1, 10000) on the TEXT column to display the data.

SOLUTION:  In your ODBC settings window, in the Metadata tab, select: Limit column size to signed 32-bit range. You will be able to select TEXT columns!

Here is my connect string for PB Classic 12.5 against mySQL 5.1.72 (remote)


// Profile mySQL_remoteEC2_wpmgr
SQLCA.DBMS = "ODBC"
SQLCA.AutoCommit = False
SQLCA.DBParm = "ConnectString='Driver={MySQL ODBC 5.2 ANSI Driver};Server=www.mydomain.com;Database=mydatabaseschemaname;User=root;Password=xxx;IGNORE_SPACE=1;FLAG_SAFE=1;"

DSN Less Connections With MySQL in PowerBuilder

Yes, you can finally connect to MySQL without a DSN, beginning with ODBC Connector Version 5.6.

Here is an example of the connect string:

ConnectionString = "DRIVER={MySQL ODBC 5.3 Driver};\
                   SERVER=localhost;\
                   DATABASE=test;\
                   USER=venu;\
                   PASSWORD=venu;\
                   OPTION=3;"

Refer to Section 5.2, “Connector/ODBC Connection Parameters” for the list of connection parameters that can be supplied.