DB Toolbox List tables - does not work properly

[pawel]

Hello All

I have two databases: one "c:\a\aa.mdb" contains some tables inside
second "c:\a\bb.mdb" contains no table.

When I run the list tables command from DB toolbox SEPARATELLY the tables are listed in proper way, i.e. one has tables, second does not.

But when I build a vi which returns the table list, and call it twice the result is wrong. Both database seems to have all tables, even though the second does not contain any tables.

I attachment you will find the packed example.

Two databases with udl files (change if unpacked to different directory than c:\a\).

The vi "two vi tables.vi" shows the problem. It calls "find tables.vi" to return the tables in database.

You can see th
at when DB List tables is called in the main vi it works fine. But when it is called two times inside the loop it returns false results.

Can anybody comment on this.

For those who are afraid of unpacking zip files I enclose pictures.

thank you in advance
Pawel

[craigherdman]

Pawel,

Thank you for your inquiry. After conducting some research I have uncovered a work-around solution to your problem. This should be considered a temporary fix as more research needs to be conducted on my end to get to the root cause of the problem.

In order to get your code up and running you can do the following.

You will need to edit the �DB Tools Open Schema.vi� to initialize an input value. To do this:
1. Open your �find tables.vi� and double click on the �DB Tools List Tables.vi�. This will display the �DB Tools List Tables.vi� front panel.
2. Show the block diagram of �DB Tools List Tables.vi� and double click on the �DB Tools Open Schema.vi�. This will display the �DB Tools Open Schema.vi� front panel.
3. Show the block diagram
of the �DB Tools Open Schema.vi�. Locate the �Code Interface Node� in the center of the diagram. Right click on the second input node from the bottom of the �Code Interface Node� and select create constant. (see attached gif)

After making these changes run your program and check your results. Again, this is a temporary fix and I am investigating the issue further. If this fix fails to solve your problem please post back with further detail. Good luck!

Craig H.
National Instruments

[pawel]

Dear Craig

Thank you for your answer. It is a littel bit a pitty that I got it so late since I already made myt own work arround the problem. Insted of modifying the "DB Tools Open Schema.vi" I call the "DB Tools List Tables.vi" dynamically setting the desired controls and reading them back "on fly". When I do this, there is no more problems with the list tables.
Probably my solution is much slower than yours, but at least I do not need to change internal private methods of DB class. That is especially important when the new upgrade will come and overwrite the old one, I guess.
But you already said it shell be ment to be temporary solution.

Since we are on the database DB tools now, another thing came to me today. Namelly, I need to wait 3-5 secs aft
er I create 7 tables (quite big) in the database (Jet 4 engine). My machine Dell, 1.5GHz., LV7, W2k.
If I do not wait, the "DB Tools execute query (Conn Execute.vi)" returns error that the table does not exist. On the other hand when I wait 5 secs after I create tables everything is fine.

It is OK to wait 5 secs after the table is created, but this time should be forced automatically by the vi, not by the user debugging, I guess.
I would send you the code, but it is connected to too much other stuff, which I am not likelly to send.

Thank you again for you answer.
kind regards

Pawel

[craigherdman]

Pawel,

I apologize for the delayed response to your first question. I am currently working on the problem in order to determine a more permanent solution. These forums are a very important resource for not only helping our customers but also for improving our products!

With regards to your other question I believe the problem is one of data flow programming. Each table creation will take a certain amount of time to complete depending on the size and nature of the rows and columns being inserted. Unless you programmatically control the data flow, the �DB Tools Execute Query.vi� does not necessarily know when each table has been created. Since control is handed off to the database program, the �DB Tools Create Table vi� is forced to wait
until the database program returns control.

To programmatically control the flow of data you would need to use either a case structure with a while loop (suggested) or a sequence structure. These structures are critical in cases where execution of a previous section of code must be completed before subsequent code portions are to execute. I have attached an exaple that may prove useful.

The wait method is actually a good way to time data flow but in this case may not be the best solution if you want your table creations to be dynamic over time (you may be forced to change the wait for larger table creations, for instance). If you know that your table creations will never change then using a wait method may be an acceptable solution.

I hope this information helps out some. If not please post back with some sample code or more detail on your issue. Good Luck!

Craig H.
National Instruments

[pawel]

Dear Craig
Thank you for your help. In my program (and problem) I created couple of empty tables in the same way as you proposed in the "Create Only" case in envlosed example. This should create only empty tables and there is no time spend on transferring the data. And this is which turned up to take more time then the vi execution.
I believe that it can be caused by several reasons. The DB tools works on CINs, on top of it, communicated with SQL engines (I use JET 4). It could be like the engine release control to CINS but still is dooing some job with OS to create a table, reserve resources etc. If the system is loaded, which could be my case, the table creation takes long time.

The idea with while loop gives me another idea to use
it while querring the database. Immediatelly after the creation I can build the while loop which continues as long as the error occurs and stopes after the No error or timeout. That could solve my problems, I guess.

kind regards
Pawel