Joomla & stored procedures

[Grzesiek]

Hello!

Will someone be so nice and tell me if in J! I can somehow use stored
procedures from mysql by Joomla's framework?

Code below doesn't work.

[CODE] $db =& JFactory::getDBO();
$query = 'CALL TestGet();';
$db->setQuery($query);
$db->loadRow();[/CODE]

Error:
[CODE]JDatabaseMySQL::query: 1064 - You have an error in your SQL
syntax; check the manual that corresponds to your MySQL server version
for the right syntax to use near '' at line 1 SQL=CALL TestGet(;[/
CODE]

Maybe someone help me.

[Nick Weavers]

I think Joomla is just echoing the message it got from mySQL here.
Have you tried executing the same call in phpmyadmin via the SQL query
"window"?

[jonnathan]

Try to remove the final semicolon:
...
$query = 'CALL TestGet()';
...

[Grzesiek]

Yes - in phpMyAdmin I can not execute stored procedures, but I've tras
that this is normal.

From MySQL Workbench it's ok.

I can also get result from standard way of querying mysql using
mysqli,fetching etc.

I thought I can get results with JDatabase api :-(

[Grzesiek]

Hmm ... now I'm getting this error:

JDatabaseMySQL::query: 2014 - Commands out of sync; you can't run this
command now SQL=SELECT id, title, module, position, content,
showtitle, params, mm.menuid FROM jos_modules AS m LEFT JOIN
jos_modules_menu AS mm ON mm.moduleid = m.id WHERE m.published = 1 AND
(m.publish_up = '0000-00-00 00:00:00' OR m.publish_up <= '2011-03-16
21:31:28') AND (m.publish_down = '0000-00-00 00:00:00' OR
m.publish_down >= '2011-03-16 21:31:28') AND m.access IN (1,1) AND
m.client_id = 0 AND (mm.menuid = 474 OR mm.menuid <= 0) ORDER BY
position, ordering

[Brad Gies]

This works fine for me :

$query = 'CALL project_category_insert_update(' . $data['id']
. ', ' . $cat_id . ')';
$db->setQuery($query);
$db->query();

BUT... I'm using the mysqli driver NOT the mysql driver. Which driver
are you using?

Sincerely,

Brad Gies
-----------------------------------------------------------------------
MaxHOMEValue.com
Kelowna, British Columbia, Canada
email: bg...@maxhomevalue.com
http://maxhomevalue.com http://bgies.com
-----------------------------------------------------------------------

[Grzesiek]

I'm using MySQLi driver.

I found two interesting things.

This code (using standard php way) is working well:

$app =& JFactory::getApplication();
$host = $app->getCfg('host');
$db = $app->getCfg('db');
$user = $app->getCfg('user');
$password = $app->getCfg('password');

// MYSQLI
$mysqli = new mysqli($host, $user, $password, $db);
$rs = $mysqli->query("CALL pageload('asdasd',
42,'127.0.0.1');");
while ($row = $rs->fetch_object()) {
echo print_r($row);
}


I also found that I have to pass output parameters for my procedure if
I want to get final record using Joomla Framework.

The problem is that my stored procedure returns one row with over 30
columns (I know, so many, but I really need em).

Why I can not get results from sproc by joomla's queries?

On 16 Mar, 18:19, Brad Gies <rbg...@gmail.com> wrote:
> This works fine for me :
>
>           $query = 'CALL project_category_insert_update(' . $data['id']
> . ', ' . $cat_id . ')';
>           $db->setQuery($query);
>           $db->query();
>
> BUT... I'm using the mysqli driver NOT the mysql driver. Which driver
> are you using?
>
> Sincerely,
>
> Brad Gies
> -----------------------------------------------------------------------
> MaxHOMEValue.com
> Kelowna, British Columbia, Canada

> email: bg...@maxhomevalue.comhttp://maxhomevalue.com     http://bgies.com

[Nick Weavers]

So this doesn't work?

$db = JFactory::getDBO();

$query = "CALL pageload('asdasd', > 42,'127.0.0.1');";
$db->setQuery($query);
$db->query();

$error_msg = $db->getErrorMsg();

$row = $db->loadObject();

if ($row) {

foreach ($row as $field=>$value) {
printf("Field: %s, Value: %s<br>", $field, $value);