Overview
Examples
Screenshots
Comparisons
Applications
Download
Documentation
Tutorials
Bazaar
Status & Roadmap
FAQ
Authors & License
Forums
Funding Ultimate++
Search on this site
Search in forums












SourceForge.net Logo
Home » Developing U++ » UppHub » Firebird
Firebird [message #33007] Thu, 30 June 2011 06:16 Go to next message
Novo is currently offline  Novo
Messages: 1358
Registered: December 2006
Ultimate Contributor
I made an initial version of a firebird driver for UPP. Below is what it can do at this time. This code was tested on Windows with local connection to firebird 2.1 so far.

#include "firebird/firebird.h"

using namespace Upp;

void TestInt(FBSession& s)
{
	Sql stmt(s);
	stmt.SetStatement(
	    "DROP TABLE test_long_table"
	);
	stmt.Run();
                  
	// Data definition statement.
	stmt.SetStatement(
		" CREATE TABLE test_long_table( \n"
		"   id integer, \n"
		"   first_field integer DEFAULT NULL, \n"
		"   second_field integer DEFAULT NULL \n"
		")"
	);
	stmt.Run();

	stmt.SetStatement(
		" INSERT INTO test_long_table \n"
		" VALUES(?, ?, ?)"
	);
		
	for (int i = 0; i < 100; ++i)
	{
		stmt.SetParam(0, i);
		stmt.SetParam(1, i * 10);
		stmt.SetParam(2, i * 100);
		stmt.RunX();
	}

	stmt.SetStatement(
		" SET TRANSACTION WAIT ISOLATION LEVEL READ COMMITTED"

	);
	stmt.Run();

	int _0 = -1;
	int _1 = -1;
	int _2 = -1;

	Ref r0(_0);
	Ref r1(_1);
	Ref r2(_2);
	int i;

	// SELECT without parameters.
	stmt.SetStatement(
		" SELECT * FROM test_long_table ORDER BY id"
	);

	stmt.Run();
	i = 0;
	while (stmt.Fetch())
	{
		stmt.GetColumn(0, r0);
		stmt.GetColumn(1, r1);
		stmt.GetColumn(2, r2);

		ASSERT(_0 == i);
		ASSERT(_1 == i * 10);
		ASSERT(_2 == i * 100);
		++i;
	}
	ASSERT(i == 100);
	ASSERT(!stmt.Fetch());

	// Rerun SELECT.
	stmt.Run();
	i = 0;
	while (stmt.Fetch())
	{
		ASSERT(stmt[0] == i);
		ASSERT(stmt[1] == i * 10);
		ASSERT(stmt[2] == i * 100);
		++i;
	}
	ASSERT(i == 100);
	ASSERT(!stmt.Fetch());

	// SELECT with parameters.
	stmt.SetStatement(
		" SELECT * FROM test_long_table WHERE id < ? ORDER BY id"
	);
	stmt.SetParam(0, 50);

	stmt.Run();
	i = 0;
	while (stmt.Fetch())
	{
		stmt.GetColumn(0, r0);
		stmt.GetColumn(1, r1);
		stmt.GetColumn(2, r2);

		ASSERT(_0 == i);
		ASSERT(_1 == i * 10);
		ASSERT(_2 == i * 100);
		++i;
	}
	ASSERT(i == 50);
	ASSERT(!stmt.Fetch());

	// Rerun SELECT with parameters.
	stmt.SetParam(0, 25);

	stmt.Run();
	i = 0;
	while (stmt.Fetch())
	{
		stmt.GetColumn(0, r0);
		stmt.GetColumn(1, r1);
		stmt.GetColumn(2, r2);

		ASSERT(_0 == i);
		ASSERT(_1 == i * 10);
		ASSERT(_2 == i * 100);
		++i;
	}
	ASSERT(i == 25);
	ASSERT(!stmt.Fetch());
}

CONSOLE_APP_MAIN
{
	const Vector<String>& cmd_line = CommandLine();
	if (cmd_line.GetCount() > 0 && FileExists(cmd_line[0]))
	{
		FBSession s;
		s.Connect(
			cmd_line[0],
			NULL,
			"SYSDBA",
			"masterkey"
			);

		TestInt(s);
		TRANSACTION(s) {
			TestInt(s);
		}
	}
}



Actually, it can do more than that. It should support all data types except of blobs and arrays.

It also introduces an interesting macro called TRANSACTION. An example:
FBSession s;

TRANSACTION(s) {
	TestInt(s);
}


If code inside of a TRANSACTION block successfully reaches end of the block, then transaction will be committed. If for some reason code jumps out of the block (in case of an exception, for example), then transaction will be rolled back.

In this driver I tried to simulate transactional behavior similar to one found in ORACLE. The way firebird itself deals with transactions is very different.

I'd like to know you opinion about this driver, design of code, transactional behavior, e.t.c.

TIA


Regards,
Novo

[Updated on: Tue, 30 August 2011 05:43]

Report message to a moderator

Re: Firebird [message #33008 is a reply to message #33007] Thu, 30 June 2011 08:20 Go to previous messageGo to next message
mirek is currently offline  mirek
Messages: 13975
Registered: November 2005
Ultimate Member
So far looks good. Fill the rest of methods and we could add this as plugin/firebird to uppsrc.

I am not quite sure about TRANSACTION macro.

Mirek
Re: Firebird [message #33010 is a reply to message #33008] Thu, 30 June 2011 08:44 Go to previous messageGo to next message
unodgs is currently offline  unodgs
Messages: 1366
Registered: November 2005
Location: Poland
Ultimate Contributor

TRANSACTION macro looks good. I like it. The only problem is how to add custom code to handle rollback situation? And does your macro breaks immediately after first error or run queries to the last and then do rollback? Normally I use try/catch scenario.
Sql q;
try {
   q.Begin()
   ...
   q.Commit()
}
catch(SqlExc e)
{
   q.Rollback();
   ...
}

Re: Firebird [message #33037 is a reply to message #33008] Mon, 04 July 2011 06:44 Go to previous messageGo to next message
Novo is currently offline  Novo
Messages: 1358
Registered: December 2006
Ultimate Contributor
mirek wrote on Thu, 30 June 2011 02:20

So far looks good. Fill the rest of methods and we could add this as plugin/firebird to uppsrc.

I am not quite sure about TRANSACTION macro.

Mirek


I added support for almost everything except of blob. I'm also going to develop a bunch of tests for all this stuff.

IMHO, TRANSACTION macro is very convenient to use. You just declare a block of code as running inside of a transaction. No extra variable declarations, no complexity related to exception handling, no worries about jumping out of a block because of business logic.

This macro is similar by design to the INTERLOCKED_ macro. They serve similar needs in different domains.


Regards,
Novo
Re: Firebird [message #33038 is a reply to message #33010] Mon, 04 July 2011 06:50 Go to previous messageGo to next message
Novo is currently offline  Novo
Messages: 1358
Registered: December 2006
Ultimate Contributor
unodgs wrote on Thu, 30 June 2011 02:44

TRANSACTION macro looks good. I like it. The only problem is how to add custom code to handle rollback situation? And does your macro breaks immediately after first error or run queries to the last and then do rollback? Normally I use try/catch scenario.
Sql q;
try {
   q.Begin()
   ...
   q.Commit()
}
catch(SqlExc e)
{
   q.Rollback();
   ...
}




Well, there is no way to add custom rollback code using this macro. But because this macro is very simple, you can implement a similar one using the same pattern. You can take a look at implementation of TRANSACTION_RETAIN in implementation of firebird driver.

B.t.w., your code will look like below.
Sql q;

TRANSACTION(q) {
    ...
}


You still need to catch exception ...
But what will happen with your code if exception is not of type SqlExc? TRANSACTION will take care of this.


Regards,
Novo
Re: Firebird [message #33050 is a reply to message #33007] Tue, 05 July 2011 06:15 Go to previous messageGo to next message
Novo is currently offline  Novo
Messages: 1358
Registered: December 2006
Ultimate Contributor
I updated firebird driver again. Blob seems to be implemented. A lot of stuff needs tests and there are known issues.

I'm also not absolutely sure about type conversion logic.
I, probably, also need to support ValueGen.
I also found OracleBlob and OracleClob in the Oracle8 driver.

Similar classes can be implemented with firebird. Unfortunately, these classes is not a generic solution. Blobs is a part of almost all databases. So, having separate classes, which implement BlockStream interface for each database, doesn't seem to be a perfect solution.

I will appreciate your feedback.

TIA


Regards,
Novo
Re: Firebird [message #33079 is a reply to message #33050] Wed, 06 July 2011 06:07 Go to previous messageGo to next message
Novo is currently offline  Novo
Messages: 1358
Registered: December 2006
Ultimate Contributor
Another update.

At this time I made tests for all data types except of Blob.

Can somebody tell me what else is missing, or not working, or not done well?

TIA


Regards,
Novo
Re: Firebird [message #33104 is a reply to message #33079] Thu, 07 July 2011 06:40 Go to previous messageGo to next message
Novo is currently offline  Novo
Messages: 1358
Registered: December 2006
Ultimate Contributor
Added support for schema. Fixed compilation on Linux.
Tested Blob. It works fine.
The only missing part is a Script Runner. I'm going to add it after this weekend.


Regards,
Novo

[Updated on: Sat, 09 July 2011 06:39]

Report message to a moderator

Re: Firebird [message #33127 is a reply to message #33010] Sat, 09 July 2011 06:49 Go to previous messageGo to next message
Novo is currently offline  Novo
Messages: 1358
Registered: December 2006
Ultimate Contributor
unodgs wrote on Thu, 30 June 2011 02:44

And does your macro breaks immediately after first error or run queries to the last and then do rollback?



TRANSACTION macro will rollback if you don't reach end of a block. This can happen in case of an exception, "break", "return", or "goto" statement. It breaks immediately, the rest of statements in the block won't be executed. "break", "return", and "goto" statements work similar to exception in this case.

Well, this macro is just another trick with scopes.


Regards,
Novo
Re: Firebird [message #33296 is a reply to message #33127] Sat, 23 July 2011 06:22 Go to previous messageGo to next message
Novo is currently offline  Novo
Messages: 1358
Registered: December 2006
Ultimate Contributor
Another update.

Added tests for blobs. Implemented script runner and several other methods. The only missing functionality is multi-level transactions. Taking in the account different transaction model of Firebird I do not want to mess with it.

I think I'm done with it.

Well, after more intensive testing I found bugs ...


Regards,
Novo

[Updated on: Mon, 25 July 2011 05:58]

Report message to a moderator

Re: Firebird [message #33394 is a reply to message #33007] Sun, 31 July 2011 19:02 Go to previous messageGo to next message
Novo is currently offline  Novo
Messages: 1358
Registered: December 2006
Ultimate Contributor
Update.

Fixed known problems, implemented ValueGen for Firebird, added more tests.

At this point I will switch into bugfixing mode.


Regards,
Novo
Re: Firebird [message #33606 is a reply to message #33394] Tue, 30 August 2011 05:40 Go to previous message
Novo is currently offline  Novo
Messages: 1358
Registered: December 2006
Ultimate Contributor
Firebird driver is in bazaar now.

Regards,
Novo

[Updated on: Tue, 30 August 2011 05:41]

Report message to a moderator

Previous Topic: MediaPlayer crash under UBUNTU 11.04
Next Topic: Getting compile on Docking in bazaar
Goto Forum:
  


Current Time: Thu Mar 28 12:20:32 CET 2024

Total time taken to generate the page: 0.02337 seconds