Page 1 of 2
MySQL question
Posted: Sun Nov 19, 2017 4:53 pm
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
Re: MySQL question
Posted: Mon Nov 20, 2017 5:30 am
by Rathinagiri
Have you tried select get_all_p0001()?
Re: MySQL question
Posted: Mon Nov 20, 2017 8:22 am
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
Re: MySQL question
Posted: Mon Nov 20, 2017 9:57 am
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
Re: MySQL question
Posted: Mon Nov 20, 2017 2:46 pm
by mol
Maybe mysql.dll has such a limitation?
Re: MySQL question
Posted: Mon Nov 20, 2017 4:58 pm
by serge_girard
Thanks Marek, Maybe it is. I will try to find out!
Serge
Re: MySQL question
Posted: Wed Nov 22, 2017 1:28 pm
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.
Re: MySQL question
Posted: Wed Nov 22, 2017 4:46 pm
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
Re: MySQL question
Posted: Wed Nov 22, 2017 6:59 pm
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")
Re: MySQL question
Posted: Thu Nov 23, 2017 8:31 am
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