 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
VB Forum / Database Access / October 2003The best way to retrieve table schema info?
I am trying to find the best way to retrieve a table schema info, mostly column attributes, using ADO. The method I am incorporating right now is the following sql statement: SELECT * FROM HISTORY WHERE 1=0 And I extract the necessary info from the recordset's Fields collection. I wonder how much of an overhead the previous statement incurs on the system. I would guess minimal... I've used ADO Connection.OpenSchema but the information returned from the method is different from what I get through the Fields collection. The former seems to be from the provider itself (OLE DB level). Other than that, using SET FMTONLY or going through sysobjects (or any of the sys* tables directly) are probably DB specific, not that it's out of the question if these will provide significant performance advantage. SQLDMO may be another. So what is the best method performance-wise and implementation-wise(ease of)? Thanks for letting me pick your brains! Hi Jiho, I would say using OpenSchema is the most universal way. Yes, it is relies on OLEDB provider's capabilities to return specific information, but this is the only way, which is database-independent.  SignatureVal Mazur Microsoft MVP Check Virus Alert, stay updated http://www.microsoft.com/security/incident/blast.asp > I am trying to find the best way to retrieve a table schema info, mostly > column attributes, using ADO. [quoted text clipped - 19 lines] > of)? > Thanks for letting me pick your brains! > I would say using OpenSchema is the most universal way. Yes, it is relies on > OLEDB provider's capabilities to return specific information, but this is > the only way, which is database-independent. Would "SELECT * FROM HISTORY WHERE PRIKEY = -1" be database-independent? Knowing that your prikey is always > 0. If not why? cheers Stu Assuming that you are able to obtain all of the info you require from the fields collection, this looks to be an acceptable solution that is database independent. I had used this approach before I learned of the OpenSchema method. -- Al Reid "It ain't what you don't know that gets you into trouble. It's what you know for sure that just ain't so." --- Mark Twain > > I would say using OpenSchema is the most universal way. Yes, it is relies > on [quoted text clipped - 6 lines] > cheers > Stu Hi Stu, I would say using of WHERE 1=0 is more reliable way, since it will always return False. But using that statement does not return some information for you, like if this is a primary key field etc. OpenSchema method provides more information for you. You solution will probably work in your case, so it is up your which way to go SignatureVal Mazur Microsoft MVP Check Virus Alert, stay updated http://www.microsoft.com/security/incident/blast.asp > > I would say using OpenSchema is the most universal way. Yes, it is relies > on [quoted text clipped - 6 lines] > cheers > Stu > I would say using of WHERE 1=0 is more reliable way, since it will always > return False. But using that statement does not return some information for > you, like if this is a primary key field etc. OpenSchema method provides > more information for you. You solution will probably work in your case, so > it is up your which way to go I use this technique to get the datastructure before i do an insert using ado. If I use the open schema method I would need to build the recordset too. So there is more programming and more chance of the insert failing (due to errors in mapping etc). Am I missing something or is the select that returns 0 records much easier to use for the purpose of doing inserts? Cheers Stu i use openschema to feed me a list of tables, teh user clicks on the table and then still using openschema i pull teh list of fields, the user clicks a field and i can tell what oepratiosn to allow and what datatype, give them a list of operations to click and a textbox to supply a value. turn that into a sql statement and store it as part of a where clause, repeat the process over and until teh user clicks report. i guess it really depends, do you need just column info, or table and column? Woody any sugestion or comment made by me should be examined first for validity and appropriateness before assuming i have any idea at all what the heck i am talking about. I am not responsible for anything you may see with my name attached to it, i think. >i use openschema to feed me a list of tables, teh user clicks on the >table and then still using openschema i pull teh list of fields, Can you pull off the fields for a table or do you have to pull off all fields and filter those for the table you want? SignatureJane Ransom in Lancaster. If you need to email me for any other reason, put ransoms at jandg dot demon dot co dot uk where you see ransom@deadspam.com i can call for just a list of tables, or a list of views supply a table name and get a list of fields for that table. did it as 2 activex. first is BoxTableSchema, set dbname, set whether want views or tables, whenuser clicks it does a dropdown and returns an alpha list teh items and returns teh name of teh selected item. next is BoxFieldSchema, set dbname, tablename, when user clicks on it they get an alpha list of fields in that when returns fieldname, field comments, field type, length, null allowed for teh selected field. Woody any sugestion or comment made by me should be examined first for validity and appropriateness before assuming i have any idea at all what the heck i am talking about. I am not responsible for anything you may see with my name attached to it, i think. Why wouldn't ADO fields collection be less universal? Wouldn't OpenSchema being dependant on a provider be less universal and more dependant on the specific provider? And when I am writing a code using the ADO objects, OpenSchema returned data types don't easily and necessarily map to the ADO field types. I don't know of any SQL-92 or 89 compliant database that would choke on SELECT * FROM HISTORY WHERE 1 = 0. What I am wondering is what the cost would be for each. - SELECT * FROM HISTORY WHERE 1=0 - SELECT * FROM HISTORY WHERE [PRIMARYKEY] IS NULL - OpenSchema ...etc. For example, my needs for the column information are getting just enough so that I can create command parameters for insert and update into a table. Fields collection provides data type and size. That's really all I need. What would I gain from using OpenSchema? What would I be missing out on, if not using OpenSchema? Thanks tremendously. > Hi Jiho, > [quoted text clipped - 28 lines] > > of)? > > Thanks for letting me pick your brains! |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.