Diese Seite mit anderen teilen ...

Informationen zum Thema:
Forum:
WinDev Forum
Beiträge im Thema:
24
Erster Beitrag:
vor 2 Jahren, 11 Monaten
Letzter Beitrag:
vor 2 Jahren, 9 Monaten
Beteiligte Autoren:
DerekT, James Covington, Curtis, kingdr, Stefan Bentvelsen, Viggo Poulsen, Fabrice Harari

Multiple insert statements via one HFSQL statement

Startbeitrag von James Covington am 04.06.2015 20: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)

Antworten:

Hi James,

afaik this works from WinDev Version 20, not in earlier versions.

von Stefan Bentvelsen - am 05.06.2015 10:02
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?

von James Covington - am 05.06.2015 14:33
Hi James,

I don't know if this feature is included in the pre-release.

von Stefan Bentvelsen - am 05.06.2015 14:41
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.'

Actually it does support automatic identifiers - you will need to declare all other fields to be updated enclosed in parenthesis.....

sqltest is string = [
INSERT INTO PublicHoliday
(HolidayDate,Description)
VALUES
('20151225','Christmas Day'),
('20151226','Boxing Day')
)
]



von DerekT - am 05.06.2015 16:32
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).

von James Covington - am 05.06.2015 22:01
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

von Viggo Poulsen - am 06.06.2015 09:12
Thanks for the reply Viggo!

Unfortunately no, I still get the same error.

von James Covington - am 08.06.2015 13:03
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

von DerekT - am 08.06.2015 14:17
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?

von James Covington - am 08.06.2015 15:44
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.

von DerekT - am 08.06.2015 17:10
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

von kingdr - am 08.06.2015 17:56
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

von Fabrice Harari - am 08.06.2015 18:00
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

von kingdr - am 08.06.2015 18:26
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)

von James Covington - am 08.06.2015 18:39
Derek, did you test successfully on a local database or client/server? I can get this style of multiple insert to work in the control center on a local database, but not client/server. I wonder if I should send in a bug report?

von Curtis - am 31.07.2015 20:11
Curtis
My test (see http://27130.foren.mysnip.de/read.php?27131,527346,538839#msg-538839)

Tested once on my dev machine and once on a remote server over VPN.
Both times was against HFSQL ClientServer.

You have not provided your sql statement so it is impossible to comment further.

von DerekT - am 01.08.2015 13:48
INSERT INTO ExampleTable(Amount,LineItem) VALUES
(55,1),
(44,2),
(33,3),
(22,4)

Error is "Unable to initialize the query. (55,1) function not managed". Same problem as the original poster although I can get it to work on a local database. I'm having to use the 'UNION ALL' approach or if my data is basically all the same I can use an IN() operator.

von Curtis - am 02.08.2015 21:46
Curtis
I have just tested this...

Added the table with Amount as currency and LineItem as int

Ran you (cust and pasted) sql statement code as ...

MyQuery is Data Source
sqltest is string = [
INSERT INTO ExampleTable(Amount,LineItem) VALUES
(55,1),
(44,2),
(33,3),
(22,4)
]
HExecuteSQLQuery(MyQuery,hModifyFile,sqltest)



Query ran successfully - 4 records were inserted.

Test with WD20, running on Win7Prof, 64bit Machine.
HF Client Server running on same machine.

Which version of WD are you using?
Have you applied the latest updates?

von DerekT - am 03.08.2015 09:17
Fully updated to 01A200066r. WD20, running on Win7Prof or Win7Ultimate 64 bit. HF Client Server running on same machine. Could the server be running an older version of HFSQL C/S?

von Curtis - am 03.08.2015 13:31
Curtis
I guess it could, just checked my 'About CCHF'...

Module CC200HF
Module Version 20.0.103.0
Internal Version 01A200066r

In the general tab Manta64 gas (along with all other modules) an internal version of 90A170078g

von DerekT - am 03.08.2015 14:10
Mine is all the same.

Where would I find this general tab Manta64?

von Curtis - am 03.08.2015 15:46
Curtis

Open the HFSQL Control Centre.
Double click on the server you wish to check.
8 tabs for this server are displayed - General is the top one.

von DerekT - am 03.08.2015 17:12
Thanks! Probably found the problem then. My internal version is 90A190044d, version 19.0.3.0.

Now I just need to figure out to update all these modules, at least Manta64.

von Curtis - am 03.08.2015 18:12
Zur Information:
MySnip.de hat keinen Einfluss auf die Inhalte der Beiträge. Bitte kontaktieren Sie den Administrator des Forums bei Problemen oder Löschforderungen über die Kontaktseite.
Falls die Kontaktaufnahme mit dem Administrator des Forums fehlschlägt, kontaktieren Sie uns bitte über die in unserem Impressum angegebenen Daten.