Wait for query to finish executing before running next query

[rickbytes]

Hello,

Is there a way to make my vb code to wait for a mysql query to finish executing before trying to run a new query?

For example: Is there a way I can run a CREATE TEMPORARY TABLE _mytable AS SELECT etc, etc, etc, wait until the temp table is created, then run SELECT * FROM _mytable?

I'm using VB 08 Pro and MySql 5.1.


Thanks,
Rick

[rpicilli]

There is some ways.

I think the easy is to do the following

First create you TmpTable

Secong put your code to select data from the just create table into a Try Catch

Look for the error. If the error was something like Table not found or Table Locked or anything that you can check stay there waiting to your table became available.

I hope this help you

Rpicilli

[Plater]

What is your code doing?
My code always blocks until the completion of query.

[Telinstryata]

Don't know for sure if this will work, but in T-SQL you can put a GO between statements to make sure that previous statements finish before starting the next statements. I don't know if you can use GO in a query like this (because I've never tried) but try something like the following...

"CREATE STATEMENT; GO; SELECT STATEMENT;"

[Curtis Rutland]

I don't think that the GO statements will work in the command objects.

It depends on how you write your queries. Some sample code of what you're doing would help. Like Plater says, mine hangs until my queries are finished, but if you're doing them in a different thread, then you have different issues.

[balabaster]

In my experience, when .NET triggers a command it waits for some response from the server and if it receives something it moves on, otherwise it carries on waiting.

Alas, any response triggers .NET to stop waiting...

An easy trick to get around this is to add Set NoCount On to the start of each procedure, manually keep track of the changes inside your procedure and then Set NoCount Off at the end before returning the manually calculated change count.

This will prevent SQL Server from providing false starts...

If you don't do this and you have a bunch of inserts at the start of your query, it will respond with a bunch "1 row updated" statements - the first of which will cause your .NET code to continue on its merry way thinking that the query has come to its conclusion.