How can I change DB schema without having to modify VIs?

[iCat]

1) There is a file that defines DB schema.

Since the DB schema is so simple, it's expressed in an excel spreadsheet file.

LabVIEW reads the list of tables from the schema file.

There are such tables as "a", "b", "c", and "d". It's saved as an array of strings.

2) Currently, A.vi reads data from table "a".

"a" is a string literal in A.vi.

3) You decided to change the name of the table "a" to "hell".

You changed "a" to "hell" in the db schema file.

The Question : Is it possible to change the table name without having to modify A.vi by referring it to the list of tables in the db schema file?

[Mark_Yedinak]

If you are building your SQL statements in LabVIEW than you can use a lookup table to get the correct information. However, I would strongly recommend that you do all of your DB access using stored procedures. They are much more flexible since you use then to define the API between your applications and the DB. Your application do not need to have any knowledge of how the DB is architected or what it's schema is. You only need to know the stored procedures. By doing so you can modify the schema as much as you like and will not impact your application provided you keep the stored procedures calls the same. In addition, stored procedures are more efficient than straight SQL.

[iCat]

---

@Mark_Yedinak wrote:

If you are building your SQL statements in LabVIEW than you can use a lookup table to get the correct information. However, I would strongly recommend that you do all of your DB access using stored procedures. They are much more flexible since you use then to define the API between your applications and the DB. Your application do not need to have any knowledge of how the DB is architected or what it's schema is. You only need to know the stored procedures. By doing so you can modify the schema as much as you like and will not impact your application provided you keep the stored procedures calls the same. In addition, stored procedures are more efficient than straight SQL.

---

Since I've been making a set of modules for lots of DBMSs, the cost of stored procedures can't be justified.

[Mark_Yedinak]

I find that difficult to believe. Ask any DB developer and they will tell you the best way to access a DB programatically is via stored procedures. Embedding SQL in an application is both inefficient and difficult to maintain. Enbedding the SQL in your code is not the best choice. If nothing else it forces the users of the DB to completely understand the DB schema. Stored procedures hide those details and provide a more smplified and logical interface into the DB.

The answer to your original question is yes, DBs generally have a metatable which conatins the name and information for tables and schemas in the database. The specifics for accessing that infomration is DB specific. That is Oracle has one method, MySQL has another and so on.