MySQL question

General Help regarding HMG, Compilation, Linking, Samples

Moderator: Rathinagiri

User avatar
serge_girard
Posts: 3161
Joined: Sun Nov 25, 2012 2:44 pm
DBs Used: 1 MySQL - MariaDB
2 DBF
Location: Belgium
Contact:

MySQL question

Post by serge_girard »

Hello,

Does anybody know how to call a stored-procedure from within HMG?

I now have a procedure:

Code: Select all

CREATE PROCEDURE GET_ALL_P0001()
BEGIN
SELECT *  FROM P0001;
END  


Allready stored in MySQL and then I try to call it:

Code: Select all

IF SQL_Connect(qHST,qUSR,qPAW,qDBN)  == Nil 
   MSGINFO('SQL_CONNECT_ERROR' ,'NOK' )		 
   RETURN 
ENDIF

cQuery2  := 'CALL GET_ALL_P0001()'
cQuery2  := dbo:Query( cQuery2 )
IF cQuery2:NetErr()               // hier fout         
   ? PROCNAME() + '2 ' + cQuery2:Error()
   MSGINFO(cQuery2:Error(), pNOK )    
   RETURN
ENDIF

FOR j := 1 To cQuery2:LastRec() 
   aCurRowj := cQuery2:GetRow(j)

   c1	   := ALLTRIM(aCurRowj:fieldGet(1))
   c2    := ALLTRIM(aCurRowj:fieldGet(2))
   ? C1
   ? C2
NEXT   
sql_disconnect()
But then I get this error:

Code: Select all

PROCEDURE GET_ALL_P0001 can't return a result set in the given context
So I guess cQuery2 := 'CALL GET_ALL_P0001()' is not the right way.

Any help or suggestion is appreciated!

Serge
There's nothing you can do that can't be done...
User avatar
Rathinagiri
Posts: 5471
Joined: Tue Jul 29, 2008 6:30 pm
DBs Used: MariaDB, SQLite, SQLCipher and MySQL
Location: Sivakasi, India
Contact:

Re: MySQL question

Post by Rathinagiri »

Have you tried select get_all_p0001()?
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.
User avatar
serge_girard
Posts: 3161
Joined: Sun Nov 25, 2012 2:44 pm
DBs Used: 1 MySQL - MariaDB
2 DBF
Location: Belgium
Contact:

Re: MySQL question

Post by serge_girard »

Hi Rathi,

Thanks for the tip. Regretfully this doesn't work either: message FUNCTION .. doesn't exist.

I tried also: cQuery2 := 'GET_ALL_P0001()'

I thought it would be about the same as in PHP: http://php.net/manual/en/mysqli.quicks ... dures.php

Serge
There's nothing you can do that can't be done...
User avatar
serge_girard
Posts: 3161
Joined: Sun Nov 25, 2012 2:44 pm
DBs Used: 1 MySQL - MariaDB
2 DBF
Location: Belgium
Contact:

Re: MySQL question

Post by serge_girard »

Rathi, It works fine in PHP:

Code: Select all

$sql2    = "CALL GET_ALL_P0001() "; 
$res     = $mysqli->query($sql2);

while($row = $res->fetch_assoc()):
   $FIL_NR  = $row["FIL_NR"] ;
   $PRES_NR = $row["PRES_NR"] ; 
   $table   .= '<tr><td>' . $FIL_NR . '</td><td>'. $PRES_NR . '</td></tr>' ;
endwhile;	
Calling a UDF however works well:

Code: Select all

CREATE FUNCTION ID13454531.fnfullname ( id int(11) ) RETURNS varchar(160) CHARACTER SET utf8
COMMENT 'Returns the full name of person in ID13454531.FAMILY table referenced by id '
DETERMINISTIC
READS SQL DATA
BEGIN
DECLARE fulname varchar(160) CHARACTER SET utf8;
SELECT CONCAT_WS(' ', ID13454531.FAMILY.VOORNAAM,   ID13454531.FAMILY.FAM_NAAM) 
INTO FULNAME 
FROM ID13454531.FAMILY 
WHERE ID13454531.FAMILY.REC_NO=id;
RETURN FULNAME;
END

