|
| Multiple insert statements via one HFSQL statement |
| Iniciado por guest, 04,jun. 2015 22:31 - 15 respuestas |
| |
| | | |
|
| |
| Publicado el 04,junio 2015 - 22:31 |
I'm attempting to execute multiple insert statements via one SQL statement like so:
INSERT INTO ExampleTable(DATE,Activity,ItemID,EmployeeID) VALUES
('20150604',"Example statement.",5820,1),
('20150604',"Example statement.",9264,1),
('20150604',"Example statement.",9265,1),
('20150604',"Example statement.",5838,1),
('20150604',"Example statement.",5858,1),
('20150604',"Example statement.",5818,1),
('20150604',"Example statement.",19798,1),
('20150604',"Example statement.",5827,1),
('20150604',"Example statement.",5819,1),
('20150604',"Example statement.",150,1),
('20150604'," Example statement.",9261,1) |
| |
| |
| | | |
|
| | |
| |
| Publicado el 05,junio 2015 - 12:02 |
Hi James,
afaik this works from WinDev Version 20, not in earlier versions. |
| |
| |
| | | |
|
| | |
| |
| Publicado el 05,junio 2015 - 16:33 |
| I see, thank you very much Stefan! This code isn't for our setup of Windev 20, is this feature not included in the pre-release? |
| |
| |
| | | |
|
| | |
| |
| Publicado el 05,junio 2015 - 16:41 |
Hi James,
I don't know if this feature is included in the pre-release. |
| |
| |
| | | |
|
| | |
| |
| Publicado el 05,junio 2015 - 18:32 |
James
Just tested this - it is working in WD20 US pre-release.
It will not handle automatic identifiers - you will need to supply the values in your insert statement and apparently it requires single quotes around text - 'Example statement.' |
| |
| |
| | | |
|
| | |
| |
| Publicado el 06,junio 2015 - 00:01 |
Thanks to all who've replied so far!
Unfortunately the Insertion query still isn't working well with me. Here's the code I try and execute:
INSERT INTO ItemHistory(DATE,Activity,ItemID,EmployeeID) VALUES
('20150605','Example statement.','16309','1'),
('20150605','Example statement.','16051','1'),
('20150605','Example statement.','8800','1'),
('20150605','Example statement.','13719','1')
which errors out and gives me the standard error message along with the message "function not managed." Any ideas as to what this could mean or if I could be executing the query incorrectly?
And just to confirm we are updated to the WinDev 20 pre-release (CC200HF.DLL = 20.0.0.2, CC200HF.EXE = 20.0.93.0). |
| |
| |
| | | |
|
| | |
| |
| Publicado el 06,junio 2015 - 11:12 |
Hi James
Just a thought: Could you try without single quotes on itemID and EmployeeID ?
INSERT INTO ItemHistory(DATE,Activity,ItemID,EmployeeID) VALUES
('20150605','Example statement.',16309,1),
('20150605','Example statement.',16051,1),
('20150605','Example statement.',8800,1),
('20150605','Example statement.',13719,1)
Best regards
Viggo Poulsen |
| |
| |
| | | |
|
| | |
| |
| Publicado el 08,junio 2015 - 15:03 |
Thanks for the reply Viggo!
Unfortunately no, I still get the same error. |
| |
| |
| | | |
|
| | |
| |
| Publicado el 08,junio 2015 - 16:17 |
James
The following test worked for me......
MyQuery is Data Source sqltest is string = [ INSERT INTO ItemHistory(DATE,Activity,ItemID,EmployeeID) VALUES ('20150605','Example statement.',16309,1), ('20150605','Example statement.',16051,1), ('20150605','Example statement.',8800,1), ('20150605','Example statement.',13719,1) ] HExecuteSQLQuery(MyQuery,hModifyFile,sqltest)
Using latest WinDev 20 US pre-release
CC200HF.DLL = 20.0.0.2 - 29/10/2014
CC200HF.EXE = 20.0.93.0 - 02/04/2014
Module 20.0.113.0
Internal version 01A200057p
File declared as HFSQL Classic, running in HFSQL Client Server |
| |
| |
| | | |
|
| | |
| |
| Publicado el 08,junio 2015 - 17:44 |
| Thank you for your reply Derek! Unfortunately that does not work for me even when I have copied word-for-word. Does anyone know what the "function not managed" part of the error message could mean? |
| |
| |
| | | |
|
| | |
| |
| Publicado el 08,junio 2015 - 19:10 |
James
Sounds like it is the INSERT function it does not like.
If you are running with HFSQL v20 Control Centre then try dropping the query into there and running it, an error will be returned if the query is invalid.
Also try running the code in the debugger - If the HExecuteSQLQuery() returns False then a couple of 'Error' lines will appear in the list at the bottom of the window - 1 of them (the top I believe) may give you some information.
Also try closing the project, deleting the .cpl file and trying again. |
| |
| |
| | | |
|
| | |
| |
| Publicado el 08,junio 2015 - 19:56 |
Hi
I used wdSql (32-bit) (v19-hfSQL20) to submit
INSERT INTO myName (NAME, description)
VALUES('hello4','desc4'),
('hello5','desc5')
and wdSql (64-bit) (v20-to v20-hfSQL) with no problem
at all where the sequence ID generated by hyperfile.
I couldn't test wdSQL 19 since I upgraded to 20.
Will try out the wd coding inside to see if there's a difference.
HTH
King |
| |
| |
| | | |
|
| | |
| |
| Publicado el 08,junio 2015 - 20:00 |
Hi James,
the only difference I can think of is that YOU may have one of the field set as automatic ID in your record, and are trying to FORCE the value in it, and that would not work
Best regards |
| |
| |
| | | |
|
| | |
| |
| Publicado el 08,junio 2015 - 20:26 |
hi
Pls see as below (works as a charm):
sSql is string
sConn is string = "Hi"
trace (HOpenConnection(sConn, myUserName, myPass, theIP, theDB,hAccessHFClientServer,hOReadWrite))
sDS is Data Source
sSql = [
INSERT INTO [myName] ([NAME], [description])
VALUES('hello988','desc8'),
('2','2'),
('3','3')
]
info (sSql)
if not HExecuteSqlQuery(sDS, sConn, hWithTransaction,sSql) then
error(HErrorInfo())
else
info("Inserted")
end
HConnectionClose(sConn)
HTH
King |
| |
| |
| | | |
|
| | |
| |
| Publicado el 08,junio 2015 - 20:39 |
Thank you everyone for your help!
I was able to find a way to work around my problems using insertation via select statement:
INSERT INTO ExampleTable(DATE,Activity,ItemID,EmployeeID)
Select * from
(select distinct '20150608','ExampleStatement.','15030','1' from dual
union all
select distinct '20150608','ExampleStatement.','15040','1' from dual) |
| |
| |
| | | |
|
| | |
| |
| Publicado el 04,septiembre 2015 - 18:46 |
We have three WinDev (Ver 20) in house developers, who tested the query and found out that it only works on HFSQL Classic file and not on HFSQL C/S file. We contacted PC Soft support and they simply said, "The syntax use in HFSQL Classic are the same in HFSQL C/S. There is no limitation", but never did any test.
Can someone please confirm if this works on HFSQL C/S file. Please make sure to test the query on a HFSQL C/S rather than HFSQL Classic file . |
| |
| |
| | | |
|
| | | | |
| | |
|