SQL DESC from ADO.Net?

[ThatRickGuy]

Hey Guys,
I'm having a heck of a time trying to get some dynamic code working. I am pulling the structure of an Oracle table out of the database and generating text based on it. The only problem is, when I pull back an empty table an look at the columns' data type, Integer columns are showing up as Decimal.

So a table like this:

CREATE TABLE SLIDE
(
  SLIDE_ID         INTEGER,
  PRESENTATION_ID  INTEGER,
  AUDIO_ID         INTEGER,
  SEQUENCE         INTEGER
)

When I do this:

    Dim oleConn As New Data.OleDb.OleDbConnection("Provider=msdaora;Data Source=" & Cred.Environment & ";User Id=" & Cred.UserName & ";Password=" & Cred.Password & ";")

    Dim oleCmd As New Data.OleDb.OleDbCommand("SELECT * FROM " & Me.cmbNamespace.Text & "." & Me.cmbTable.Text & " WHERE 1=0", oleConn)

    oleConn.Open()
    Dim dt As New DataTable
    Dim da As New OleDb.OleDbDataAdapter(oleCmd)
    da.Fill(dt)

    Debug.WriteLine(dt.Columns(0).DataType.ToString)

Even though the fields are defined as an Integer in the database, .Net is defining the columns Decimal. While this isn't a huge functional problem, it is killing my documentation.

Anyone know of a way to either get that code to return the correct Oracle datatype, or to get the DESC command to work, even if it just returns a big string?

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[AlexCuse]

I take it selecting columns by name in this is not an option.

Are these tables all empty, or are you forcing the empty result set by using WHERE 1=0? Maybe you could take top 1 * (if you have actual data), and see if it gets the right data type that way? If it doesn't, you'd at least have the data to make an educated guess. But this could all be moot if the below works:

As far as the DESC command is concerned, I am not really familiar with that command (SQL Server guy...) but this looks useful:

http://webmag.mvps.org/?p=67

Basically you want to mimic the DESC command by querying oracle's system tables. I have to do this in SQL server now ant then, if your app's database permissions are set up properly for it it shouldn't be too hard.

Hope it helps,

Alex

[small]----signature below----[/small]
With all due respect, Don Bot, I don't think we should rely on an accident happening. Let's kill him ourselves.

Ignorance of certain subjects is a great part of wisdom

 

[ThatRickGuy]

Unfortunately, the tables are all likely to be empty when this code is running. My initial attempt at pulling back an empty datatable was to just avoid extra load on the server/network when all I wanted was the meta data.

The link is pointing to what I am currently trying.

SELECT column_name,
       concat(concat(concat(data_type,'('),data_length),')') 
  FROM user_tab_columns 
 WHERE table_name='Answer';

Unfortunately, I'm getting no rows returned from this. I've posted on that subject in the Oracle forums here: thread1177-1410745

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[chrissie1]

Did you try using ODP.NET?
Me I use oradirect with good results.

It seems to me that you are having oledb problems. in that oledb is not correcttly interpreting the result so a better driver should solve that.


DESC? do you mean ORDER BY ... DESC?

Christiaan Baes
Belgium

My Blog
"In a system where you can define a factor as part of a third factor, you need another layer to check the main layer in case the second layer is not the base unit." - jrbarnett

 

[ThatRickGuy]

DESC is short for Describe. It is an Oracle specific command that returns a string description of the table.

The problem I was having appears to be because the table I was looking for wasn't on the Users tablespace. Changing to the All_TAB_COLS view fixed it right up!

SELECT * FROM SYS.ALL_TAB_COLS WHERE Table_Name = 'ANSWER';

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.