I want to be able create stored procedures using phpMyAdmin and later on use it through php.
From what I know, I found out that we cannot manage stored procedures through phpMyAdmin.
What other tool can manage stored procedure?
I am not even sure if it is better option to use stored procedure through PHP. Any suggestion?
Forget about mysqli, it's much harder to use than PDO and should have been already removed. It is true that it introduced huge improvements over mysql, but to achieve the same effect in mysqli sometimes requires enormous effort over PDO i.e. associative fetchAll.
Instead, take a look at PDO, specifically prepared statements and stored procedures.
$stmt = $dbh->prepare("CALL sp_takes_string_returns_string(?)");
$value = 'hello';
$stmt->bindParam(1, $value, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 4000);
// call the stored procedure
$stmt->execute();
print "procedure returned $valuen";
Figured it out. This is not a bug with PHP (though it used to be) - it's a bug in some versions of phpmyadmin. The same bug intermittently reappears and is then fixed in various subversions (see above):
#1312 - PROCEDURE [name] can't return a result set in the given context
This behavior appears limited to SELECT statements within stored procedures inside phpmyadmin.
Using a client like MySQL Workbench works around the problem (or you could upgrade phpmyadmin, but that's a pain if you're on a shared server like I am).
Anyway, thanks to everyone for your help.
Maybe you can try the following (taken from https://.com/a/10513319/1236044 )
It uses CreateSQLQuery. You may try replacing the select... with Exec MyStoredProc
The key point is having your select or stored procedure return columns with the same name as the properties of the DTO you are trying to populate.
public class YourDto
{
public int YourDtoId { get; set; }
public string YourDtoTitle { get; set; }
}
then
var result = yourNhSession
.CreateSQLQuery("select yourColumn1 as YourDtoId, yourColumn2 as YourDtoTitle from YOUR_TABLE")
.SetResultTransformer(Transformers.AliasToBean<YourDto>())
.List<YourDto>();
I came up with the following solution using the concept of ResultTransformer described here:
public class DataTableResultTransformer : IResultTransformer
{
private DataTable dataTable;
public IList TransformList(IList collection)
{
var rows = collection.Cast<DataRow>().ToList();
rows.ForEach(dataRow => dataTable.Rows.Add(dataRow));
return new List<DataTable> { dataTable };
}
public object TransformTuple(object[] tuple, string[] aliases)
{
//Create the table schema based on aliases if its not already done
CreateDataTable(aliases);
//Create and Fill DataRow
return FillDataRow(tuple, aliases);
}
private DataRow FillDataRow(object[] tuple, string[] aliases)
{
DataRow dataRow = dataTable.NewRow();
aliases.ToList().ForEach(alias =>
{
dataRow[alias] = tuple[Array.FindIndex(aliases, colName => colName == alias)];
});
return dataRow;
}
private void CreateDataTable(IEnumerable<string> aliases)
{
if (dataTable == null)
{
dataTable = new DataTable();
aliases.ToList().ForEach(alias => dataTable.Columns.Add(alias));
}
}
}
And use it as following:
using (ISession session = sessionFactory.OpenSession())
{
var sqlQuery = session.CreateSQLQuery("SELECT ID, NAME, ADDRESS FROM CUSTOMER");
var transformedQuery = sqlQuery.SetResultTransformer(new DataTableResultTransformer());
return transformedQuery.List().Single();
}
I have just created a custom ResultTransformer and use it in my sql query to transform the result of the query based on my logic in the DataTableResultTransformer.
The TransformTupple method is called for each item in the result set. The tuple contains the data where as the aliases contain the names for the data. So, we have almost everything to build and fill our DataTable. Once all the items of the result set have been transformed by the TransformTupple method then the TransformList method is called at the end. The collection parameter contains all items that we transformed into DataRow in TransformTupple method. So, here we can easily fill our DataTable with the DataRows and return.
Hope its helpful for others dealing with same kind of scenario.
Since a stored procedure is created, altered and dropped using queries you actually CAN manage them using phpMyAdmin.
To create a stored procedure, you can use the following (change as necessary) :
And make sure you set the "Delimiter" field on the SQL tab to //.
Once you created the stored procedure it will appear in the Routines fieldset below your tables (in the Structure tab), and you can easily change/drop it.
To use the stored procedure from PHP you have to execute a CALL query, just like you would do in plain SQL.