Code: Select all

cQuery2 := 'select fnfullname(12)'
? 'cQuery2', cQuery2
cQuery2      := dbo:Query( cQuery2 )
IF cQuery2:NetErr()               // hier fout         
   ? PROCNAME() + '2 ' + cQuery2:Error()
   MSGINFO(cQuery2:Error(), pNOK )    
   RETURN
ENDIF    

FOR j := 1 To cQuery2:LastRec() 
   aCurRowj          := cQuery2:GetRow(j)

   c1	   := ALLTRIM(aCurRowj:fieldGet(1))
   ? C1
NEXT    
Can't figure out why PROCDEDURE isn't working.

Serge
There's nothing you can do that can't be done...
User avatar
mol
Posts: 3718
Joined: Thu Sep 11, 2008 5:31 am
Location: Myszków, Poland
Contact:

Re: MySQL question

Post by mol »

Maybe mysql.dll has such a limitation?
User avatar
serge_girard
Posts: 3161
Joined: Sun Nov 25, 2012 2:44 pm
DBs Used: 1 MySQL - MariaDB
2 DBF
Location: Belgium
Contact:

Re: MySQL question

Post by serge_girard »

Thanks Marek, Maybe it is. I will try to find out!

Serge
There's nothing you can do that can't be done...
User avatar
koke
Posts: 116
Joined: Wed Aug 21, 2013 3:54 pm
DBs Used: DBF, mySql, mariaDB

Re: MySQL question

Post by koke »

Si el procedimiento te regresa una cadena de texto con el query deberias quitar las comillas y dejar solo el texto.
google translate.
If the procedure returns a string with the query, you should remove the quotes and leave only the text.
,___,
[O.o]
/)__)
-”–”-
KoKe
User avatar
serge_girard
Posts: 3161
Joined: Sun Nov 25, 2012 2:44 pm
DBs Used: 1 MySQL - MariaDB
2 DBF
Location: Belgium
Contact:

Re: MySQL question

Post by serge_girard »

Koke,

I'm afraid I don't understand what you mean!
Can you explain?
The calling PROC is always a string-array.

Serge
There's nothing you can do that can't be done...
User avatar
koke
Posts: 116
Joined: Wed Aug 21, 2013 3:54 pm
DBs Used: DBF, mySql, mariaDB

Re: MySQL question

Post by koke »

Lo siento el que no entendió fui yo.
Creí que la función era de harbour.
Pero realice pruebas y me funciono de la siguiente manera

y me trae el resultado de la función que yo cree
espero que te sirva de algo, siento la confusión.
saludos.

google translate

I'm sorry the one who did not understand was me.
I thought the function was harbor.
But I did tests and it worked in the following way

And it brings me the result of the function that i create
I hope it helps you, I am sorry for the confusion.
regards.

Code: Select all

cQuery := "select consultanom('01') as nombre"
bQuery := oServer:Query (cQuery)
If bQuery:NetErr()
	Msgstop(bQuery:Error())
	Return Nil
EndIf
oRow := bQuery:GetRow(1)
main.text_1.Value := oRow:FieldGet("nombre")
,___,
[O.o]
/)__)
-”–”-
KoKe
User avatar
dragancesu
Posts: 920
Joined: Mon Jun 24, 2013 11:53 am
DBs Used: DBF, MySQL, Oracle
Location: Subotica, Serbia

Re: MySQL question

Post by dragancesu »

Look https://code.tutsplus.com/articles/an-i ... -net-17843

I do not use, but I see that a better definition is needed, it must be defined what type of returns, then there is no problem of more columns and rows

And I'm going to go and I think it's easy and I forget what's required
Post Reply