Free Database: The Relational Databases Blog

Workaround: Using LIMIT parameters in MySQL stored procedures

2010-06-20T10:42:00.000-07:00

It seems that MySQL doesn't accept direct parameter assignment for the LIMIT clause. For example the stored procedure parameters or the local defined variables will generate syntax error if are used directly in the LIMIT clause inside a Store Procedure: SELECT...LIMIT @limit;

MySQL Store Procedure LIMIT Workaround

A possible worksround for this MySQL LIMIT issue is to use the LIMIT containing query like this:

SET @v_limit = v_store_proc_parameter;
SET @v_query = 'SELECT ... FROM ... WHERE ... LIMIT ?`;

PREPARE v_query from @v_query;
EXECUTE v_query USING @v_limit;

Firebird Index key size calculator

2009-10-13T03:59:00.000-07:00

Maximum index key size is 252 bytes (for Firebird <= 1.5) or 1/4 of page size (Firebird 2). So trying to create a composite index using multiple columns might end with a:

"key size too big for index ..."

or

"key size exceeds implementation restriction for index..." errors.

Here is a tool that can help you to determine the real size of your index: Index key size calculator. Here is a backup link (Firebird index key size calculator)

TIP: Please be sure your application can access the "firebird.msg" file in order to be able to get the specific error message. Otherwise you will get a generic exception message.

Create Firebird database from script file by code

2009-09-13T01:07:00.000-07:00

It seems the Firebird embedded engine is not able to create a database by executing a full file script as one statement. If this is what you are trying to achieve, here is a workaround (.NET/C#/ActiveRecord): Create a Firebird database from script file with ActiveRecord

Oracle date functions

2009-05-07T13:33:00.000-07:00

Here is a list of the Oracle Date functions. I had enough problems with the Oracle dates in the past, so I created this list, maybe someone will find it useful.

Oracle SYSDATE function

Returns the current date and time set for the operating system on which the database resides.

Syntax:	SYSDATE
Example:	SELECT SYSDATE FROM dual;

Oracle CURRENT_DATE function

Returns the current date in the session time zone, in a value in the Gregorian calendar of datatype DATE.

Syntax:	CURRENT_DATE
Example:	SELECT CURRENT_DATE FROM dual;

Oracle "+" and "-" (plus/minus) date operators

Add or substract a number of days from a date.

Syntax:	{date} + {integer} {date} - {integer}
Example:	SELECT SYSDATE + 1 FROM dual; SELECT SYSDATE - 1 FROM dual;

Oracle ADD_MONTHS function

Function to add a number of months to a date. This could be rounded to below if the resulting month has fewer days than the month this function is applied to.

Syntax:	ADD_MONTHS
Example:	SELECT add_months(SYSDATE, 2) FROM dual;

Oracle DUMP function

Returns a VARCHAR2 value containing the datatype code, length in bytes, and internal representation of a value.

Syntax:	DUMP({value} [,{return_format}[,{start_position}[,{length}]]]){value}{return_format}{start_position}{length}{value}{return_format}{start_position}{length}
Example:	SELECT add_months(SYSDATE, 2) FROM dual;

Oracle GREATEST function

With the greatest function you can select the date/time that is the highest in a range of date/times.

Syntax:	GREATEST
Example:	SELECT GREATEST(SYSDATE-7, SYSDATE-9, SYSDATE+2)FROM t;

Oracle INTERVAL function

Interval to adjust date-time.

Syntax:	INTERVAL '{integer}' {unit}
Example:	SELECT TO_CHAR(SYSDATE + INTERVAL '10' MINUTE, 'HH:MI:SS') FROM dual;

Oracle LEAST function

With the least function you can select the earliest date/time in a range of date/times.

Syntax:	LEAST({date}, {date}, {date}, ...)
Example:	SELECT LEAST(datecol1, datecol2, datecol3) FROM t;

Oracle LAST_DAY function

Returns the last_day of a month based on the month the passed date is in.

Syntax:	LAST_DAY({date})
Example:	SELECT LAST_DAY(CURRENT_DATE) FROM t;

Oracle LEAST function

With the least function you can select the earliest date/time in a range of date/times.

Syntax:	LEAST({date}, {date}, {date}, ...)
Example:	SELECT LEAST(datecol1, datecol2, datecol3) FROM t;

Oracle MONTHS_BETWEEN function

Returns the number of months between two dates. The number is not rounded.

Syntax:	MONTHS_BETWEEN({latest_date}, {earliest_date})
Example:	SELECT MONTHS_BETWEEN(SYSDATE+365, SYSDATE-365) FROM dual;

Oracle NEXT_DAY function

Date of next specified date following a date NEXT_DAY(, ) Options are SUN, MON, TUE, WED, THU, FRI, and SAT. This can be dependend on NLS_SETTINGS!

Syntax:	NEXT_DAY({date}, {day of the week})
Example:	SELECT NEXT_DAY(SYSDATE, 'FRI') FROM dual;

Oracle ROUND function

Returns date rounded to the unit specified by the format model. If you omit the format, the date is rounded to the nearest day.

Syntax:	ROUND({date_value}, {format})
Example:	SELECT ROUND(TO_DATE('27-OCT-00'),'YEAR') NEW_YEAR FROM dual;

Oracle TRUNC function

Convert a date to the date without time (0:00h).

Syntax:	TRUNC({date_time})
Example:	SELECT TRUNC(SYSDATE);

Embedded Firebird database overview

2009-05-01T09:07:00.000-07:00

I was looking for a small and powerful database in order to fulfil the requirements of my last project. Because the database will be running locally I took the decision to use an embedded database. After a few searches over the Internet and some embedded database comparison reviews, I found Firebird which is actually a powerful but unknown (yet) relational database system. Here are some great features of the Firebird embedded database version:

Runtime size

The size of the runtime is 2 MB in the minimal configuration. There are only two files required (an embedded Firebird runtime DLL and an ADO.NET provider assembly). Even with all the other supporting files it doesn't get over 3 MB.

The facts of having such a small footprint of the runtime makes me even more happy about using the Firebird as an embedded database.

XCOPY deployment

Both the runtime and database file can be deployed by simple copying, no separate installer or component registration is required.

Could it be more simple than this?

Custom filename extensions

The database file (it's just a single file) can have any name and extension and can be located at any place on your hard drive. You can associate the extension with your application. This allows you to use Firebird database as an intelligent storage format.

This is also a feature I lake to have, because my users will create databases on-the-fly but I want to track them by categories using different file extensions. Great!

Migration

When migrating the database to a standalone Firebird server just copy the database file and change a connection string on your client. The migration to a standalone Firebird server couldn't be easier.

Open-source

The license is open-source but commercial friendly. You can bundle embedded Firebird with your application without making your code available.

If you want to know how to user GUIDs with Firebird and ActiveRecord, read this article: ActiveRecord, Firebird and Guid Primary Keys