Solved: ODBC tables and fields

1) What version of .NET do you have (2005 or 2003)?

2) Are you working with a DSN?

3) Does your DSN require authentication (user, password)?

Bob

asi

ASKER

2005
DSN
we config the authentication in the DSN / connection string.

Bob Learned

I haven't used this class in a while, so let me know if you can use it (it was converted from VB.NET code):

using System;
using System.Data.Odbc;
using System.Runtime.InteropServices;
using System.Text;
using System.Collections.Generic;
using System.Windows.Forms;

public class OdbcSchema
{
private const int MAX_BUFFER = 0x1000;
private static IntPtr m_hEnvironment;
private static IntPtr m_hDatabaseConnection;
private static IntPtr m_hStatement;
private static int m_intRetCode;
private const int SQL_ERROR = -1;
private const int SQL_INVALID_HANDLE = -2;
private const int SQL_NO_DATA_FOUND = 100;
private const int SQL_SUCCESS = 0;
private const int SQL_SUCCESS_WITH_INFO = 1;
private const int SQL_NTS = -3;
private const int SQL_SQLSTATE_SIZE = 5;
private const int SQL_MAX_MESSAGE_LENGTH = 512;
private const int SQL_MAX_DSN_LENGTH = 32;
private const int SQL_CHAR = 1;
private const int SQL_NUMERIC = 2;
private const int SQL_DECIMAL = 3;
private const int SQL_INTEGER = 4;
private const int SQL_SMALLINT = 5;
private const int SQL_FLOAT = 6;
private const int SQL_REAL = 7;
private const int SQL_DOUBLE = 8;
private const int SQL_VARCHAR = 12;
private const int SQL_TYPE_MIN = 1;
private const int SQL_TYPE_NULL = 0;
private const int SQL_TYPE_MAX = 12;
private const int SQL_C_CHAR = SQL_CHAR;
private const int SQL_C_LONG = SQL_INTEGER;
private const int SQL_C_int = SQL_SMALLINT;
private const int SQL_C_FLOAT = SQL_REAL;
private const int SQL_C_DOUBLE = SQL_DOUBLE;
private const int SQL_CLOSE = 0;
private const int SQL_DROP = 1;
private const int SQL_UNBIND = 2;
private const int SQL_RESET_PARAMS = 3;
private const int SQL_DRIVER_NOPROMPT = 0;
private const int SQL_DRIVER_COMPLETE = 1;
private const int SQL_DRIVER_PROMPT = 2;
private const int SQL_DRIVER_COMPLETE_REQUIRED = 3;

[DllImport("odbc32.dll", CharSet = CharSet.Ansi)]
private static extern int SQLError(IntPtr EnvironmentHandle, IntPtr ConnectionHandle, IntPtr StatementHandle, StringBuilder Sqlstate, ref int NativeError, StringBuilder MessageText, int BufferLength, ref int TextLength);

[DllImport("odbc32.dll")]
private static extern int SQLAllocEnv(ref IntPtr EnvironmentHandle);

[DllImport("odbc32.dll", CharSet = CharSet.Ansi)]
private static extern int SQLFreeEnv(IntPtr EnvironmentHandle);

[DllImport("odbc32.dll")]
private static extern int SQLAllocConnect(IntPtr EnvironmentHandle, ref IntPtr ConnectionHandle);

[DllImport("odbc32.dll", CharSet = CharSet.Ansi)]
public static extern int SQLDriverConnect(IntPtr hdbc, IntPtr hwnd, string szConnStrIn, int cbConnStrIn, StringBuilder szConnStrOut, int cbConnStrOutMax, ref int pcbConnStrOut, [MarshalAs(UnmanagedType.U2)]
            int fDriverCompletion);

[DllImport("odbc32.dll")]
private static extern int SQLAllocStmt(IntPtr ConnectionHandle, ref IntPtr StatementHandle);

[DllImport("odbc32.dll", CharSet = CharSet.Ansi)]
private static extern int SQLFreeStmt(IntPtr StatementHandle, [MarshalAs(UnmanagedType.U2)]
            int Option);

[DllImport("odbc32.dll", CharSet = CharSet.Ansi)]
private static extern int SQLDisconnect(IntPtr ConnectionHandle);

[DllImport("odbc32.dll", CharSet = CharSet.Ansi)]
private static extern int SQLFreeConnect(IntPtr ConnectionHandle);

[DllImport("odbc32.dll", CharSet = CharSet.Ansi)]
private static extern int SQLTables(IntPtr StatementHandle, string CatalogName,
int NameLength1, string SchemaName, int NameLength2, string TableName, int NameLength3, string TableType, int NameLength4);

[DllImport("odbc32.dll", CharSet = CharSet.Ansi)]
private static extern int SQLNumResultCols(IntPtr hstmt, ref int pccol);

[DllImport("odbc32.dll", CharSet = CharSet.Ansi)]
private static extern int SQLDescribeCol(IntPtr hstmt, int icol, ref string szColName,
int cbColNameMax, ref int pcbOutLen, ref int pfSqlType, ref int pcbColDef,
ref int pibScale, ref int pfNullable);

[DllImport("odbc32.dll")]
private static extern int SQLFetch(IntPtr StatementHandle);

[DllImport("odbc32.dll")]
private static extern int SQLGetData(IntPtr StatementHandle, [MarshalAs(UnmanagedType.U4)]
            int ColumnNumber, int TargetType, StringBuilder TargetValue, int BufferLength, ref int StrLen_or_Ind);

public static List<string> GetDSNTables(string dsnName)
{
return GetDSNTables(dsnName, string.Empty, string.Empty);
}

public static List<string> GetDSNTables(string dsnName, string userName, string userPassword)
{
List<string> listTables = new List<string>();
try
{
if (InitializeEnvironment())
{
string connectString = string.Format("DSN={0};user={1};pwd={2}", dsnName, userName, userPassword);
if (BeginConnection(connectString) == 0)
{
listTables = GetTables();
EndConnection();
}
ExitEnvironment();
}
return listTables;
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
return null;
}

private static string GetSQLErrorDesc(IntPtr hEnv, IntPtr hDbc, IntPtr hStmt, int intRetCode)
{
int Outlen = 0;
int Native = 0;
string strReturn = "";
const string NO_INFO = "Error - <No error information available>";
StringBuilder rgbValue1 = new StringBuilder(16);
StringBuilder rgbValue3 = new StringBuilder(MAX_BUFFER);
do
{
m_intRetCode = SQLError(hEnv, hDbc, hStmt, rgbValue1, ref Native, rgbValue3, MAX_BUFFER, ref Outlen);
if (m_intRetCode == SQL_SUCCESS | m_intRetCode == SQL_SUCCESS_WITH_INFO)
{
if (Outlen == 0)
{
strReturn = NO_INFO;
}
else
{
if (m_intRetCode == SQL_ERROR)
{
strReturn = rgbValue3.ToString().Substring(0, Outlen);
}
else
{
strReturn = rgbValue3.ToString().Substring(0, Outlen);
}
}
}
} while (!(m_intRetCode != SQL_SUCCESS));

if (strReturn == "")
{
if (intRetCode == SQL_NO_DATA_FOUND)
{
strReturn = "No data found";
}
else if (intRetCode == SQL_INVALID_HANDLE)
{
strReturn = "Invalid handle";
}
else
{
strReturn = NO_INFO;
}
}
return strReturn;
}

private static bool InitializeEnvironment()
{
if (!m_hEnvironment.Equals(IntPtr.Zero))
return true;

m_intRetCode = SQLAllocEnv(ref m_hEnvironment);
if ((m_intRetCode == SQL_ERROR | m_intRetCode == SQL_INVALID_HANDLE))
{
MessageBox.Show("Unable to allocate ODBC environment handle");
m_hEnvironment = IntPtr.Zero;
return false;
}
else
{
return true;
}
}

private static bool ExitEnvironment()
{
m_intRetCode = SQLFreeEnv(m_hEnvironment);
if (m_intRetCode != SQL_SUCCESS)
{
MessageBox.Show("Unable to free ODBC environment handle");
}
else
{
m_hEnvironment = IntPtr.Zero;
return true;
}
return false;
}

private static int BeginConnection(string sConnectString)
{
try
{
if (sConnectString.Trim().Length == 0)
{
throw new ArgumentException("Connection string cannot be blank.");
}
m_intRetCode = SQLAllocConnect(m_hEnvironment, ref m_hDatabaseConnection);
if (m_intRetCode != SQL_SUCCESS)
{
throw new ApplicationException("Cannot allocate connection handle");
}
string strConStrIn = sConnectString;
string strConStrOut = new string(' ', 1024);
int length = 0;
StringBuilder builder = new StringBuilder(1024);
m_intRetCode = SQLDriverConnect(m_hDatabaseConnection, IntPtr.Zero, strConStrIn, strConStrIn.Length, builder, builder.Length, ref length, SQL_DRIVER_NOPROMPT);
if ((m_intRetCode == SQL_ERROR) | (m_intRetCode == SQL_INVALID_HANDLE))
throw new ApplicationException(GetSQLErrorDesc(m_hEnvironment, m_hDatabaseConnection, m_hStatement, 0));

if (m_intRetCode == SQL_SUCCESS)
m_intRetCode = SQLAllocStmt(m_hDatabaseConnection, ref m_hStatement);
}
catch
{
m_intRetCode = SQL_ERROR;
}
return m_intRetCode;
}

private static bool EndConnection()
{
m_intRetCode = SQLFreeStmt(m_hStatement, SQL_CLOSE);
if (m_intRetCode != SQL_SUCCESS)
{
MessageBox.Show("Unable to free statment handle");
}
m_hStatement = IntPtr.Zero;
m_intRetCode = SQLDisconnect(m_hDatabaseConnection);
if (m_intRetCode != SQL_SUCCESS)
{
MessageBox.Show("Unable to disconnect");
}
m_intRetCode = SQLFreeConnect(m_hDatabaseConnection);
if (m_intRetCode != SQL_SUCCESS)
{
MessageBox.Show("Unable to free connection handle");
}
m_hDatabaseConnection = IntPtr.Zero;
return true;
}

private static List<string> GetTables()
{
return GetTables(true, "'TABLE', 'VIEW', 'SYSTEM TABLE'");
}

private static List<string> GetTables(bool Owner, string TableType)
{
List<string> listTables = new List<string>();
int lngLenRead = 0;

m_intRetCode = SQLTables(m_hStatement, "\0", SQL_NTS, "\0", SQL_NTS, "\0", SQL_NTS, "TABLE,VIEW,SYNONYM", SQL_NTS);
if (m_intRetCode != SQL_SUCCESS)
{
MessageBox.Show(GetSQLErrorDesc(m_hEnvironment, m_hDatabaseConnection, m_hStatement, (int)m_intRetCode));
}
else
{
StringBuilder strOwner = new StringBuilder(255);
StringBuilder strName = new StringBuilder(255);
m_intRetCode = SQLFetch(m_hStatement);
if (m_intRetCode != SQL_SUCCESS)
{
MessageBox.Show(GetSQLErrorDesc(m_hEnvironment, m_hDatabaseConnection, m_hStatement, m_intRetCode));
}
else
{
while (m_intRetCode != SQL_NO_DATA_FOUND)
{
m_intRetCode = SQLGetData(m_hStatement, 2, SQL_C_CHAR, strOwner, strOwner.Capacity, ref lngLenRead);
m_intRetCode = SQLGetData(m_hStatement, 3, SQL_C_CHAR, strName, strName.Capacity, ref lngLenRead);
if (strOwner.Length > 0 && strName.Length > 0)
{
listTables.Add(strOwner.ToString() + "." + strName.ToString());
strOwner = new StringBuilder(255);
strName = new StringBuilder(255);
m_intRetCode = SQLFetch(m_hStatement);
}
}
}
}
return listTables;
}
}

Bob

asi

ASKER

How do i receive fields for table ?

asi

ASKER

For some reason when i try to use it:

List <string> lsttable= OdbcSchema.GetDSNTables( "MySql_Northwind");

i receive an error "Cannot allocate connection handle" , why ?
Thank you
Asi