[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.dotnet.framework.odbcnet

Problem with ConnectionString and MySQL

Steffen Schloenvoigt

4/26/2005 9:31:00 AM

Hey everybody,

since System.Data.Odbc lacks support of SQLTables,
I decided to write a little Class to call this
function by invoking it from the native odbc32.dll.

It works by now really fine except, when I try to
connect to a DSN without giving a uid and pwd.
The SQLConnect()-function seems to override the values
that are stored in the DSN. So if I connect to a MySQL-DB
I get "Access denied for user ODBC@localhost".

Is there any possibility to use the uid and pwd
that the user defined in the DSN?
Connecting with System.Data.Odbc works, so I
think it MUST be possible.

I tried different values for UID and PWD and
setting NameLengt2 and NameLength3 to both,
SQL_NTS and 0. Nothing worked.

Could anyone help me please?
With best regards
Steffen Schloenvoigt

PS: Sorry for my bad english. I did't sleep much last night ;-)
2 Answers

Steffen Schloenvoigt

6/8/2005 7:34:00 AM

0

Hi Andy,

I''m sorry but my boss doesn''t like the idea
to post the whole code out into the world.
But using SQLDriverConnect() instead of SQLConnect()
did solve the Problem.
But I want to help you to get a clue about what I''m talking:

/*
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
*/
For the SQLTables you''ve got to do the following imports:
/*
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
*/

[DllImport("odbc32")]
internal static extern OdbcReturn SQLAllocHandle (
OdbcHandleType HandleType,
IntPtr InputHandle,
ref IntPtr OutputHandlePtr);

[DllImport("odbc32")]
internal static extern OdbcReturn SQLSetEnvAttr (IntPtr
EnvironmentHandle,
OdbcEnv Attribute,
IntPtr Value, int StringLength);

[DllImport("odbc32")]
internal static extern OdbcReturn SQLDriverConnect( IntPtr
ConnectionHandle , IntPtr WindowHandle,
string InConnectionString , short StringLength1,
string OutConnectionString , short BufferLength,
ref short StringLength2Ptr , ushort DriverCompletion);

[DllImport("odbc32")]
internal static extern OdbcReturn SQLDisconnect(IntPtr
ConnectionHandle);

[DllImport("odbc32")]
internal static extern OdbcReturn SQLFreeHandle(ushort
HandleType, IntPtr SqlHandle);

[DllImport("odbc32")]
private static extern OdbcReturn SQLTables( IntPtr StatementHandle,
string CatalogName, short NameLength1,
string SchemaName , short NameLength2,
string TableName , short NameLength3,
string TableType , short NameLength4);

[DllImport("odbc32")]
private static extern OdbcReturn SQLBindCol( IntPtr StatementHandle,
ushort ColumnNumber,
short TargetType,
byte[] TargetValuePtr,
int BufferLength,
ref int StrLen_or_Ind);

[DllImport("odbc32")]
internal static extern OdbcReturn SQLFetch (IntPtr StatementHandle);

/*
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
*/
Then u''ll need some enums:
/*
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
*/

internal enum OdbcHandleType : short
{
/// <summary>
/// Environment-Handle
/// </summary>
Environment = 1,
/// <summary>
/// Database-Connection-Handle
/// </summary>
DataBaseConnection = 2,
/// <summary>
/// Statement-Handle
/// </summary>
Statement = 3,
/// <summary>
/// Description-Handle
/// </summary>
Description = 4
}


internal enum OdbcReturn : short
{
Error = -1,
InvalidHandle = -2,
StillExecuting = 2,
NeedData = 99,
Success = 0,
SuccessWithInfo = 1,
NoData = 100
}

/*
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
*/
Now the code: (After every call, you should check ret of cause)
/*
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
*/

IntPtr envHandle = null;
IntPtr connHandle = null;
IntPtr statementHandle = null;
OdbcReturn ret = OdbcReturn.Error;

// First we need a environment-Handle
ret = SQLAllocHandle (OdbcHandleType.Environment, IntPtr.Zero, ref
envHandle);
// Then we''ve got to set the environment to version 3
ret = SQLSetEnvAttr (envHandle, 200, (IntPtr) 3 , 0);
// Then we create the connection-Handle
ret = SQLAllocHandle (OdbcHandleType.DataBaseConnection, envHandle, ref
connHandle);

/* ++++++++++++++++++Now we connect +++++++++++++++++++++*/

// Connection-String
string connectionString = "dsn="+DSN-Name;
// create a Buffer
string OutConnectionString=new String('' '',1024);
// Set length to zero, will be changed by SQLDriverConnect()
short OutLen=0;
// Connect
ret = SQLDriverConnect( connHandle,
IntPtr.Zero,
connectionString,
-3,
OutConnectionString,
(short)
OutConnectionString.Length,
ref OutLen,
0);

/* ++++++++++++++++++ Now we are connected +++++++++++++++++++*/

// Next we need a statement-Handle
ret = SQLAllocHandle (OdbcHandleType.Statement, connHandle, ref
statementHandle);

// Now lets execute SQLTables:
ret = SQLTables(statementHandle, null, 0, null, 0, null, 0, null, 0);

// If SQLTables() returned Success we can fetch the results
if(ret == OdbcReturn.Success || ret == OdbcReturn.SuccessWithInfo )
{
// create buffers
int STRLEN = 128;
byte[] _cat = new byte[STRLEN];
byte[] _schem = new byte[STRLEN];
byte[] _name = new byte[STRLEN];
byte[] _type = new byte[STRLEN];
byte[] _desc = new byte[STRLEN];
// the length of the strings will be returned into the next values
int _cat_c=0, _schem_c=0, _name_c=0, _type_c=0, _desc_c=0;

// bind the cols
SQLBindCol ( statementHandle, 1, (short)1, _cat,
STRLEN, ref _cat_c );
SQLBindCol ( statementHandle, 2, (short)1, _schem, STRLEN,
ref _schem_c );
SQLBindCol ( statementHandle, 3, (short)1, _name, STRLEN,
ref _name_c );
SQLBindCol ( statementHandle, 4, (short)1, _type,
STRLEN, ref _type_c );
SQLBindCol ( statementHandle, 5, (short)1, _desc, STRLEN,
ref _desc_c );


while(true)
{
ret = SQLFetch(statementHandle);
if (ret == OdbcReturn.Error)
{
throw new Exception("Error while fetching a row
of data");
}
if (ret == OdbcReturn.Success || ret ==
OdbcReturn.SuccessWithInfo)
{
string categorie = ByteArrayToString(
_cat , _cat_c );
string schema = ByteArrayToString(
_schem, _schem_c );
string name =
ByteArrayToString( _name , _name_c );
string typ =
ByteArrayToString( _type , _type_c );
string description = ByteArrayToString(
_desc , _desc_c );

/*
++++++++++++++++++++++++++++++++++++++++++++++++++*/
// Now do with the strings what you want
/*
++++++++++++++++++++++++++++++++++++++++++++++++++*/
}
else
{
// No more rows in there
break;
}
}
}

/*
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
*/
// Additional this method could be usefull
/*
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
*/
private static string ByteArrayToString(byte[] bytes, int length)
{
StringBuilder sb = new StringBuilder();
for(int i=0; i<length; i++)
{
sb.Append((char)(bytes[i]));
}
return sb.ToString();
}

/*
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
*/
/*
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
*/
/*
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
*/

I hope this will help you and others on the list somehow.
Don''t forget to extend the code with some error-handling.
For further information please read the api-documentation under
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcabout_this_...

With best regards
Steffen

Andy

6/8/2005 12:07:00 PM

0

Steffen,

Great, thanks for the code. So it sounds like you did end up solving
your original problem? If not, I''ll try it out likely in a few days
(had to move on for now) and see if I can find whats wrong.

Thanks again!
Andy