"Execute Query VI" multiple updates with one query

[PNB]

Hello,

I am doing mathematical opratios on traces that are stored in a database. I would like to write back the resuts of the operations into the database via an update query, e.g.:

UPDATE [Bond_CSV_Data] SET [PrID1_P1]=66325.500000 WHERE [Bond-Group-ID]="Cu_300_1st_DTS" AND [Module]=2 AND [Wire]=4 AND [Bond-Foot]=1 AND [Lot]="V2011-S21"

Since I do a lot of this operations, I would like to update several entries at once, e.g.:

UPDATE [Bond_CSV_Data] SET [PrID1_P1]=66325.500000 WHERE [Bond-Group-ID]="Cu_300_1st_DTS" AND [Module]=2 AND [Wire]=4 AND [Bond-Foot]=1 AND [Lot]="V2011-S21"
UPDATE [Bond_CSV_Data] SET [PrID1_P1]=65986.500000 WHERE [Bond-Group-ID]="Cu_300_1st_DTS" AND [Module]=2 AND [Wire]=5 AND [Bond-Foot]=1 AND [Lot]="V2011-S21"
UPDATE [Bond_CSV_Data] SET [PrID1_P1]=67500.000000 WHERE [Bond-Group-ID]="Cu_300_1st_DTS" AND [Module]=2 AND [Wire]=6 AND [Bond-Foot]=1 AND [Lot]="V2011-S21"
UPDATE [Bond_CSV_Data] SET [PrID1_P1]=67217.500000 WHERE [Bond-Group-ID]="Cu_300_1st_DTS" AND [Module]=2 AND [Wire]=7 AND [Bond-Foot]=1 AND [Lot]="V2011-S21"

However, when I do that I resceive an error message, it seems that I have not chosen the correct command seperator. In all SQL Tutorials it has been done wth a simple "end of line". This seems not to work here. The single query works however.

It also works, when I do it one by one in a FOR-Loop, however this takes ages.

Is there a simple way to make severeal Update queryes (Several hundred) at once?

If not, I will update the already existing database over the holidays, it will surely take a while and thn make th operation always directly when i upload ne data. This way I wll no have to update new data.

Kind Regards

Patryk

[Emna20]

Hello ,

for your application you could use the "DB tools execute query " , write the SQL query to update your multiple result .

best Regard.

[PNB]

Hello Emna,

thanks for the fast reply, but I am actually using the "DB tools execute query ". However it seems not to work with multiple statements at once, it works perfectly just with just one Update statement.

[DavidBoyd]

You didn't mention what kind of database backend you're using, but in general it's faster, and definitely more secure, to create a stored procedure (once) within your database, then call that using the LabVIEW database toolkit's support for invoking the stored procedure (in other contexts called "parameterized query").  Then you're only setting up a parameter list (which takes place on your client) and passing the params to the server, where it's already compiled and optimized the procedure (in this case an INSERT).

Sending SQL commands on-the-fly (what you're doing presently), by contrast, requires the server to interpret the text, convert the numbers from text to float, etc., all of which is comparatively slow, can't really be optimized, and in the larger sense completely insecure, since you're permitting the server to execute any SQL text anybody sends to it.

Dave

[PNB]

Hello Dave,

thanks a lot for your reply. I am actually pretty new to databases in general and am not aware of many things probably. I didnt know for example, that the stored procedure actually does somethng on the server. I thought it is just another way to write SQL queries.

Thanks for clearing that up.

Patryk

[Yamaeda]

It was a long time since I touched SQL, but I remember using ; as end of line. It might be DB-dependant though.

[PNB]

I am using the "Microsoft JET Database Engine" I hope this is the backend.

So i tried to create a stored procedure. MY Query looks like this:

CREATE PROCEDURE UpdatePROSI @param TEXT, @val DOUBLE, @bg_id TEXT, @dule INT, @wire INT, @bf INT, @lot TEXT
AS
UPDATE [Bond_CSV_Data] SET [@param]= @value WHERE [Bond-Group-ID]=@bg_id AND [Module]=@dule AND [Wire]=@wire AND [Bond-Foot]=@bf AND [Lot]=@lot

However when I run this query I get this error message:

NI_Database_API.lvlib:Conn Execute.vi->Simple_Query.vi<ERR>ADO Error: 0x80004005
Exception occured in Microsoft JET Database Engine: Ungültige SQL-Syntax: Token erwartet: AS. in NI_Database_API.lvlib:Conn Execute.vi->Simple_Query.vi

In german it says, that he expects a "AS" token. But the AS token is in the query. What is wrong?

[DavidBoyd]

The text of your CREATE PROCEDURE has both @val in its formal parameter declaration and @value in its body.  Could this be the source of your error?

Also, please clarify - were you trying to execute the CREATE PROCEDURE from LabVIEW, or are you receiving the error when trying to call the procedure?

As a rule I don't try to execute statements which edit the structure of the database (in this case, create a procedure) using the toolkit, since these are one-off operations.  Instead, use the tools provided by the database for those operations - SQL Server Management Studio for SQL Server, or MS Access for Access/Jet.

The only thing you should be coding in LabVIEW is a call to the stored procedure using the "DB Tools Create Parameterized Query" (the name of this VI is unfortunate, it creates the references needed to call a procedure which already exists).  Then you need to call "DB Tools Set Parameter Value" for each parameter assignment, then "DB Tools Execute Query", and finally the "Fetch" and "Free" methods.

Sounds complex, but once you put these VIs together, it works beautifully.

Dave