Schema and OdbcConnection
-
How do I get the database schema once I have set up an OdbcConnection? Primarily I need a list of tables and columns in the database. My App needs to be able to connect to any ODBC datasource and work out what tables and columns it contains. TIA
I have started to use the ODBC calls and P/Invoke as the Odbc stuff is .Net is sorely lacking. Still can't get it work though.
IntPtr henv; short ret; // Allocate environment handle ret = SQLAllocHandle(SQL_HANDLE_ENV, (IntPtr)SQL_NULL_HANDLE, out henv); if(ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO) { ret = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,(IntPtr)SQL_OV_ODBC3, 0); if(ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO) { IntPtr hdbc; ret = SQLAllocHandle(SQL_HANDLE_DBC, henv, out hdbc); if(ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO) { // ret = SQLConnect(hdbc, dsn, SQL_NTS, null, SQL_NTS, null, SQL_NTS); if(ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO) { IntPtr hstmt; ret = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, out hstmt); if(ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO) { ret = SQLTables(hstmt, null, SQL_NTS, null, SQL_NTS, null, SQL_NTS, null, SQL_NTS); if(ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO) { StringBuilder tableName = new StringBuilder(256); int tableNameLen; ret = SQLBindCol(hstmt, 2, 1, tableName, 256, out tableNameLen); if(ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO) { while(SQLFetch(hstmt) == SQL_SUCCESS) { Console.WriteLine(tableName + ":" + tableNameLen.ToString()); } } } ret = SQLFreeHandle(SQL_HANDLE_STMT, hstmt); } ret = SQLDisconnect(hdbc); } // ret = SQLFreeHandle(SQL_HANDLE_DBC, hdbc); } } ret = SQLFreeHandle(SQL_HANDLE_ENV, henv); }The SQLFetch will be called as many times as there are tables in the dsn but the dsn is empty and tableNameLen is -1. Rugby League: The Greatest Game Of All.