Hi Elvis,

1. So far PB has never officially supported MySQL.
2. You can work it around by adopting the three resolutions below:

       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 ;

3. 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,

So there is no solution to this?????  This was written in 2017.  It is 2020 I am converting from MS SQL to MySQL and getting the same issue and cannot find a solution.

se me ocurrio una idea pero solo es una solucion temporal que funciona. Cree una funcion:
(I came up with an idea but it is only a temporary solution that works. Create a function:)

f_RunSQLSintaxWithFuncion(string as_sql, boolean ab_retornavalor, transaction at_sqlca)

string ls_dato
if ab_RetornaValor then//retorna algun valor devuelto por el script
    DECLARE micursor DYNAMIC CURSOR FOR SQLSA; 
    PREPARE SQLSA FROM :as_sql USING at_sqlca; 
    OPEN DYNAMIC micursor; 
    FETCH micursor INTO :ls_dato; 
    CLOSE micursor; 
    return ls_dato;
else//solo ejecuta
    EXECUTE IMMEDIATE :as_sql USING at_SQLCA ;
     if at_SQLCA.sqlcode = -1 then
        return at_SQLCA.SQLerrtext
     end if    
     return ''
end if

Se le invoca de la siguiente manera
(​It is invoked as follows)

//Esta sentencia retorna un valor(This statement returns a value) 

STRING ls_script
INTEGER li_total
ls_script="SELECT COUNT(id_obra) FROM obras_trabajador WHERE status='N'"
li_total=integer(f_runsqlsintaxwithfunction(ls_script , true, SQLCA))

MessageBox("total", String(li_total))

//Esta sentencia solo ejecuta una actualización (This statement only executes an update)
ls_script="UPDATE obras_trabajador SET status='D' WHERE status='N'"
f_runsqlsintaxwithfunction(ls_script, false, SQLCA)

Espero que en algun momento el soporte a MYSQL sea completo pero hasta entonces planteo la solución
​(I hope that at some point the support for MYSQL is complete but until then I pose the solution)

Regards
Elvis Cardenas

Try using COUNT(*) instead of COUNT(colname).

Can you run those same SQL statements directly in MySQL, using the mysql.exe prompt or MySQL Workbench.

Certainly MySQL has the normal Count aggregate function.