MySQL question

General Help regarding HMG, Compilation, Linking, Samples

Moderator: Rathinagiri

User avatar
serge_girard
Posts: 1905
Joined: Sun Nov 25, 2012 2:44 pm
DBs Used: 1 MySQL - MariaDB
2 DBF
Location: Belgium
Has thanked: 314 times
Been thanked: 74 times
Contact:

MySQL question

Post by serge_girard » Sun Nov 19, 2017 4:53 pm

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

User avatar
Rathinagiri
Posts: 5181
Joined: Tue Jul 29, 2008 6:30 pm
DBs Used: MariaDB, SQLite, SQLCipher and MySQL
Location: Sivakasi, India
Has thanked: 141 times
Been thanked: 131 times
Contact:

Post by Rathinagiri » Mon Nov 20, 2017 5:30 am

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: 1905
Joined: Sun Nov 25, 2012 2:44 pm
DBs Used: 1 MySQL - MariaDB
2 DBF
Location: Belgium
Has thanked: 314 times
Been thanked: 74 times
Contact:

Post by serge_girard » Mon Nov 20, 2017 8:22 am

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

User avatar
serge_girard
Posts: 1905
Joined: Sun Nov 25, 2012 2:44 pm
DBs Used: 1 MySQL - MariaDB
2 DBF
Location: Belgium
Has thanked: 314 times
Been thanked: 74 times
Contact:

Post by serge_girard » Mon Nov 20, 2017 9:57 am

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

User avatar
mol
Posts: 2874
Joined: Thu Sep 11, 2008 5:31 am
Location: Myszków, Poland
Has thanked: 133 times
Been thanked: 66 times
Contact:

Post by mol » Mon Nov 20, 2017 2:46 pm

Maybe mysql.dll has such a limitation?

User avatar
serge_girard
Posts: 1905
Joined: Sun Nov 25, 2012 2:44 pm
DBs Used: 1 MySQL - MariaDB
2 DBF
Location: Belgium
Has thanked: 314 times
Been thanked: 74 times
Contact:

Post by serge_girard » Mon Nov 20, 2017 4:58 pm

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

Serge

User avatar
koke
Posts: 27
Joined: Wed Aug 21, 2013 3:54 pm
Has thanked: 1 time
Been thanked: 5 times

Post by koke » Wed Nov 22, 2017 1:28 pm

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: 1905
Joined: Sun Nov 25, 2012 2:44 pm
DBs Used: 1 MySQL - MariaDB
2 DBF
Location: Belgium
Has thanked: 314 times
Been thanked: 74 times
Contact:

Post by serge_girard » Wed Nov 22, 2017 4:46 pm

Koke,

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

Serge

User avatar
koke
Posts: 27
Joined: Wed Aug 21, 2013 3:54 pm
Has thanked: 1 time
Been thanked: 5 times

Post by koke » Wed Nov 22, 2017 6:59 pm

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: 477
Joined: Mon Jun 24, 2013 11:53 am
DBs Used: DBF, MySQL, Oracle
Location: Subotica, Serbia
Has thanked: 3 times
Been thanked: 73 times

Post by dragancesu » Thu Nov 23, 2017 8:31 am

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