firebird-net-provider — All about development of the MS .Net provider

[Firebird-net-provider] Possible DataAdapter Problem

[Mark E. S. Jr. <ms...@sc...>]

I'm working with a downloaded copy of Firebird (downloaded less than two
weeks ago from sourceforge), on a WinXP box.

I'm able to work with the server just fine, have imported a large amount
of data, used the ODBC driver to import data from Access with success.
However, I have not been able to get a driver working well in .NET
either version 1.0 or 1.1.

When using the FirebirdSql.Data.Firebird drivers, it usually works the
first time, but after that, I get sometimes a SQL error with a code
-104, and after that it starts just telling me "Error reading data from
the connection" on the line where I call the DataAdapter.Fill function.

I haven't seen somebody using a DataAdapter in the way that I am, so I'm
wondering if its just a DataAdapter issue.  Below you can see the
example code I'm testing with.  After I get the data I'm just piping out
out to XML.

string sSQL = "SELECT * FROM INVOICERECEIPT_HEADER WHERE
POSTHEADID=8220;";
FbConnection conn = new FbConnection("Database=D:\Program
Files\Firebird\data\test.gdb;User=test_user;Password=zipper;Dialect=3;Se
rver=localhost;Role=authors");

FbCommand cmd = new FbCommand(sSQL, conn);
cmd.CommandType = CommandType.Text;

FbDataAdapter da = new FbDataAdapter(cmd);
DataSet ds = new DataSet("InvoiceReceipt");

conn.Open();
FbTransaction trans = conn.BeginTransaction();
cmd.Transaction = trans;
da.Fill(ds, 0, 0, "Header");
trans.Commit();
conn.Close();

Like I said, it seems to work the first time especially after a reboot,
sometimes it will work a second time, but after that I get the SQL -104
error, and after that I just keep getting "Error reading data from the
connection".

Is there something I'm doing wrong?  The only thing I had to add over
the documentation in the help files is the Transaction code.  It would
not allow me to execute anything without first making a transaction.

Thanks!

Mark E. Scott Jr.
ms...@sc...

P.S. I was originally using the compiled DLL from the site, and have now
downloaded the latest source from CVS and included it in my Visual
Studio project so I could perhaps use the debugger and look through
whats happening.  Haven't had time to do that yet.

Re: [Firebird-net-provider] i forgot

[<car...@te...>]

Hello:


> i forgot to tell you that i compile it simple by typeing nant in 
> comandline.

Can you send the database structure ??


-- 
Best regards

Carlos Guzmán Álvarez
Vigo-Spain

"No tengo dones especiales.Sólo soy apasionadamente curioso"
Albert Einstein, científico.

[Firebird-net-provider] i forgot

[Pavol S. <pa...@st...>]

hallo again,

i forgot to tell you that i compile it simple by typeing nant in comandline.

p.

[Firebird-net-provider] hi :(

[Pavol S. <pa...@st...>]

hi i downloaded new cvs tree, i still have problem...(PINVOKE) sending 
you comlete data...

DBSearchWords, DBArticle compile in to dll
main is testing program...

please test it :-( I dont know what to do :(

thanx p.

Re: [Firebird-net-provider] FbConnection, FbCommand and IDbConnection, IDbCommand

[<car...@te...>]

Hello:

> FbConnection should implement
> IDbConnection.Open
> IDbConnection.Close
> IDbConnection.ChangeDatabase
> 
> FbCommand should implement:
> IDbCommand.ExecuteNonQuery
> IDbCommand.ExecuteScalar
> IDbCommand.Cancel
> IDbCommand.Prepare

I can implement it but can you tell for what is needed an explicit 
implementation of these IDbConnection and IDbCommand methods ??.


--
Best regards

Carlos Guzma'n A'lvarez
Vigo-Spain

"No tengo dones especiales.So'lo soy apasionadamente curioso"
Albert Einstein, cienti'fico.

[Firebird-net-provider] FbConnection, FbCommand and IDbConnection, IDbCommand

[Dumitru P. <pop...@ya...>]

FbConnection should implement
IDbConnection.Open
IDbConnection.Close
IDbConnection.ChangeDatabase

FbCommand should implement:
IDbCommand.ExecuteNonQuery
IDbCommand.ExecuteScalar
IDbCommand.Cancel
IDbCommand.Prepare


__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com

Re: [Firebird-net-provider] next think

[<car...@te...>]

Hello:

> hext think is, that if you leave connection live to long (under havy 
> load) it is growing in server...
> as i said i have firebird on machine with 2Gb ram and when fb grows too 
> big (due this persistant connection) i recive that exception, taht data 
> cannot be read from connection... :(
> 
> it looks that we need something to force pooled connection to close and 
> open new connection, there can be counter in getconnection and if 
> connection if in pool to long, it will be closed and new connection will 
> rise...


Hummm i not sure of this, i will try the as3ap bencmark in multiuser 
mode, using pooling, how this
have tests of 15 long i think that can be a good test or you leave the 
connection with much more time ???




Best regards
Carlos Guzmán Álvarez
Vigo-Spain

Re: [Firebird-net-provider] again PInvoke exception

[<car...@te...>]

Hello:

> it is compiled ok, but after start there is that exception...
> Did cvs tree changed??? 

On ly for delete GC.Collect() call

> am i downloading correct data???

I think yes, if you want i can send a new build and you can test with 
it, i will get all source from cvs ina new directory and test it for try 
to see what happening.




Best regards
Carlos Guzmán Álvarez
Vigo-Spain

Re: [Firebird-net-provider] big problem

[<car...@te...>]

Hello:

> this happend under havy load :( i have aprox. 40000 articles in 
> database.. and im creating list of words for seraching. It means get 
> article from database, split it in words array and foreach word look if 
> it is in database if not insert it... i use one connection and one 
> transaction for all word looking and inserting and separate connection 
> and transaction for geting article...

Ok,  i have made tests inserting 100 000 in 4 tables without problems, 
are you using Fb 1.0 or Fb 1.5 ??

> If you will want i will send you my code..

If you can send a test case it can be helpfull.




Best regards
Carlos Guzmán Álvarez
Vigo-Spain

[Firebird-net-provider] next think

[Pavol S. <pa...@st...>]

hi again,

hext think is, that if you leave connection live to long (under havy 
load) it is growing in server...
as i said i have firebird on machine with 2Gb ram and when fb grows too 
big (due this persistant connection) i recive that exception, taht data 
cannot be read from connection... :(

it looks that we need something to force pooled connection to close and 
open new connection, there can be counter in getconnection and if 
connection if in pool to long, it will be closed and new connection will 
rise...

thanx
p.

[Firebird-net-provider] again PInvoke exception

[Pavol S. <pa...@st...>]

hi,

i have there PInvoke exception as before...

i downloaded sources from

ano...@cv...:/cvsroot/firebird

it is compiled ok, but after start there is that exception...
Did cvs tree changed??? am i downloading correct data???

p.

[Firebird-net-provider] big problem

[Pavol S. <pa...@st...>]

hallo

i have big problem


Unhandled Exception: FirebirdSql.Data.Firebird.FbException: invalid 
request hand
le

    at FirebirdSql.Data.Firebird.FbCommand.Prepare()
    at NovyCas.DBSearchWords.InsertWord(String iWord, FbConnection& 
dbConn, FbTra
nsaction& dbTran) in C:\Projekty\NetStarek\NovyCas\DBSearchWords.cs:line 50
    at NovyCas.DBSearchWords.WordsToDb(String[] iWords) in 
C:\Projekty\NetStarek\
NovyCas\DBSearchWords.cs:line 75
    at Tests.Main(String[] args) in 
C:\Projekty\NetStarek\Tests\Main.cs:line 72

Unhandled Exception: FirebirdSql.Data.Firebird.FbException: Error 
reading data f
rom the connection.

    at FirebirdSql.Data.Firebird.FbConnection.Close()
    at NovyCas.DBSearchWords.WordsToDb(String[] iWords) in 
C:\Projekty\NetStarek\
NovyCas\DBSearchWords.cs:line 83
    at Tests.Main(String[] args) in 
C:\Projekty\NetStarek\Tests\Main.cs:line 72
Press any key to continue . . .






this happend under havy load :( i have aprox. 40000 articles in 
database.. and im creating list of words for seraching. It means get 
article from database, split it in words array and foreach word look if 
it is in database if not insert it... i use one connection and one 
transaction for all word looking and inserting and separate connection 
and transaction for geting article...
If you will want i will send you my code..

p.

Re: [Firebird-net-provider] garbage collecting

[<car...@te...>]

Hello:

> i think, that you have to force garbage collection only in case, that 
> you have to dispose expensive resources and i think, that it is better 
> to call dispose and make object not reincarnable...

I have made some tests to the firebird .net provider without 
GC.Collect() Call using the as3ap bechmark with the 40 mb data ( that 
gives then 10 users simulation in multiuser mode ) and all seems to work 
well and memory used is aproximately the same than without it, i have 
removed this call and commit the changes to the CVS





Best regards
Carlos Guzmán Álvarez
Vigo-Spain

Re: [Firebird-net-provider] garbage collecting

[<car...@te...>]

Hello:

> this is not bug report, its just question...
> 
> why you have there in FbConnectonPoll.CleanUp CG.Collect??? every where 
> i was looking for this is said, that GC know best when to collect and 
> so... (exact criteria are guarded by microsoft :)

 > i think, that you have to force garbage collection only in case, that
 > you have to dispose expensive resources and i think, that it is better
 > to call dispose and make object not reincarnable....

Yes i know that this is not really needed, it's only for being sure that 
all is going well with garbage collection :)




Best regards
Carlos Guzmán Álvarez
Vigo-Spain

[Firebird-net-provider] garbage collecting

[Pavol S. <pa...@st...>]

hi,

this is not bug report, its just question...

why you have there in FbConnectonPoll.CleanUp CG.Collect??? every where 
i was looking for this is said, that GC know best when to collect and 
so... (exact criteria are guarded by microsoft :)

i think, that you have to force garbage collection only in case, that 
you have to dispose expensive resources and i think, that it is better 
to call dispose and make object not reincarnable...

just question for discussion not bug...

p.

Re: [Firebird-net-provider] garbage collecting

[<car...@te...>]

Hello:

> How many people is in mailing list??? 


There are 43 people who have subscribed to the list.





Best regards
Carlos Guzmán Álvarez
Vigo-Spain

Re: [Firebird-net-provider] garbage collecting

[<car...@te...>]

Hello:

> im just studing your sources... I was looking for something to improve but i
> found nothing :-)
> Simply you done great work... really great... but i still want to help you,
> so i will continue looking in sources :)

Thanks very much :)


> i have some questions:
> 
> are you doing all sources alone??? im checking this mailing list since
> alpha1 i think, but i didnt found there any other code contributor...

At this moment yes i'm alone :), but there are a guy ( Tom Antony ) that 
contribute the source code of isc_database_info method of the gds, and i 
need to give great thanks to people that report the errors that they found.

There are some things that i can make like see if i can make the 
implementation FbPermission and FbPermissionAttribute, but there really 
little information on how this classes need to work and i'm not sure 
that they are really needed at all, and try to add support to array 
fields but for this i need to see Firebird sources and this will get 
lots of time to me since i have a limited knowledge of C/C++.


> How many people is in mailing list??? 

I don't know but i can try to ask it to one of the list admins to see if 
he can say the number to us.

> And how many people is using your provider???

For this i only can tell the number of downloads that are reflected in 
Sourceforge project page:

	http://sourceforge.net/project/showfiles.php?group_id=9028



Best regards
Carlos Guzmán Álvarez
Vigo-Spain

[Firebird-net-provider] garbage collecting

[Pavol S. <st...@ra...>]

hi,

im just studing your sources... I was looking for something to improve but i
found nothing :-)
Simply you done great work... really great... but i still want to help you,
so i will continue looking in sources :)

i have some questions:

are you doing all sources alone??? im checking this mailing list since
alpha1 i think, but i didnt found there any other code contributor...

How many people is in mailing list??? And how many people is using your
provider???

Re: [Firebird-net-provider] more commands

[<car...@te...>]

Hello:

> and you said that you implemented named parameters??? I was looking for 
> that in sources yesterday but i didnt find it :(


I have commited to the CVS a little changes on this, please if you can 
get it.





Best regards
Carlos Guzmán Álvarez
Vigo-Spain

Re: [Firebird-net-provider] more commands

[<car...@te...>]

Hello:

> i think, that ExecuteSclalar is working fine , but when i use same 
> connection and transaction for two different commands there is 
> exception, that transactions in trans and conn are different... I will 
> try it with your build... Again thanx for that...

Ok, tell to me if you continue to experience this problem.

> by the way... I wan to help you improve helps, so i will send you 
> modified xml files...

Ok without problems.

> and you said that you implemented named parameters??? I was looking for 
> that in sources yesterday but i didnt find it :(

See in FbStatement.cs, this it's a really simple modification but it's 
working well in my tests ( ideas are welcome :) ), GetInSqlda was 
modified and there are a new method:

		/// <include file='xmldoc/fbstatement.xml' 
path='doc/member[@name="M:GetInSqlda"]/*'/>		
		private XSQLDA GetInSqlda()
		{
			XSQLDA		in_sqlda	= statement.InSqlda;
			Encoding	encoding	= Connection.Encoding;
			Regex		search		= new Regex("(@([a-zA-Z-$][a-zA-Z0-9_$]*).)");
			string		parameterName = String.Empty;
			
			if (in_sqlda == null)
			{
				return in_sqlda;
			}

			MatchCollection matches = search.Matches(CommandText);
			
			for (int i = 0; i < in_sqlda.sqln; i++)
			{
				try
				{
					parameterName = matches[i].Value.Trim();
				}
				catch
				{
					parameterName = parameters[i].ParameterName;
				}

				if (parameters[parameterName].Value == System.DBNull.Value)
				{
					if ((in_sqlda.sqlvar[i].sqltype & 1 ) == 0)
					{
						throw new InvalidOperationException("Input parameter value cannot 
be null.");
					}
					else
					{
						in_sqlda.sqlvar[i].sqlind	= -1;
						in_sqlda.sqlvar[i].sqldata	= null;
					}
				}
				else
				{
					long multiplier = 1;
					if (in_sqlda.sqlvar[i].sqlscale < 0)
					{
						int exp = in_sqlda.sqlvar[i].sqlscale*(-1);
						multiplier = (long)System.Math.Pow(10, exp);
					}

					switch (in_sqlda.sqlvar[i].sqltype & ~1)
					{
						case GdsCodes.SQL_TEXT:
						case GdsCodes.SQL_VARYING:							
							in_sqlda.sqlvar[i].sqldata = 
encoding.GetBytes((string)parameters[parameterName].Value);
							in_sqlda.sqlvar[i].sqllen  = 
encoding.GetByteCount((string)parameters[parameterName].Value);
							break;

						case GdsCodes.SQL_SHORT:
							if (in_sqlda.sqlvar[i].sqlscale < 0)
							{
								decimal paramValue = 
Convert.ToDecimal(parameters[parameterName].Value.ToString());
								in_sqlda.sqlvar[i].sqldata =
									Convert.ToInt16(paramValue*multiplier);
							}
							else
							{										
								in_sqlda.sqlvar[i].sqldata = 
Convert.ToInt16(parameters[parameterName].Value.ToString());
							}
							break;

						case GdsCodes.SQL_LONG:
							if (in_sqlda.sqlvar[i].sqlscale < 0)
							{
								decimal paramValue = 
Convert.ToDecimal(parameters[parameterName].Value.ToString());
								in_sqlda.sqlvar[i].sqldata =
									Convert.ToInt32(paramValue*multiplier);
							}
							else
							{
								in_sqlda.sqlvar[i].sqldata = 
Convert.ToInt32(parameters[parameterName].Value.ToString());
							}
							break;

						case GdsCodes.SQL_FLOAT:
							in_sqlda.sqlvar[i].sqldata = 
float.Parse(parameters[parameterName].Value.ToString());
							break;

						case GdsCodes.SQL_DOUBLE:
						case GdsCodes.SQL_D_FLOAT:
							in_sqlda.sqlvar[i].sqldata =
								Convert.ToDouble(parameters[parameterName].Value.ToString());
							break;

						case GdsCodes.SQL_BLOB:
							if (in_sqlda.sqlvar[i].sqlsubtype == 1)
							{
								FbClob clob = new FbClob(Connection, Transaction);
								in_sqlda.sqlvar[i].sqldata = clob.Write(
									Convert.ToString(parameters[parameterName].Value));
							}
							else
							{
								FbBlob blob = new FbBlob(Connection, Transaction);
								in_sqlda.sqlvar[i].sqldata = 
blob.Write((byte[])parameters[parameterName].Value);
							}

							break;

						case GdsCodes.SQL_ARRAY:
							// TODO: Write array data
							break;

						case GdsCodes.SQL_QUAD:
						case GdsCodes.SQL_INT64:
							if (in_sqlda.sqlvar[i].sqlscale < 0)
							{
								decimal paramValue = 
Convert.ToDecimal(parameters[parameterName].Value.ToString());
								in_sqlda.sqlvar[i].sqldata =
									Convert.ToInt64(paramValue*multiplier);
							}
							else
							{										
								in_sqlda.sqlvar[i].sqldata = 
Convert.ToInt64(parameters[parameterName].Value.ToString());
							}
							break;

						case GdsCodes.SQL_TIMESTAMP:
						case GdsCodes.SQL_TYPE_TIME:			
						case GdsCodes.SQL_TYPE_DATE:				
							in_sqlda.sqlvar[i].sqldata = 
DateTime.Parse(parameters[parameterName].Value.ToString());
							break;

						default:
							throw new NotSupportedException("Unknown data type");
					}							
				}
			}

			return in_sqlda;
		}

		/// <summary>
		/// Format the Command Text string and subst the parameters names by ?
		/// </summary>
		private string ParseCommandText(string commandText)
		{
			string		sqlText			= commandText;

			if(Parameters.Count != 0)
			{
				foreach(FbParameter parameter in parameters)
				{
					/* If ParameterName Starts with "@" replace
					 * ParameterName with ?
					 */
					if (parameter.ParameterName.StartsWith("@"))
					{
						sqlText = sqlText.Replace(parameter.ParameterName, "?");
					}
				}
			}

			return sqlText;
		}




Best regards
Carlos Guzmán Álvarez
Vigo-Spain

Re: [Firebird-net-provider] build problem...

[<car...@te...>]

Hello:

> Thanx for your builds, they are working... It looks that i downloaded it 
>  in bad time... now is everything ok...

Ok :)




Best regards
Carlos Guzmán Álvarez
Vigo-Spain

Re: [Firebird-net-provider] more commands

[Pavol S. <pa...@st...>]

Carlos Guzm=E1n =C1lvarez wrote:

> Hello:
>
>> can i use 2 fbCommands with one fbConnection and one fbTransaction
>> or
>> have i create separate connection or transaction for each fbCommand???
>>
>> this example throws exception:
>> FbConnection dbConn =3D new FbConnection(fConnString);
>> try {
>>     dbConn.Open();
>>                FbTransaction dbTran =3D dbConn.BeginTransaction();
>>     if (fID < 0) {
>>         string genQuery =3D "SELECT GEN_ID(GENARTICLEID, 1) FROM=20
>> RDB$DATABASE";
>>         FbCommand dbCmd2 =3D new FbCommand(genQuery, dbConn, dbTran);
>>         dbCmd2.ExecuteScalar();
>>     }
>>     FbCommand dbCmd =3D new FbCommand(query, dbConn, dbTran);
>>            dbCmd.Prepare();
>>     dbCmd.Parameters.Add("xID", fID);
>>     dbCmd.ExecuteNonQuery();
>>     dbTran.Commit();
>> }
>> finally {
>>     dbConn.Close();
>> }
>
>
>
> I going to see it , one question are you testing with CVS sources?? In=20
> older versions there a error when using ExecutingScalar that is fixed i=
n
> CVS.
>
i think, that ExecuteSclalar is working fine , but when i use same=20
connection and transaction for two different commands there is=20
exception, that transactions in trans and conn are different... I will=20
try it with your build... Again thanx for that...

by the way... I wan to help you improve helps, so i will send you=20
modified xml files...
and you said that you implemented named parameters??? I was looking for=20
that in sources yesterday but i didnt find it :(

p.

[Firebird-net-provider] build problem...

[Pavol S. <pa...@st...>]

Thanx for your builds, they are working... It looks that i downloaded it 
  in bad time... now is everything ok...

again thanx...

p.

Re: [Firebird-net-provider] more commands

[<car...@te...>]

Hello:

> can i use 2 fbCommands with one fbConnection and one fbTransaction
> or
> have i create separate connection or transaction for each fbCommand???
> 
> this example throws exception:
> FbConnection dbConn = new FbConnection(fConnString);
> try {
>     dbConn.Open();
>            
>     FbTransaction dbTran = dbConn.BeginTransaction();
>     if (fID < 0) {
>         string genQuery = "SELECT GEN_ID(GENARTICLEID, 1) FROM 
> RDB$DATABASE";
>         FbCommand dbCmd2 = new FbCommand(genQuery, dbConn, dbTran);
>         dbCmd2.ExecuteScalar();
>     }
>     FbCommand dbCmd = new FbCommand(query, dbConn, dbTran);
>        
>     dbCmd.Prepare();
>     dbCmd.Parameters.Add("xID", fID);
>     dbCmd.ExecuteNonQuery();
>     dbTran.Commit();
> }
> finally {
>     dbConn.Close();
> }


I going to see it , one question are you testing with CVS sources?? In 
older versions there a error when using ExecutingScalar that is fixed in
CVS.



Best regards
Carlos Guzmán Álvarez
Vigo-Spain

Re: [Firebird-net-provider] question ExecuteScalar

[<car...@te...>]

Hello:

> string genQuery = "SELECT GEN_ID(GENARTICLEID, 1) FROM RDB$DATABASE";
> FbCommand dbCmd2 = new FbCommand(genQuery, dbConn, dbTran);
> int fID = dbCmd2.ExecuteScalar(); <- ?????? cast to (int) is not working


Test with this:

	int fID = Convert.ToInt32(dbCmd2.ExecuteScalar());



Best regards
Carlos Guzmán Álvarez
Vigo-Spain