i found this
MySQL 5.1 also supports the execution of a string containing multiple
statements separated by semicolon (“;”)
characters. This capability is enabled by special options that are specified
either when you connect to the server with mysql_real_connect() or after
connecting by calling` mysql_set_server_option().
and i saw in hbmysql\mysql.c, there isn´t CLIENT_MULTI_STATEMENTS
can someone implements this in hbmysql?
/* connect to server with the CLIENT_MULTI_STATEMENTS option */
if (mysql_real_connect (mysql, host_name, user_name, password,
db_name, port_num, socket_name, CLIENT_MULTI_STATEMENTS) == NULL)
{
printf("mysql_real_connect() failed\n");
mysql_close(mysql);
exit(1);
}
/* execute multiple statements */
status = mysql_query(mysql,
"DROP TABLE IF EXISTS test_table;\
CREATE TABLE test_table(id INT);\
INSERT INTO test_table VALUES(10);\
UPDATE test_table SET id=20 WHERE id=10;\
SELECT * FROM test_table;\
DROP TABLE test_table");
if (status)
{
printf("Could not execute statement(s)");
mysql_close(mysql);
exit(0);
}
/* process each statement result */
do {
/* did current statement return data? */
result = mysql_store_result(mysql);
if (result)
{
/* yes; process rows and free the result set */
process_result_set(mysql, result);
mysql_free_result(result);
}
else /* no result set or error */
{
if (mysql_field_count(mysql) == 0)
{
printf("%lld rows affected\n",
mysql_affected_rows(mysql));
}
else /* some error occurred */
{
printf("Could not retrieve result set\n");
break;
}
}
/* more results? -1 = no, >0 = error, 0 = yes (keep looping) */
if ((status = mysql_next_result(mysql)) > 0)
printf("Could not execute statement\n");
} while (status == 0);
Best Regards
Fernando Athayde
mysql_close(mysql);
________________________________
De: Fernando Athayde <[email protected]>
Para: [email protected]
Enviadas: Quarta-feira, 14 de Abril de 2010 16:08:03
Assunto: [Harbour] tmysql and scripts
hello all
I can´t run scripts sql using tmysql, I guess my fault, but I can´t see where
cScript := ""
cScript += "DELIMITER $$"+CHR(10)+CHR(13)
cScript += "CREATE DEFINER = `ro...@`%` TRIGGER
`lancamentos_fixos_before_ins_tr` BEFORE INSERT ON `lancamentos_fixos` FOR EACH
ROW"+CHR(10)+CHR(13)
cScript += "BEGIN"+CHR(10)+CHR(13)
cScript += ""+CHR(10)+CHR(13)
cScript += "IF ( (NEW.idsequencial IS NULL) OR (NEW.idsequencial = 0) )
THEN"+CHR(10)+CHR(13)
cScript += "BEGIN"+CHR(10)+CHR(13)
cScript += " SELECT MAX(idsequencial) INTO @ultimo_id FROM
`lancamentos_fixos` WHERE idserver=NEW.idserver AND
idfilial=NEW.idfilial;"+CHR(10)+CHR(13)
cScript += ""+CHR(10)+CHR(13)
cScript += " IF ((@ultimo_id IS NULL) OR (@ultimo_id=0))
THEN"+CHR(10)+CHR(13)
cScript += " BEGIN"+CHR(10)+CHR(13)
cScript += " SET NEW.id = CONCAT( NEW.idserver, NEW.idfilial, 1
);"+CHR(10)+CHR(13)
cScript += " SET NEW.idsequencial = 1;"+CHR(10)+CHR(13)
cScript += " END;"+CHR(10)+CHR(13)
cScript += " ELSE"+CHR(10)+CHR(13)
cScript += " BEGIN"+CHR(10)+CHR(13)
cScript += " SET NEW.id = CONCAT( NEW.idserver, NEW.idfilial,
(@ultimo_id + 1) );"+CHR(10)+CHR(13)
cScript += " SET NEW.idsequencial = (@ultimo_id + 1);"+CHR(10)+CHR(13)
cScript += " END;"+CHR(10)+CHR(13)
cScript += " END IF;"+CHR(10)+CHR(13)
cScript += ""+CHR(10)+CHR(13)
cScript += " END;"+CHR(10)+CHR(13)
cScript += " END IF;"+CHR(10)+CHR(13)
cScript += "END $$"+CHR(10)+CHR(13)
cScript += "DELIMITER ;"+CHR(10)+CHR(13)
oServer:query( ALLTRIM( cScript ) )
but happens Error in your sql syntax
I tested the code in sql client and runs ok
the script
DELIMITER $$
CREATE DEFINER = `ro...@`%` TRIGGER `lancamentos_fixos_before_ins_tr` BEFORE
INSERT ON `lancamentos_fixos` FOR EACH ROW
BEGIN
IF ( (NEW.idsequencial IS NULL) OR (NEW.idsequencial = 0) ) THEN
BEGIN
SELECT MAX(idsequencial) INTO @ultimo_id FROM `lancamentos_fixos` WHERE
idserver=NEW.idserver AND idfilial=NEW.idfilial;
IF ((@ultimo_id IS NULL) OR (@ultimo_id=0)) THEN
BEGIN
SET NEW.id = CONCAT( NEW.idserver, NEW.idfilial, 1 );
SET NEW.idsequencial = 1;
END;
ELSE
BEGIN
SET NEW.id = CONCAT( NEW.idserver, NEW.idfilial, (@ultimo_id + 1) );
SET NEW.idsequencial = (@ultimo_id + 1);
END;
END IF;
END;
END IF;
END $$
DELIMITER ;
I don´t know how make this, can someone help me?
Best Regards
Fernando Athayde
_______________________________________________
Harbour mailing list (attachment size limit: 40KB)
[email protected]
http://lists.harbour-project.org/mailman/listinfo/harbour
