Welcome Guest Search | Active Topics | Log In | Register

VistaDB User Forums » VistaDB 3.x » General Questions » Can you get column data types from Database Schema system table?
Can you get column data types from Database Schema system table? Options
Previous Topic · Next Topic
sethspearman
#1 Posted : Wednesday, July 01, 2009 11:34:04 PM
Rank: Member

Groups: Member

Joined: 3/3/2007
Posts: 8
Points: 9
Is there any published data on the Database Schema system table. Specifically that describes the columns.

At the moment I am specifically looking for a way to retrieve the col type of each col to verify existence for a column in a particular table.
Back to top
 Report Abusive | Report SPAM
davidmccallum
#2 Posted : Thursday, July 02, 2009 12:58:55 AM
Rank: Team VistaDB

Groups: Member, Team VistaDB

Joined: 8/13/2006
Posts: 606
Points: 2,577
Location: Edinburgh, Scotland
See Why self reference and join [database schema]?
David McCallum

www.actual-systems.com

dmacuk.homeserver.com
Back to top
WWW  Report Abusive | Report SPAM
js_vistadb
#3 Posted : Thursday, July 02, 2009 11:09:52 AM
Rank: VistaDB Staff

Groups: Administration, VistaDB Staff

Joined: 8/13/2006
Posts: 3,633
Points: 5,124
Location: Mount Dora, Florida
Look up information on GetSchema() that is how you are supposed to get table and column information from an ADO.NET provider.

You can get information from [database schema] but it is not the recommended way, and you cannot self join on that virtual table.

The help has information about that as well.

Jason Short
Back to top
WWW BLOG  Report Abusive | Report SPAM
sethspearman
#4 Posted : Thursday, July 02, 2009 11:46:05 AM
Rank: Member

Groups: Member

Joined: 3/3/2007
Posts: 8
Points: 9
Jason and David,

In asking the question WHY are people asking for this I thought I might be able to offer some insight. Please keep in mind I am not an expert and what I say should not be considered the "final answer" nor am I arguing that what I am about to say it the only way it could be done.

At the shop I work at (and for the commercial app I am writing) TSQL script gives us a "scripty" way to create the database and (more importantly) update the database per version release of the software without having to hard code those changes into our application or assembly.

We create a TSQL script for each version release of our software. We embed that script into the assembly as a text-based resource file. That combined with our custom .net updater code will look at the current version of the application and automatically apply and iterate through each of these resource files and automatically apply them to the database by executing the scripts against the database and then storing in a configuration table/field the last applied update. So that update code only runs when there is a mismatch between current version of the assembly and latest update value stored in the database.

One of the business requirements of those scripts is that you have to be able to run them more than once without duping rows or causing DDL errors.

So they have a lot of If NOT EXIST (SELECT 1 FROM SOMETABLE WHERE...) in it to prevent duping the rows. But to prevent the DML errors we have to do stuff like IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'mytable' AND COLUMN_NAME = 'mycolumn') ALTER TABLE...

The nice thing about using t-sql script to do this is you can nicely combine the ddl changes with the data modifications. So you don't update this column until the column is added but you also need to be able to verify that the column exists before running the dml changes.

I suppose there is a pure ado.net way to accomplish this but our tsql way has worked very well for us and we would like to be able to do something similar using VistaDB.

Any thoughts?

Seth B Spearman






js_vistadb wrote:
Look up information on GetSchema() that is how you are supposed to get table and column information from an ADO.NET provider.

You can get information from [database schema] but it is not the recommended way, and you cannot self join on that virtual table.

The help has information about that as well.
Back to top
 Report Abusive | Report SPAM
js_vistadb
#5 Posted : Thursday, July 02, 2009 3:52:04 PM
Rank: VistaDB Staff

Groups: Administration, VistaDB Staff

Joined: 8/13/2006
Posts: 3,633
Points: 5,124
Location: Mount Dora, Florida
INFORMATION_SCHEMA is totally different - and we will support it in VistaDB 4. That was built for the purpose of schema query. The question I had was about [database schema] (which was NOT intended for that purpose).

The thought at the time of building the 3.x engine was that users would write Applications to do their schema upgrades. You just can't match the power of what you can do in CLR code vs a script. We also didn't have TSQL Procs at the time so you really had to write CLR code if you wanted any type of complex logic operations.

Schema modification and versioning are much more difficult in a desktop app due to the local nature of the files. The user could have stuck with a really old version for some esoteric reason and now need to run through a lot of steps to get up to current during upgrades. I understand that need.

Look in the help file for "database schema" and you will see the pages that talk about what the fields mean. But remember you cannot self reference or join that schema - it is a virtual table not a physical one. You sometimes have to do more than one operation to get the level you want for something like this.

From the 3.6 help file:

Code:
You can use multiple statements to achieve the
same results a self referencing query,
but we don't recommend this as a best practice. 
It is included here for completeness only.

DECLARE @fkref as NCHAR(50);

SET @fkref = SELECT objectId from [database schema] WHERE 
typeid = 1 and name = 'FileLinks';

SELECT EXISTS( SELECT name from [database schema] WHERE 
typeid = 3 and name = 'FileLinkId' AND
foreignReference = @fkref );


It's not perfect, but it is the correct approach for 3.x.

Jason Short
Back to top
WWW BLOG  Report Abusive | Report SPAM
Users browsing this topic
Guest
VistaDB User Forums » VistaDB 3.x » General Questions » Can you get column data types from Database Schema system table?
Forum Jump  
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.

YAFPro Theme Created by Jaben Cargman (Tiny Gecko)
Powered by YAF 1.9.3 | YAF © 2003-2009, Yet Another Forum.NET
This page was generated in 0.094 seconds.