I will develop in the near future some sections of a Haxe application
that will wrap the management of the database so that our game developer can
roar free into the gameplay.
The database we intend to use is MySql, since the support for
other multi-user database is, at least from what I saw, very preliminary in the
Haxe environment.
We are interested into the Cpp target for our needs.
The basic issue is
that the haxe API does not allow me to receive a result set from a stored
procedure.
What I found out is that:
1- Haxe cpp code file “mysql.cpp” states that, on method static
value mysql_connect( value params ) the
following:
LINE 513: [.....] mysql_real_connect(cnx,val_string(host),val_string(user),val_string(pass),NULL,val_int(port),val_is_null(socket)?NULL:val_string(socket),0) [.....]
Note the hard-codded
0 as the last parameter.
MySql API reference on this webpage has that http://dev.mysql.com/doc/refman/5.0/en/mysql-real-connect.html
|
CLIENT_MULTI_RESULTS
|
Tell the server that
the client can handle multiple result sets from multiple-statement executions
or stored procedures. This flag is automatically enabled if CLIENT_MULTI_STATEMENTS is enabled. See the note following this
table for more information about this flag.
|
MySQL
# TABLE!!!!!
CREATE TABLE `testtable` (
`idTestTable` int(10) unsigned NOT NULL AUTO_INCREMENT,
`TestTablecol` varchar(45) DEFAULT NULL,
PRIMARY KEY (`idTestTable`),
UNIQUE KEY `idTestTable_UNIQUE` (`idTestTable`)
) ENGINE=InnoDB AUTO_INCREMENT=43 DEFAULT CHARSET=utf8;
# SELECT SPROC
DELIMITER $$
CREATE DEFINER=`haxe_dev`@`%` PROCEDURE `select_test_table`()
SQL SECURITY INVOKER
BEGIN
SELECT `testtable`.`idTestTable`,
`testtable`.`TestTablecol`
FROM `haxe_dev`.`testtable`;
END$$
DELIMITER ;
# INSERT SPROC
DELIMITER $$
CREATE DEFINER=`haxe_dev`@`%` PROCEDURE `insert_test_table`(`col_name` varchar(45))
BEGIN
INSERT INTO `haxe_dev`.`testtable`
(`TestTablecol`)
VALUES
(col_name);
END$$
DELIMITER ;
# TESTING!
#Receives result set with no problem
CALL `haxe_dev`.`select_test_table`();
# Inserts with no problem
CALL `haxe_dev`.`insert_test_table`("BLABLABLA");
HAXE TEST APPLICATION
package ;
import cpp.Lib;
import sys.db.Mysql;
import sys.db.Connection;
import sys.db.ResultSet;
/**
* ...
* @author Rafael S Macedo
*/
class Main
{
static function main()
{
// Creating the Connection
var conn:Connection = sys.db.Mysql.connect( {
host: "escri-1",
port: 3306,
user: "haxe_dev",
pass: "haxe_dev",
socket: null,
database: "haxe_dev"
});
var int_RandNumber = Std.random(2000);
var str_Name = 'New From Haxe and Stored Procedure! $int_RandNumber';
// Sends insert to the database as SProc!
// WORKS!
var str_Command = 'CALL `insert_test_table`("$str_Name");';
var dbResult:ResultSet = conn.request(str_Command);
// Sends select to the database as SProc
// Does not work!
str_Command = 'CALL `select_test_table`();';
dbResult = conn.request(str_Command);
conn.close();
}
}
RESULT
Called from Main::main::Main.hx::40
Called from sys.db._Mysql.MysqlConnection::request::C:\HaxeToolkit\haxe\std/cpp/
_std/sys/db/Mysql.hx::118
Called from extern::cffi::C:/HaxeToolkit/haxe/lib/hxcpp/3,1,39/src/hx/Lib.cpp::1
19
Error : CALL `select_test_table`(); PROCEDURE haxe_dev.select_test_table can't r
eturn a result set in the given context
The error "PROCEDURE can't return a result set in the given context" is influenced by the parameter CLIENT_MULTI_RESULTS that has to be passed as the last argument of function mysql_real_connect.
I still didn't dig into it, but I suppose the return from SQL would be in that case an array of result sets.
My Idea / Wish: Extend the base API so that, at least as far as CPP is concerned, allow a return of one result set from a stored procedure. I do not need several result sets to be returned, only one (as shown).
This would be done by:
1- Allowing the client_flag parameter to be optionally passed using the sys.db.Mysql.connect method.
2- After executing the command that would return several result sets, manipulate it so that the first result is given to the client application the same way as a result set is given from a SELECT command.
The questions are:
1- Do you guys have a better idea on how to solve the problem?
2- I am new to Haxe, so I need information on how to properly change the code of mysql.cpp, on how to change my applicaiton to use the not-standard version of the mysql.cpp file.
Thank you very much for any insight on this.
Congratulations on such a job well done. Haxe is great.
Best Regards,
