[Firebird-net-provider] Reply to two people, Alan McDonald and Carlos Guzman Alvarez, with questions about transactions

[Jason V. <jas...@ya...>]

Thanks for jumping in here!

Here's an example of the problem I'm having.

This is a slightly modified version of the example
found on MSDN for SqlDataReader and such.

As Carlos pointed out, if I use the transaction for
Sql2000 (MS), it should work for all users. I still
can't get my mind to accept just WHY a transaction is
required to read data, as it isn't for most SQL
engines I've used thus far, and from ADO (while my
ODBC connector trial was still working), Firebird
didn't require them either, as I could tell.

The example below is in two sections, the first is for
Firebird, the second (commented out) is for MS SQL. I
switch to that by removing the comment blocks, and
commenting out the upper portion for Firebird.

The target is compiled for a managed target, console
application (as setup from MSDN), so "letting go" of
pointers like it does is "acceptable" - though not so
much to me - it works without complaint in MS SQL, and
illustrates the problem.

In the Firebird version, the trans->Commit() causes an
"InvalidOperationException" - so it can't continue.
Since the "ExecuteReader" won't work without a
transaction, I'm at a loss for just how to use the
same connection a second time without first closing
it. 

On the other hand, the MS SQL version runs as it is,
and runs fine without the transaction object.

I tried replacing the commit with rollback, and just
letting the transaction fall out of scope. Rollback
fires the same exception as commit, and even when the
transaction object falls out of scope, the connection
still has a transaction open, and won't allow another
reader to be executed (even when the previous reader
is closed).

What am I not understanding here?

BTW, I tried building the source for the Firebird
provider in VS.NET 2003. I created a managed DLL
target, tossed in all of the CS modules, relocated the
"SNK" file, and with only commenting out the
System.Drawing and the "ToolBox" entry, it compiled
without errors or complaints. However, for reasons I
don't yet understand, while the target applications
link properly, the fire an exception complaining they
can't find the assembly, even when RegASM is used, and
the original Firebird....dll is tucked away elsewhere.
I think the material I commented out is important, but
there must be an MS equivalent I'm not including. I'll
download NANT and try with that (never heard of it
before now - and I can't yet figure out how to build
it with Mono).




#using <mscorlib.dll>
using namespace System;

#using "System.dll"
#using "System.Data.dll"
#using
"d:\firebirdnetprovider\FirebirdSql.Data.Firebird.dll"



using namespace System::ComponentModel;
using namespace System::Data;
using namespace System::Data::SqlClient;

using namespace FirebirdSql::Data::Firebird;


void main()
{
	// Query user for user id and password

	// Connect to the SQL Database and issue a SELECT
command all in one statement
	String *query = "SELECT * FROM DEPARTMENT";
	// build connect string with the userid and password

    String *connectString =
"Database=d:\\firebird\\examples\\employee.fdb;User=xxxxxx;Password=xxxxxxx;Dialect=3;Server=192.168.0.1;";




	//You can use this format if you are connecting with
a known user name and password,
	// however it is not recommended to store these in
the source for security reasons
	//String *connectString =  S"Data
Source=localhost;Database=Northwind;UID=sa;Password=;";

	FbConnection* sqlconn = new
FbConnection(connectString);
	sqlconn->Open();



	FbCommand *sqlCommand = new FbCommand(query,
sqlconn);

    FbTransaction *trans =
sqlconn->BeginTransaction();
    sqlCommand->Transaction = trans; 

	// Create a SqlDataReader to enumerate the result of
executing the command against the database.
	FbDataReader *dataReader =
sqlCommand->ExecuteReader();

	// Find number of columns in result
	int numCols = dataReader->FieldCount;

	// Display number of columns returned from query
	Console::Write(S"No. of columns:");
	Console::WriteLine(numCols);
 
	// Display the data, separated by tabs
	while(dataReader->Read())
	{
		for (int c = 0; c < numCols; c++)
		{
			Console::Write(dataReader->Item[c]->ToString());
			Console::Write(S"\t");
		}
		Console::WriteLine(S"");
	}


//
==============================================================


// at the following Commit, an
"InvalidOperationException" fires


//
==============================================================

   trans->Commit();
   dataReader->Close();

	sqlCommand = new FbCommand(query, sqlconn);

    trans = sqlconn->BeginTransaction();
    sqlCommand->Transaction = trans; 

	// Create a SqlDataReader to enumerate the result of
executing the command against the database.
	dataReader = sqlCommand->ExecuteReader();

	// Find number of columns in result
	numCols = dataReader->FieldCount;

	// Display number of columns returned from query
	Console::Write(S"No. of columns:");
	Console::WriteLine(numCols);
 
	// Display the data, separated by tabs
	while(dataReader->Read())
	{
		for (int c = 0; c < numCols; c++)
		{
			Console::Write(dataReader->Item[c]->ToString());
			Console::Write(S"\t");
		}
		Console::WriteLine(S"");
	}


   trans->Commit();
   dataReader->Close();


}




/*
void main()
{
	// Query user for user id and password

	// Connect to the SQL Database and issue a SELECT
command all in one statement
	String *query = S"SELECT * FROM [Order Details]";
	// build connect string with the userid and password
	String *connectString = S"Data
Source=192.168.0.1;Database=Northwind;UID=xxxxxxx;Password=xxxxxxxxx;";

	//You can use this format if you are connecting with
a known user name and password,
	// however it is not recommended to store these in
the source for security reasons
	//String *connectString =  S"Data
Source=localhost;Database=Northwind;UID=sa;Password=;";

	SqlConnection* sqlconn = new
SqlConnection(connectString);
	sqlconn->Open();

    SqlTransaction *trans =
sqlconn->BeginTransaction();
	SqlCommand *sqlCommand = new SqlCommand(query,
sqlconn);
    sqlCommand->Transaction = trans;

	// Create a SqlDataReader to enumerate the result of
executing the command against the database.
	SqlDataReader *dataReader =
sqlCommand->ExecuteReader();

	// Find number of columns in result
	int numCols = dataReader->FieldCount;

	// Display number of columns returned from query
	Console::Write(S"No. of columns:");
	Console::WriteLine(numCols);

	// Display the data, separated by tabs
	while(dataReader->Read())
	{
		for (int c = 0; c < numCols; c++)
		{
			Console::Write(dataReader->Item[c]->ToString());
			Console::Write(S"\t");
		}
		Console::WriteLine(S"");
	}

    trans->Commit();
    dataReader->Close();


    trans = sqlconn->BeginTransaction();
	sqlCommand = new SqlCommand(query, sqlconn);
    sqlCommand->Transaction = trans;

	// Create a SqlDataReader to enumerate the result of
executing the command against the database.
	dataReader = sqlCommand->ExecuteReader();

	// Find number of columns in result
	numCols = dataReader->FieldCount;

	// Display number of columns returned from query
	Console::Write(S"No. of columns:");
	Console::WriteLine(numCols);

	// Display the data, separated by tabs
	while(dataReader->Read())
	{
		for (int c = 0; c < numCols; c++)
		{
			Console::Write(dataReader->Item[c]->ToString());
			Console::Write(S"\t");
		}
		Console::WriteLine(S"");
	}



   trans->Commit();
   dataReader->Close();


}

*/



__________________________________
Do you Yahoo!?
SBC Yahoo! DSL - Now only $29.95 per month!
http://sbc.yahoo.com