ADODB Problems

[datenis]

I'm using ADODB to connect to PSQL 2000. I'm using VS 2005 and C# to do this. I'm trying to add the pervaisve connectivity to some existing code.

For ADODB, I'd just add the reference and then I'd thought it should work after calling the below code:

ADODB.Connection cnBV = new ADODB.Connection();
cnBV.Open("LocalDB", "", "", -1);

However, Pervasive bounces back with a "Driver Not Capable" Error. The bizzare thing is that when I create a new C# project and call the same two lines above with the reference added, the code steps through properly.

Please help.

 

[mirtheil]

Why are you using ADODB in C$?
I would suggest using the OdbcConnection object.
THen again, you are using an old version of PSQL. I haven't used that version in C#.
You would use something like:

using System;
using System.Data;
using System.Data.Odbc;
using System.IO;

namespace SimpleAdoNetConsole
{
	class Class1
	{
		[STAThread]
		static void Main(string[] args)
		{
			try
			{
				OdbcConnection conn=new OdbcConnection("DSN=DEMODATA");
				conn.Open();
				// Create a SQL command
				string SQLstr = "select * from class";
		
				OdbcCommand DBCmd = new OdbcCommand(SQLstr, conn);
				OdbcDataReader myDataReader;
				myDataReader = DBCmd.ExecuteReader();
				Console.WriteLine("FieldCount: " + myDataReader.FieldCount.ToString());
				//Console.WriteLine("Records Affected:" + myDataReader.RecordsAffected.ToString());
				Console.ReadLine();
				while (myDataReader.Read())
				{
					for (int i=0;i<myDataReader.FieldCount;i++)
					{
						Console.WriteLine("Field " + i.ToString() + ": " + myDataReader[i].ToString());
					}
				}
				myDataReader.Close();
				conn.Close();
				Console.WriteLine("Press Enter to continue");
				Console.ReadLine();
			}
			catch (Exception ex)
			{
				Console.WriteLine(ex.Message);
				Console.ReadLine();
			}
			
		}
	}
}

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[datenis]

Hi Mirtheil,

I've heed your advice and tried using the ODBC connection object in C#

I was able to step through that code in the brand new project, and I get a different error if the code were added to an existing object, with the following exception thrown on conn.open():

ERROR [25000] [Microsoft][ODBC Driver Manager] Failed to enlist on calling object's transaction
ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed
ERROR [01000] [Microsoft][ODBC Driver Manager] The driver doesn't support the version of ODBC behavior that the application requested (see SQLSetEnvAttr).

My question is: What can cause these exceptions thrown at one project and not another, given that the same code was executed?

 

[mirtheil]

What else is happening in your code where you see the failure?
The following error is caused because PSQL ODBC is ODBC v2.5 but C# is treating it as an ODBC v3.0 driver.

ERROR [01000] [Microsoft][ODBC Driver Manager] The driver doesn't support the version of ODBC behavior that the application requested (see SQLSetEnvAttr).

I'm not sure about the other messages.



Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[datenis]

I've tried another way, where I create another brand new project, and copy all code from the existing project over, added those references, and at last added the lines of BV connection code.

This time I get a popup message from Pervasive ODBC Engine Interface:

"Connect failed because you attempted a DSN less connection. Please, retry your connection with a DSN. If you already attempted the connection using a DSN, please retry after recreating the DSN.

And then the exception is thrown with the following message:

ERROR [IM003] Specified driver could not be loaded due to system error  1114 (Pervasive ODBC Engine Interface).

Now If I go back to the dummy project with only the two lines of connection code I get the same error.

 

[datenis]

I was able to get around the above problem. But then I'm back to the same issue with

ERROR [01000] [Microsoft][ODBC Driver Manager] The driver doesn't support the version of ODBC behavior that the application requested (see SQLSetEnvAttr).

Is there a way to force the project to use ODBC V2.5 for the connections to pervasive?

Could this be caused by the fact that I have other references that may use the newer odbc driver on my existing project?

 

[mirtheil]

Does my sample return the same error?
I've never seen it as an error but more of a warning. Can you confirm exactly where the error is displayed? Can you post the shortest version of your code that shows the error you're seeing?

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[datenis]

Hi Mirtheil,

Yes your code works. But I cannot get it to step through conn.Open without an exception when I added your code to my code. I still get the "Driver doesn't support the version of ODBC" error.

 

[mirtheil]

Because my code doesn't give the same exception, please post your code as you have it written when the exception occurs.

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[datenis]

I stepped through your code, and therefore it didn't go to the exception.

public void mirthcode()
        {
               try
            {
                OdbcConnection conn = new OdbcConnection("DSN=DEMODATA");
                conn.Open();
                // Create a SQL command
                string SQLstr = "select * from class";

                OdbcCommand DBCmd = new OdbcCommand(SQLstr, conn);
                OdbcDataReader myDataReader;
                myDataReader = DBCmd.ExecuteReader();
                Console.WriteLine("FieldCount: " + myDataReader.FieldCount.ToString());
                //Console.WriteLine("Records Affected:" + myDataReader.RecordsAffected.ToString());
                Console.ReadLine();
                while (myDataReader.Read())
                {
                    for (int i = 0; i < myDataReader.FieldCount; i++)
                    {
                        Console.WriteLine("Field " + i.ToString() + ": " + myDataReader[i].ToString());
                    }
                }
                myDataReader.Close();
                conn.Close();
                Console.WriteLine("Press Enter to continue");
                Console.ReadLine();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

 

[datenis]

The code fails on conn.open()

 

[datenis]

Could threading be a problem? Since the code that is being called is spawned as a thread from another program.

 

[mirtheil]

I'm not sure. Threading shouldn't cause that kind of behavior. How is the exception displayed? I'm still not seeing the behavior you are seeing. What kind of projects are you creating?


Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com