Fosters

how do i do the sql equivalent of 'desc employee_table;' in an ado command/recordset (to return the table definition into VB)?

sorry about the geeky question

BigGT3Fan

Fosters,

I don't know how to to directly send a DESC command to the DB and get back the results as a text string, sounds like that would not be possible.

You need to use the ADOX library, which supports extended operations & DDL through a set of ADO objects.

This will allow you to get back an object model containing all the tables in your schema, all their columns, datatypes etc.

You can then traverse this object structure to read the schema definitions generically.

HTH,




Alex

Fosters

I cheated!

Function GetDescForTable() As String()
Dim TD() As String
Dim RS As New ADODB.Recordset
Dim sSQL As String
sSQL = "select column_name, data_type " & _
"from dba_tab_columns where owner = 'P11D' " & _
"and table_name = '" & UCase(Form1.cmbTables.Text) & "'"
RS.Open sSQL, sCN, adOpenStatic, adLockReadOnly
ReDim TD(2, 0)
While Not RS.EOF
ReDim Preserve TD(2, UBound(TD, 2) + 1)
TD(0, UBound(TD, 2) - 1) = RS!column_name
TD(1, UBound(TD, 2) - 1) = RS!data_type
RS.MoveNext
Wend
RS.Close
GetDescForTable = TD
End Function

DazV

If you're using ASP, just whack it into the SQL string...

Dim myrecordset
Set myrecordset=server.createobject("ADODB.Recordset")
sqltext = "SELECT forename, surname FROM customers ORDER BY surname DESC;"
myrecordset.Open sqltext, "DSN=whatever"
etc...

BigGT3Fan

Fosters - yeah, that will work. I guess ADOX is generic and gives you DDL and schema info for whatever db you are using, whereas your method is Oracle specific.




Alex