Android Question call MySql stored procedure with ExecuteRemoteQuery

[achtrade]

Hello,

I have a stored procedure in MySQL, it receives 2 parameter and return an integer value.

How can I call this SP using ExecuteRemoteQuery and catch the returned value.

I'm using ExecuteRemoteQuery because my db is hosted in Godaddy.

thanks.

 

[Erel]

See this page: http://php.net/manual/en/mysqli.quickstart.stored-procedures.php

It seems that you just need to send a query with CALL.

 

[achtrade]

See this page: http://php.net/manual/en/mysqli.quickstart.stored-procedures.php

It seems that you just need to send a query with CALL.

it works, but I need to catch the output parameter @status, how can I do it ?

ExecuteRemoteQuery("CALL Insert(1, @status)", NEW_USER)

thanks.

 

[Erel]

You will need to modify the PHP script and do whatever you need in the script.

 

[achtrade]

You will need to modify the PHP script and do whatever you need in the script.

Ok, I'm not a PHP expertise but I'll try to do it.

Thanks.

 

[achtrade]

I solved this removing the output parameter from the sp and now it only return a single value using select statement. Now I need to extract that value from here

parser.Initialize(res)
Result = parser.NextArray

I did this but I'm not sure if this is the correct way

Result = parser.NextArray 'returns a list with maps
Dim m As Map
m = SPResult.Get(0)
Msgbox("Result=" & m.Get("Result"),"")

A little help with this

thanks.

 

[DonManfred]

what is the output of

log(m)

?
Or better

log(res)

If you have written that php file you are using it is most probably a good idea to post the php-code (wrapped in code tags)

 

[achtrade]

what is the output of

log(m)

?
Or better

log(res)

If you have written that php file you are using it is most probably a good idea to post the php-code (wrapped in code tags)

the output of res is {"Result"},{"-1"} and the php file is the same as in this post

https://www.b4x.com/android/forum/threads/connect-android-to-mysql-database-tutorial.8339/

 

[achtrade]

anyone ?

 

[DonManfred]

Missed your answer

Just saw the link to the php...

You log output does not look like a map.

see this for an example log output how a map should look like

    Dim m As Map = CreateMap("Month":1,"Result":10)
    Log(m)
        ' (MyMap) {Month=1, Result=10}

So i believe your "m" is NOT a Map.

Your output looks like a json string with two values (not a map)
the values are "Result" and "-1"

As the original php is not retrieving any results from a stored procedure i suggest to post the php-file you are USING (you can put xxx on the credentials as we dont need them)

maybe the "-1" IS the result?! ;-) (-1 in php is TRUE)

 

[achtrade]

Manfred,

The php is the same, I didn't change it at all

$con = mysql_connect($databasehost,$databaseusername,$databasepassword) or die(mysql_error());mysql_select_db($databasename) or die(mysql_error());mysql_query("SET CHARACTER SET utf8");$query = file_get_contents("php://input");$sth = mysql_query($query);

if (mysql_errno()) {header("HTTP/1.1 500 Internal Server Error");
 echo $query.'\n';
 echo mysql_error();
}
else
{$rows = array();
 while($r = mysql_fetch_assoc($sth)) {$rows[] = $r;
 }
 print json_encode($rows);
}?>

inside my MySQL SP I have a select statement like this: select '-1' as 'Result';

and this is how I call the SP

ExecuteRemoteQuery("CALL InsertNewUser('John', 'Smith', 'jsmith@hotmail.com', '3014337474')", NEW_USER)

and I have this in the JobDone sub

Dim res As String
res = Job.GetString
      
Dim parser As JSONParser
parser.Initialize(res)

SPResult = parser.NextArray 'returns a list with maps
Dim m As Map
m = SPResult.Get(0)
msgbox(m.Get("Result")

Everything is working fine, but like you say, I don't think that this is a map, so, what should be the right way to get a single value ?

thanks

 

[DonManfred]

Everything is working fine, but like you say, I don't think that this is a map, so, what should be the right way to get a single value ?

fixing you php... i can not see any mysqlquery call before the line

while($r = mysql_fetch_assoc($sth)) {$rows[] = $r;