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

Resolved Is PB2019 Build 2082 compatible with MySQL 8.0

  1. Issue
0
Votes
Undo
  1. Gerry Whitmarsh
    Gerry Whitmarsh
  2. PowerBuilder
  3. Saturday, 13 July 2019 15:07 PM UTC

Hi,

Due to a MySQL bug regarding auto increment columns, I am investigating upgrading from MySQL 5.7 to 8.0 where the bug has been fixed. I have successfully upgraded the database but as I have table names and columns that are now reserved words I have to – according to the upgrade check report – enclose them in quotes (or back ticks) when referring to them. I can’t get this to work. I have tried enclosing the names in double quotes, single quotes and back ticks but still get the error (for table groups) Incorrect table name ‘ groups ‘. Also in the database painter, I cannot open these tables due to a syntax error.

Is there known issues with PB2019 Build 2082 and MySQL 8?

Thanks,

Gerry.

  1. MySQL
  2. PowerBuilder (Appeon)
Fill in some PDF properties with NativePDF method
How to conduct transaction when autocommit set to ...
Responses (5)
mike S
mike S Accepted Answer Pending Moderation
  1. Saturday, 13 July 2019 20:25 PM UTC
  2. PowerBuilder
  3. # 1

did you select the option to enclose table and column names in quotes in the database profile?

Comment
Gerry Whitmarsh
  1. Gerry Whitmarsh
  2. Sunday, 14 July 2019 08:02 AM UTC
Hi Mike,



Thanks for the reply, I have never used that option before. If I check this option and use the back tick as quote character (other characters don't work), I can then edit the table in the database painter, but still get errors with embedded SQL. If I don't enclose the table name in quotes, I get a syntax error:



Database C0038: SQLSTATE = 37000[MySQL][ODBC 8.0(w) Driver[MySQL-8.0.16]You have an error in your syntax.......



If I enclose the table name in quotes, I still get the Incorrect table name error:



Database C0038: SQLSTATE = S1000[MySQL][ODBC 8.0(w) Driver[MySQL-8.0.16]Incorrect table name ' groups '



Any ideas?



Thanks.
  1. Helpful
Brad Mettee
  1. Brad Mettee
  2. Sunday, 14 July 2019 13:01 PM UTC
If you use inline sql, you need to use the backtick to enclose table/column names, not double quotes. The backtick, on a USA keyboarard, is the unshifted Tilde key (next to the 1 key, and under the ESC key).

  1. Helpful
There are no comments made yet.
Gerry Whitmarsh
Gerry Whitmarsh Accepted Answer Pending Moderation
  1. Sunday, 14 July 2019 20:17 PM UTC
  2. PowerBuilder
  3. # 2

I have just discovered that it is not confined to MySQL 8.0 and PB2019. I get the same result with PB 10.5,  MySQL 5.7 and ODBC connector 5.3 if I use back ticks.

SELECT access_group
INTO    :i_access 
FROM `groups`
WHERE office = "XXX";

This gives the error:

Database C0038: SQLSTATE = S1000[MySQL][ODBC 5.3(w) Driver[MySQL-5.7.14-log]Incorrect table name ' groups ' in the IDE. Not using the ticks is accepted in the IDE, but gives a syntax error at run time.

I have never before used back ticks, but with MySQL 8.0 I need to, so I have just discovered this issue.

Any help would be appreciated.

Comment
mike S
  1. mike S
  2. Monday, 15 July 2019 00:33 AM UTC
it may be several hours of work, but you can change the table names and/or column names.



Write the database scripts, then search for the table names in your PB script and change those too. I think in the longer term it will be less work to change the table names now to not using the mysql reserved words.



I went through something similar with sql server once.



https://dev.mysql.com/doc/refman/8.0/en/rename-table.html



  1. Helpful
There are no comments made yet.
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Sunday, 14 July 2019 20:27 PM UTC
  2. PowerBuilder
  3. # 3

Hi Gerry;

   Currently, PB 2017/2019 do not support MySql.

Regards ... Chris

Comment
There are no comments made yet.
Armeen Mazda @Appeon
Armeen Mazda @Appeon Accepted Answer Pending Moderation
  1. Monday, 15 July 2019 00:00 AM UTC
  2. PowerBuilder
  3. # 4

MySQL is not an officially supported database, but there are some customers using it with PowerBuilder.  So I guess you have discovered one of the features that doesn't work because it is not an officially supported database.

Appeon does have plans to support MySQL in the future, but right now I cannot give you an approximate date.  So in the meantime, there are two options to move forward:

1. Workaround the unsupported feature by not using reserved words; or

2. Switch to another open source database that is supported, for example PostgreSQL.

Comment
There are no comments made yet.
Gerry Whitmarsh
Gerry Whitmarsh Accepted Answer Pending Moderation
  1. Monday, 15 July 2019 06:42 AM UTC
  2. PowerBuilder
  3. # 5

Thanks guys for the replies.

I guess my only choice is to rename the tables - ouch!

As MySQL is one of the most popular databases, I am really surprised that Powerbuilder doesn't support it - yet.

Again, thanks for the feedback.

Gerry.

Comment
Armeen Mazda @Appeon
  1. Armeen Mazda @Appeon
  2. Monday, 15 July 2019 20:23 PM UTC
It's coming, but we went with PostgreSQL first over MySQL because it better conforms to the SQL standard. So for existing PowerBuilder customers that are trying to migrate off of say Oracle or Sybase database then PostgreSQL would be a better migration path for them than MySQL.
  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.