|
| Many SQL Commands at a time with HExecuteSQLQuery |
| Iniciado por marco.a.machado, 05,mar. 2020 02:57 - 2 respuestas |
| |
| | | |
|
| |
Miembro registrado
7 mensajes |
|
| Publicado el 05,marzo 2020 - 02:57 |
I spent a whole day trying to find out a solution for this:
Inside a string variable I have many insert SQL command separated by comma. It´s generated on-the-fly using data received using a Webservice. It´s a bulk load insert.
Some errors can occur in this process like the I faced out today. A special caracter. It broked one of 400 SQL commands inside of my "SQL Buffer Commands".
My code is :
Procedure InsertSQLCommands(insCMD)
SQLTransaction(sqlStart,MyCon)
WHEN EXCEPTION IN
IF HExecuteSQLQuery(dsExecDB,MyCon,hQueryWithoutCorrection,insCMD) THEN
IF ErrorOccurred THEN
Info(ErrorInfo(errFullDetails))
END
SQLTransaction(sqlCommit,MyCon)
RESULT True
ELSE
gLog.AddMessage("Falha Inserindo BulkLoad de Publicacoes", "ERRO")
SQLTransaction(sqlRollBack,MyCon)
fSaveText(".\inssql_Erro_" + SysDateTime() + ".sql",insCMD)
RESULT False
END
DO
gLog.AddMessage("Falha Inserindo BulkLoad de Publicacoes", "Excecao")
SQLTransaction(sqlRollBack,MyCon)
fSaveText(".\inssql_Exc_" + SysDateTime() + ".sql",insCMD)
RESULT False
END
Questions:
Why HExecuteSQLQuery doesnt´t return a false value when an error occurs?
Transactions doesn´t work?
Windev is good enougth to work with just and SQL commmand at a time ?
Does some one can give some light about it? |
| |
| |
| | | |
|
| | |
| |
Miembro registrado
102 mensajes |
|
| Publicado el 09,marzo 2020 - 17:43 |
Hi Marco,
just two ideas.
First, try to use HCancelDeclaration() and HFreeQuery() before your HexecuteSQLQuery()
Second, I'm not shure if the return value of HExecuteSQLQuery is reliable, especially if you do an insert and especially if you do a query without correction. Why don't you try
WHEN EXCEPTION IN
HCancelDeclaration()
HFreeQuery()
HExecuteSQLQuery(dsExecDB,MyCon,hQueryWithoutCorrection,insCMD)
DO
Info(ErrorInfo(errFullDetails))
END
HTH
Sebastian
--
WD23 with Oracle, WDM23 Apps for Android, iOS and Windows CE.
Python 3 with Oracle and MySQL. PHP with MySQL and JSON. |
| |
| |
| | | |
|
| | |
| |
Miembro registrado
7 mensajes |
|
| Publicado el 10,marzo 2020 - 15:40 |
Hi Sebastian
My effords were for nothing.
HExecuteSQLQuery just works fine using one SQL command at a time.
I were using 400 SQL Insert Commands and one of those was broken with a syntaxe error and HExecuteSQLQuery execute until the wrong sql command, stop executin and returns a TRUE like all fine.
It´s wrong!
After that, I decide to follow a tip from Andy Stapleton about Mysql Insert with multi rows.
https://www.mysqltutorial.org/mysql-insert-multiple-rows/
it was worth a lot.
Instead of many inside of a string like:
INSERT INTO table (Col1, Col2) Values ('val1','Val2');
INSERT INTO table (Col1, Col2) Values ('val1','Val2');
INSERT INTO table (Col1, Col2) Values ('val1','Val2');
INSERT INTO table (Col1, Col2) Values ('val1','Val2');
I had used one Insert command with multi rows as follow:
INSERT INTO table (Col1, Col2) Values ('val1','Val2'),
('val3','Val4'),
('val','Val6');
Thanks Andy for the tip!
It works great!
Thanks Sebastian for your help and suggestions |
| |
| |
| | | |
|
| | | | |
| | |
|