1. Elvis Cardenas
  2. PowerBuilder
  3. Monday, 28 August 2017 22:12 PM UTC
Buen día tengo el siguiente problema con mi gestor de base de datos(MYSQL):
1)INTEGER LI_ROWS SELECT COUNT(id_obra) INTO :LI_ROWS FROM obras_trabajador USING SQLCA;
 
ERROR:FUNCTION BD.COUNT does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual

2)INTEGER LI_ROWS SELECT COUNT(*) INTO :LI_ROWS FROM obras_trabajador USING SQLCA;

ERROR:SQLSTATE = 37000

[MySQL][ODBC 5.1 Driver][mysqld-5.7.14]
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 obras_trabajador' at line 1

 
Soporta MYSQL? cual podría ser el error?, siempre tuve ese problema con MYSQL no con otros Gestores de Base de Datos.
 
Mark Lee @Appeon Accepted Answer Pending Moderation
  1. Wednesday, 12 August 2020 09:26 AM UTC
  2. PowerBuilder
  3. # 1

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 ;
  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
There are no comments made yet.
Louis Arena Accepted Answer Pending Moderation
  1. Tuesday, 11 August 2020 17:26 PM UTC
  2. PowerBuilder
  3. # 2

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.

Comment
  1. Louis Arena
  2. Tuesday, 11 August 2020 18:03 PM UTC
It does not explain anything. This is the code

select count(*)

into :i

from person

where pid = :pb_var.long_var[1]

;

This is the 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



works perfect in SQL Server
  1. Helpful
  1. Elvis Cardenas
  2. Tuesday, 11 August 2020 20:49 PM UTC
Al parecer es un error que pb al trasnformar las sentencias SQL de MySQL



Encontré otra solución después de tanto buscar... al iniciar la aplicación ejecuta la siguiente sentencia..

SET SESSION sql_mode = 'IGNORE_SPACE'; using SQLCA



usando la funcion que cree anteriormente:

f_RunSQLSintaxWithFuncion("SET SESSION sql_mode = 'IGNORE_SPACE';", false ,SQLCA)





saludos.
  1. Helpful
  1. Elvis Cardenas
  2. Tuesday, 11 August 2020 20:58 PM UTC
otra opcion es en el ODBC es en Flags 3 y deben de marcar: "Ignore space after function names "
  1. Helpful
There are no comments made yet.
Elvis Cardenas Accepted Answer Pending Moderation
  1. Tuesday, 5 September 2017 17:45 PM UTC
  2. PowerBuilder
  3. # 3

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

Comment
There are no comments made yet.
Roland Smith Accepted Answer Pending Moderation
  1. Tuesday, 29 August 2017 00:44 AM UTC
  2. PowerBuilder
  3. # 4

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

Comment
  1. Daniel Vivier
  2. Tuesday, 29 August 2017 14:47 PM UTC
The original post shows they already tried that with the same result.

  1. Helpful
  1. Narayana Bhat
  2. Tuesday, 5 September 2017 11:55 AM UTC
Hi



I have also faced same issue, but till now i have not got solutions for the same



Narayana

  1. Helpful
There are no comments made yet.
Daniel Vivier Accepted Answer Pending Moderation
  1. Tuesday, 29 August 2017 00:12 AM UTC
  2. PowerBuilder
  3. # 5

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.

Comment
  1. Elvis Cardenas
  2. Tuesday, 5 September 2017 17:14 PM UTC
Yes, run normally



 

  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.
We use cookies which are necessary for the proper functioning of our websites. We also use cookies to analyze our traffic, improve your experience and provide social media features. If you continue to use this site, you consent to our use of cookies.