1. Louis Arena
  2. PowerBuilder
  3. Tuesday, 11 August 2020 17:34 PM UTC

I am running PB 2019 R2 build 2353.  I currently use MS SQL Server.  I am in the process of converting to MySQL version 8.0.21.  I am able to connect to the database no problem using ODBC drivers.  I converted one of the simpler applications and it connects and opens no problem.  However this simple SQL statement generates the following error.

SQLSTATE = 37000
[MySQL][ODBC 8.0(a) Driver][mysqld-8.0.21]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*) from person where pid =?' at line 1

The SQL statement is 

select count(*)

into :i

from person

where pid = :ll_person

;

 

The problem seems to be the into :i

I am unable to find anything about this issue on the web.  Any help would be appreciated.

Thank you,

 

frederic roggemans Accepted Answer Pending Moderation
  1. Thursday, 13 August 2020 08:04 AM UTC
  2. PowerBuilder
  3. # 1
I am using mysql with powerbuilder with no problem via ODBC. In MySQL you can use sql_mode to get as close as possible to mssql oracle. Do not forget either OPTION in your connect string. Your example works perfectly with PB2017R3. Really shame not to support the database n ° 1 MySQL which is an excellent tool.
Comment
  1. frederic roggemans
  2. Thursday, 13 August 2020 16:03 PM UTC
Armeen, I think this is not the problem, powerbuilder supports postgres so via a native driver. But during an MsSQL migration to other db engines, it will not automatically transform the queries so (Embedded and datawindow sql syntax) there will inevitably be transformations to perform.

Exact ?
  1. Helpful
  1. Chris Pollach @Appeon
  2. Friday, 14 August 2020 03:23 AM UTC
Hi Frederic;

Correct, embedded SQL is at the mercy of the PB developer.

However, if DWO's use a "PBSELECT" (aka use graphic mode) in their SQL Painter, then they will *dynamically* align their SQL at run-time to the DBMS that they find active when the SetTransObject() command is executed.

HTH

Regards ... Chris
  1. Helpful
  1. frederic roggemans
  2. Friday, 14 August 2020 06:18 AM UTC
Thank you for these clarifications, Chris.

PowerBuilder is an awesome tool, and when used with ANSI code and PBSELECT allows database switching very easily.
  1. Helpful
There are no comments made yet.
Mark Lee @Appeon Accepted Answer Pending Moderation
  1. Wednesday, 12 August 2020 09:25 AM UTC
  2. PowerBuilder
  3. # 2

Hi Louis,

      1. So far PB has never officially supported MySQL.

      2. You can work it around by adopting the three resolutions below:

  1. One:

    Before you execute the SELECT statement, please add this code to run first.

string sql
sql = "SET sql_mode = 'IGNORE_SPACE' "
EXECUTE IMMEDIATE :sql ;

SELECT count (*) FROM employee where pid= ll_person ;

         Two:

         Change the ODBC settings for the MySQL Server Database, check the checkbox of the Ignore space after functions names item and save it and try it again.

        

         Three:

         You can work it around using the Dynamic SQL instead of the SELECT SQL statement as blew:

integer i

DECLARE my_cursor DYNAMIC CURSOR FOR SQLSA ;
PREPARE SQLSA FROM "SELECT count (*) FROM employee where pid="+string(ll_person) ;
OPEN DYNAMIC my_cursor ;
FETCH my_cursor INTO :i;
CLOSE my_cursor ;
  1. If you don't use the internal function "count", then the issue won’t happen.

          You can try running this code below and see if it works.

select pid
into :i
from person
where pid = :ll_person ;

 

    The reason is the internal function standard is different between MS SQL Server and MYSQL Server.

For example, there is a space between "count" and "(" in the command below. With this space, you can run this command below in MS SQL Server or ASA Server or Oracle Server, but you can't use this command in MYSQL server.

When you compile this code in PB, the count function will auto add the space between "count" and "(" and causes the error.

e.g.:

SELECT count ( * ) FROM employee where pid=1;  

 

Regards,

 

Comment
  1. Louis Arena
  2. Wednesday, 12 August 2020 13:08 PM UTC
Mark this is great and I see how it works. But my thoughts are that this is a very large application that ultimately manages all functions of our business. The changes to the application to make this work would be very extensive. But the bigger issue is that I have so many stored procedures and external functions that also need to run that I will not be comfortable running a back end that is not supported by PB.

Thanks
  1. Helpful
  1. Armeen Mazda @Appeon
  2. Wednesday, 12 August 2020 15:42 PM UTC
I’m confused... why would you try to use an unsupported database?
  1. Helpful
There are no comments made yet.
Louis Arena Accepted Answer Pending Moderation
  1. Tuesday, 11 August 2020 17:54 PM UTC
  2. PowerBuilder
  3. # 3

Because I am working with an outside developer and we just happened to say, use MySQL and move to AWS.  I am not committed to any database at this point, but I had no idea that MySQL was not supported.  Thanks for the info, I am going to download PostgreSQL and try that.  The point is, I do not want to upgrade my SQL Server because their licensing costs are just rediculous.

thanks

Comment
  1. Chris Pollach @Appeon
  2. Tuesday, 11 August 2020 18:13 PM UTC
I hear you. PostgreSQL is free. ?
  1. Helpful
  1. Louis Arena
  2. Tuesday, 11 August 2020 18:33 PM UTC
I will let you know, I just reached out to you.
  1. Helpful
There are no comments made yet.
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Tuesday, 11 August 2020 17:40 PM UTC
  2. PowerBuilder
  3. # 4

Hi Louis;

  FYI : Note that Appeon PB does not officially support MySQL. Why not use PostgreSQL?

Regards... Chris

Comment
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.