Is there some approach for executing many sql (at least 2 sql) once?

[威海的风]

Hi Mike,

I use mySql 5.0.22.Now I hava a vi that do the insert/update/delete sql in my system.I give the vi a sql,then it executes.The function like this is required frequently.

[威海的风]

The update sql  always hava two or more sentences in transaction.

[威海的风]

The problem is solved.I write a procedure that contain sql block.But I confuse why not support a vi that can execute more sql at one time.

[smercurio_fc]

Your question doesn't really make any sense, since that has nothing to do with LabVIEW. In order to connect to a database you need to have some sort of driver for the language to be able to use. That's all that LabVIEW is doing. To ask that there should be a VI to "support multiple SQL statements" is no different than asking that there should be a Java function or a C function to support multiple SQL statements. There isn't because it makes no sense for there to be one.

[Dennis_Knutson]

Have you tried the simple method of using 2 SQL Execute in parallel? I don't remember if they are reentrant but you can make them so if they aren't already.

[威海的风]

Thank you very much!

[Matthew_Kelton]

---

smercurio_fc wrote:
Your question doesn't really make any sense, since that has nothing to do with LabVIEW. In order to connect to a database you need to have some sort of driver for the language to be able to use. That's all that LabVIEW is doing. To ask that there should be a VI to "support multiple SQL statements" is no different than asking that there should be a Java function or a C function to support multiple SQL statements. There isn't because it makes no sense for there to be one.

---

I believe he was asking how to put multiple statements into one SQL string.  Using the BLOCK statement will allow you to do this in NI's DB toolkit (and apparently LabSQL).

This can be extremely useful if you have a lot of SQL transactions to make.  You can find many sources online indicating a huge performance increase when using this.  I personally saw a dramatic decrease in time it took to make modifications to a database by using this method.  If you are not doing a lot of transactions, it may not be necessary.  There are also some limitations on what you can do with the BLOCK statement.

You are right that it is a DB driver supported feature, but it is possible to execute multiple statements in one call to the database.

[smercurio_fc]

I know about executing multiple statements in one call to a database. Actually, the user was already making a multiple statement call in his reply #6, so I couldn't understand what they were asking for, exactly. To me it sounded like the user was expecting there to be a VI to "magically" do this just because it was LabVIEW, and my point was "why should there be?".

[TRAVO]

Hello

[TRAVO]

I think that I have a similar situation...

If I run a simple SQL statement with one SELECT from one table, all is well. However, it seems that when I run a query that has multiple statements such as create temp table, inserts, query from #tmp table joins etc.. I see no results returned back to LV. Not sure what the deal is. I have access to a remote server with multiple DB's and run queries daily so I can create the query in management studio and then try it via LV once the query proves out in manageent studio so I know that the code is legit. Is there something specific with LV DB kit that only allows one select statement? should I try to wrap it all up into a variable and then execute it such as

declare @sql varchar(max)

set @sql = ' code block'

while @@fetch_status = 0

begin

execute (@sql)

I know that a stored proc may be useful as well, but I dont have the keys to the kingdom for creating such proc's, whats the work around?

Thanks all,

Travis