Help please with adodb calling stored procedure in mysql db

[bbcat]

Hi All,

I need help with this.

I am using adodb in php to connect to mysql db. How can I call stored procedure using adodb?

Please kinldy help me. I can't find the code example.

Greatly appreciate all the help I can get.

Sincerely

Kate

[YAOMK]

I'm not familiar with adodb, but it is an abtraction layer for rdbms connectivity. For mysql, running a query with "CALL my_procedure()"; should be enough.

[bbcat]

I can't. When tried calling the store procedure it gave me an error.

[YAOMK]

well what was the error?

[bbcat]

This is what I got. My stored procedure work when call not from the adodb.

1312: PROCEDURE pmwiki.sp_getStudentCourseLoad can't return a result set in the given context

ADOConnection._Execute(call sp_getStudentCourseLoad (100607119), false) % line 893, file: adodb.inc.php

[YAOMK]

okay I did a little research and this is how they do it in ASP.

[bbcat]

The reason why I think I would need a call stored_procedure_name is because I am using mysql and not mssql. I tried using exec stored_procedure_name and it is not working 🙁

this is my code:

<?
error_reporting(E_ALL);
require_once("cookbook/adodb/adodb.inc.php");

$DB = NewADOConnection('mysql');

if (!$D😎 die("Connection failed");
$DB->debug = true;
$DB->Connect('localhost', 'root', 'pass', 'pmwiki');
$sql="100607119";
$rs = $DB->Execute("sp_getStudentCourseLoad $sql");

print "<pre>";
print_r($rs->GetRows());
print "</pre>";

?>

Thank you very much for your help

Kate

[YAOMK]

I got this from the mysql bugs log:

Description:
When calling a stored procedure that returns a result set, error 1312 is returned:

DIAG [S1000] [MySQL][MyODBC 5.00.09][MySQL] 1312 PROCEDURE <db>.<proc> can't return a
result set in the given context (1312)

How to repeat:
Create database, tables and a procedure that contains a SELECT. Call the procedure via
ODBC

Suggested fix:
Set CLIENT_MULTI_RESULTS or CLIENT_MULTI_STATEMENTS as default (or configurable) flags
when connecting to the datasource.

[danielsonchris]

Using ADODB I had the same issue. "1312: PROCEDURE" ... "can't return a result set in the given context".

Two ways to solve this problem, if you are using a more recent version of the ADODB library set you will have the ability to seed in a value into the $db->optionFlags variable.

1) New way:

//Modify your CONNECTION STRING: (use mysqli)
define('CONNSTR','mysqli://username:password@location/dbName');

 $db = NewADOConnection(CONNSTR);
     $db->optionFlags["CLIENT_MULTI_RESULTS"] = array( /*CLIENT_MULTI_RESULTS*/0x20000, 1);

2) Older version of ADODB, Found this helpful link. You will need to apply a patch to your ADODB library. (Apply it into the adodb/drivers/mysqli.*.php file)

http://blog.neofreko.com/index.php/2006/11/22/php-adodbs-mysqli-call-stored-proc-patch/

// Set connection options
	    // Not implemented now
if (function_exists(‘mysqli_options’)) {
         mysqli_options($this->_connectionID, /*CLIENT_MULTI_RESULTS*/0×20000, 1);
      }

Kind Regards,
Chris Danielson
MaxPowerSoft, LLC

[bbcat]

I did what you have suggested.

This is what I got from the error

Query: call sp_getStudentCourseLoad (100607119) failed. PROCEDURE pmwiki.sp_getStudentCourseLoad can't return a result set in the given context
1312: PROCEDURE pmwiki.sp_getStudentCourseLoad can't return a result set in the given context

ADOConnection._Execute(call sp_getStudentCourseLoad (100607119), false) % line 893, file: adodb.inc.php

This is my stored procedure

CREATE PROCEDURE sp_getStudentCourseLoad (studentid int )
BEGIN
select shortname from course where courseid in
(
select courseid from individual where cuid=studentid
);
END

this is my code

<?
echo "dum";
echo "test";
error_reporting(E_ALL);

require_once("cookbook/adodb/adodb.inc.php");

define('CONNSTR','mysqli://root:password@localhost/pmwiki');

$DB= NewADOConnection(CONNSTR);
$DB->optionFlags["CLIENT_MULTI_RESULTS"] = array( /CLIENT_MULTI_RESULTS/0x20000, 1);

$DB->debug = true;
// Set these parameters for your database:
// ---------------------------------------------------------------------------

// ---------------------------------------------------------------------------
/$rs = $DB->Execute("SELECT FROM accounts LIMIT 10");*/

$sql="100607119";
$rs = $DB->Execute("call sp_getStudentCourseLoad ($sql)");

print "<pre>";
print_r($rs->GetRows());
print "</pre>";

?>

Is there a better way to call stored procedure from mysql in php using adodb?? This is really driving me crazy. I really and deeply appreciate all the help, and patience you have extended to me. sigh Is there a light at the end of this tunnel?

Kate

[danielsonchris]

Kate,
May I first recommend changing the stored procedure to the following form:

CREATE PROCEDURE sp_getStudentCourseLoad (IN studentid INT)
BEGIN
SELECT c.shortname 
FROM course c, individual i 
WHERE c.courseid=i.courseid AND i.cuid=studentid;
END

What version of ADODB are you using?

Kind Regards,
Chris Danielson
MaxPowerSoft, LLC

[bbcat]

V5.02 version of ADODB
I have changed the stored procedure you have suggested

[danielsonchris]

• Are you still receiving the error after changing the stored procedure?

• Did you test the stored procedure from the command line? (Make sure it works flawlessly).

• Did you setup an ODBC connection for this database? In my test environment I am not using such a mechanism. I am instead just using a raw connection string as you have demonstrated in your previous example. My concern is that something is filtering the call of the stored procedures outside of the ADODB library set.

My example of calling:

/* cutil is a simple customized utility for massaging quote strings into a ANSI SQL standard.  (please ignore) */
$sql = "CALL readUser(" .
            cutil::SQLQuote($username) . "," .
            cutil::SQLQuote(md5($password)) . ")";
$rs = $db->Execute($sql);
if ($rs) {
            if (!$rs->EOF) {
                print_r($rs->fields);
            }
            $rs->Close();
}

The version I tested the fix posted in this thread was ADODB 5.00. Assuming you have the rights to make changes into the ADODB, then I would recommend adding a few print and exit statements into the "drivers/adodb-mysqli.inc.php" file. This will ensure that you have in fact modified the "optionFlags" variable, and that the library is in fact using your variable in connecting to the database.

Kind Regards,
Chris Danielson
MaxPowerSoft, LLC

[dewa_aji]

Hi, Kate

may be u could try these lines of code

/connection to database/

include_once 'adodb/adodb.inc.php';

$conn = &ADONewConnection('mysqli');
$conn->PConnect('host_name','user_name','pwd','database_name');

$conn->SetFetchMode(ADODB_FETCH_ASSOC);

if (!$conn) die ("Database Connection sql server failed");

/*
Stored Procedure name : sp_getLastACt
Parameters : par1 & par2

*/
$que = "CALL sp_getLastAct('test','simpsons')";

/n do like usual we using adodb class for executing query/

$res = $conn->Execute($que);

    echo "<pre>";
var_dump($res);
die();

/
it works > u can see the result ($res)
simple like usual (^-)
/