`
dengbaoleng
  • 浏览: 1124996 次
文章分类
社区版块
存档分类
最新评论

C#操作firebird数据库大全

 
阅读更多

Download firebird下:http://www.firebirdsql.org/en/downloads/

FAQ:

Can I use a relative path to the database in the connection string?

Yes. It can be relative to the working directory.

How many concurrent connections can you open using the embedded Firebird?

It's not limited. However, only one application can open a database at a time. The application can be a regular Firebird

server or your application using the fbembed.dll.

What files are required to use the embedded Firebird?

The following files are required:

·fbembed.dll (the embedded Firebird itself)

·FirebirdSql.Data.Firebird.dll (the Firebird ADO.NET Provider assembly)

The following files are recommended:

·firebird.msg (friendly error messages)

The following files are optional:

·aliases.conf

·firebird.conf

·ib_util.dll

·intl/fbintl.dll

·udf/fb_udf.dll

·udf/ib_udf.dll

Where should I put the embedded Firebird files in my application?

Put them in the working folder of your application. Typically it will be the directory where your .exe file lies.

Connect to a embedded database

Firebird ADO.NET provider supports a new class (FbConnectionStringBuilder) that makes the work with connection strings much easier:

You can create a connection string by specifying the properties item by item:

FbConnectionStringBuilder Properties Overview

FbConnectionStringBuilder Property

Type

Description

ConnectionLifeTime

System.Int64

When a connection is returned to the pool, its creation time is compared with the current time, and the connection is destroyed if that time span (in seconds) exceeds the value specified by connection lifetime.

ConnectionString

System.String

Here you can get or set the entire connection string.

ConnectionTimeout

System.Int32

The time to wait while trying to establish a connection before terminating the attempt and generating an error.

Database

System.String

The database path to establish the connection. Can be relative to the server executable or fbembed.dll (embedded Firebird).

DataSource

System.String

The server name for establish the connection.

Dialect

System.Byte

Database dialect (1 or 3). Use 3 unless you want specifically use dialect 1 for backwards compatiblity.

FetchSize

System.Int32

Indicates the number of rows that will be fetched at the same time onReadcalls into the internal row buffer.

Charset

System.String

Connection character set.

IsolationLevel

System.Data.IsolationLevel

The maximum number of connections allowed in the pool.

MaxPoolSize

System.Int32

The maximun pool size.

MinPoolSize

System.Int32

The minimum number of connections allowed in the pool.

PacketSize

System.Int16

The size (in bytes) of network packets used to communicate with an instance of Firebird Server.

Password

System.String

The password for the Firebird user account.

Pooling

System.Boolean

Whentrue, theFbConnectionobject is drawn from the appropriate pool, or if necessary, is created and added to the appropriate pool.

Port

System.Int32

The port number in the server for establish the connection

Role

System.String

The user role name.

ServerType

System.Int32

When0the provider will use the GDS implementation used for connections to Firebird Super or Classic servers, when1the provider will use the GDS implementation used for connections to the Firebird embedded server.

UserID

System.String

The firebird User account for login.

·Connection String CharsetkeywordParameters:

Firebird Charset

Description

ASCII

American Standard Code for Information Interchange.

BIG_5

Big5, Traditional Chinese.

DOS437

MS-DOSUnited States,Australia,New Zealand,South Africa.

DOS850

MS-DOS Latin-1.

DOS860

MS-DOS Portugues.

DOS861

MS-DOS Icelandic.

DOS863

MS-DOS Canadian French.

DOS865

MS-DOS Nordic.

EUCJ_0208

JIS X 0201, 0208, 0212, EUC encoding, Japanese.

GB_2312

GB2312, EUC encoding, Simplified Chinese.

ISO8859_1

ISO 8859-1, Latin alphabet No. 1.

ISO8859_2

ISO 8859-2, Latin alphabet No. 2.

KSC_5601

Windows Korean.

ISO2022-JP

Windows Japanese.

SJIS_0208

Japanese (Shift-JIS)

UNICODE_FSS

Eight-bit Unicode Transformation Format.

WIN1250

Windows Eastern European.

WIN1251

Windows Cyrillic.

WIN1252

Windows Latin-1.

WIN1253

Windows Greek.

WIN1254

Windows Turkish.

WIN1254

Windows Hebrew.

Arabic

Windows Turkish.

WIN1257

Windows Baltic.

Connection Pooling

Firebird ADO.NET provider supports connection pooling. By default, connection pooling is turned on. That means that when you call Close method on FbConnection instance the connection to the server is not closed but is returned to the pool.

Connection pooling is useful especially for Web applications. Each page request would otherwise need to open a new connection and that can be time expensive.

You can modify the behavior of connection pooling in the connection string by using these parameters:

Parameter

Default value

Description

Pooling

true

Enables or disables connection pooling for this connection. If it is false, a new connection will always be opened.

MinPoolSize

0

The minimum connections that are always open in the pool

MaxPoolSize

100

The maximum connections that will be in the pool.

Connection Lifetime

0

How long should the connection remain in the pool (in seconds) after its creation. It is checked when the connection is returned to the pool. 0 means that the connection never expires.

Since version 1.7 of the Firebird ADO.NET Provider you can check the number of connections in the pool usingFbConnection.GetPooledConnectionCount().

You can explicitly clear a pool usingFbConnection.ClearPool()or all pools usingFbConnection.ClearAllPools().

Things to remember:

· The connection pools are created per connection string. If you modify the connection string a new connection (and a pool) will be created.

· The connection is returned to the pool when callingClose()method ofFbConnection.

· When you use connection pooling it is better to open the connection as late as possible and close as soon as possible.


Create a New Database From an SQL Script

The following method will create a new database from an SQL script. It uses theembedded Firebirdbut you can switch to a standalone server by changing the connection string. It requires Firebird ADO.NET Provider 1.7a.



Using FbDataAdapter to Fill a Dataset

TEST.SQL Script (Testing Database)

Run the following script to create the testing database. It creates a simple table called MYTABLE with a single row.




Readingvalues

You can fill an untyped DataSet using the following code:

Firebird and .NET Framework Data Types Mapping

Here is the mapping of Firebird data types to .NET Framework data types.

Use the specified FbDataReader method to read the data from a given column.

Firebird type

.NET type

FbDataReader method

Example

BIGINT

System.Int64

GetInt64()

BIGINT Reading Example (C#)

BLOB

System.Byte[]

GetBytes()

BLOB Reading Example (C#)

BLOB SUB_TYPE 1

System.String

GetString()

BLOB SUB_TYPE 1 Reading Example (C#)

CHAR

System.String

GetString()

CHAR Reading Example (C#)

DATE

System.DateTime

GetDateTime()

DATE Reading Example (C#)

DECIMAL

System.Decimal

GetDecimal()

DECIMAL Reading Example (C#)

DOUBLE PRECISION

System.Double

GetDouble()

DOUBLE PRECISION Reading Example (C#)

FLOAT

System.Float

GetFloat()

FLOAT Reading Example (C#)

INTEGER

System.Int32

GetInt32()

INTEGER Reading Example (C#)

NUMERIC

System.Decimal

GetDecimal()

NUMERIC Reading Example (C#)

SMALLINT

System.Int16

GetInt16()

SMALLINT Reading Example (C#)

TIME

System.DateTime

GetDateTime()

TIME Reading Example (C#)

TIMESTAMP

System.DateTime

GetDateTime()

TIMESTAMP Reading Example (C#)

VARCHAR

System.String

GetString()

VARCHAR Reading Example (C#)

BIGINT Reading Example (C#)

TEST.SQL Script (Testing Database)

Run the following script to create the testing database. It creates a simple table called MYTABLE with a single row.


Readingvalues

The following sample prints the values from the column "VAL" to console:

BLOB Reading Example (C#)

TEST.SQL Script (Testing Database)

Run the following script to create the testing database. It creates a simple table called MYTABLE with a single row:

Readingvalues

Because it is not possible to insert binary BLOB value in the SQL script we need to insert it in the code before reading.

The following sample prints the values from the column "VAL" to console:

BLOB SUB_TYPE 1 Reading Example (C#)

TEST.SQL Script (Testing Database)

Run the following script to create the testing database. It creates a simple table called MYTABLE with a single row.

CREATE TABLE MYTABLE (
IDINTEGER,
VALBLOB SUB_TYPE 1 SEGMENT SIZE 80
);
INSERT INTO MYTABLE (ID, VAL) VALUES (1, '123');

Readingvalues

The following sample prints the values from the column "VAL" to console:

CHAR Reading Example (C#)

TEST.SQL Script (Testing Database)

Run the following script to create the testing database. It creates a simple table called MYTABLE with a single row.

CREATE TABLE MYTABLE (
IDINTEGER,
VALCHAR(20)
);
INSERT INTO MYTABLE (ID, VAL) VALUES (1, '123');

Readingvalues

The following sample prints the values from the column "VAL" to console:

DATE Reading Example (C#)

TEST.SQL Script (Testing Database)

Run the following script to create the testing database. It creates a simple table called MYTABLE with a single row.

CREATE TABLE MYTABLE (
IDINTEGER,
VALDATE
);
INSERT INTO MYTABLE (ID, VAL) VALUES (1, '2005-10-01');

Readingvalues

The following sample prints the values from the column "VAL" to console:

DECIMALReadingExample (C#)

TEST.SQL Script (Testing Database)

Run the following script to create the testing database. It creates a simple table called MYTABLE with a single row.

CREATE TABLE MYTABLE (
IDINTEGER,
VALDECIMAL(15,2)
);
INSERT INTO MYTABLE (ID, VAL) VALUES (1, 123.2);

Readingvalues

The following sample prints the values from the column "VAL" to console:



DOUBLE PRECISION Reading Example (C#)

TEST.SQL Script (Testing Database)

Run the following script to create the testing database. It creates a simple table called MYTABLE with a single row.

CREATE TABLE MYTABLE (
IDINTEGER,
VALDOUBLE PRECISION
);
INSERT INTO MYTABLE (ID, VAL) VALUES (1, 123.124);

Readingvalues

The following sample prints the values from the column "VAL" to console:



FLOAT Reading Example (C#)

TEST.SQL Script (Testing Database)

Run the following script to create the testing database. It creates a simple table called MYTABLE with a single row.

CREATE TABLE MYTABLE (
IDINTEGER,
VALFLOAT
);
INSERT INTO MYTABLE (ID, VAL) VALUES (1, 123.124);

Readingvalues

The following sample prints the values from the column "VAL" to console:



INTEGER Reading Example (C#)

TEST.SQL Script (Testing Database)

Run the following script to create the testing database. It creates a simple table called MYTABLE with a single row.

CREATE TABLE MYTABLE (
IDINTEGER,
VALINTEGER
);
INSERT INTO MYTABLE (ID, VAL) VALUES (1, 123);

Readingvalues

The following sample prints the values from the column "VAL" to console:


NUMERIC Reading Example (C#)

TEST.SQL Script (Testing Database)

Run the following script to create the testing database. It creates a simple table called MYTABLE with a single row.

CREATE TABLE MYTABLE (
IDINTEGER,
VALNUMERIC(15,2)
);
INSERT INTO MYTABLE (ID, VAL) VALUES (1, 123.2);

Readingvalues

The following sample prints the values from the column "VAL" to console:


SMALLINT Reading Example (C#)

TEST.SQL Script (Testing Database)

Run the following script to create the testing database. It creates a simple table called MYTABLE with a single row.

CREATE TABLE MYTABLE (
IDINTEGER,
VALSMALLINT
);
INSERT INTO MYTABLE (ID, VAL) VALUES (1, 123);

Readingvalues

The following sample prints the values from the column "VAL" to console:



TIME Reading Example (C#)

TEST.SQL Script (Testing Database)

Run the following script to create the testing database. It creates a simple table called MYTABLE with a single row.

CREATE TABLE MYTABLE (
IDINTEGER,
VALTIME
);
INSERT INTO MYTABLE (ID, VAL) VALUES (1, '00:01:00');

Readingvalues

The following sample prints the values from the column "VAL" to console:



TIMESTAMP Reading Example (C#)

TEST.SQL Script (Testing Database)

Run the following script to create the testing database. It creates a simple table called MYTABLE with a single row.

CREATE TABLE MYTABLE (
IDINTEGER,
VALTIMESTAMP
);
INSERT INTO MYTABLE (ID, VAL) VALUES (1, '2005-11-15 00:00:00');

Readingvalues

The following sample prints the values from the column "VAL" to console:

VARCHAR Reading Example (C#)

TEST.SQL Script (Testing Database)

Run the following script to create the testing database. It creates a simple table called MYTABLE with a single row.

CREATE TABLE MYTABLE (
IDINTEGER,
VALVARCHAR(20)
);
INSERT INTO MYTABLE (ID, VAL) VALUES (1, '123');

Readingvalues

The following sample prints the values from the column "VAL" to console:



Transaction Isolation Levels

Every database operation in Firebird runs inside atransaction. The transactions have these features: Atomicity, Consistency, Isolation, Durability (read more aboutACID). Let's talk a bit about theisolationfeature.

Isolation says that the transaction shouldn't interfere with other transactions. Achieving full isolation would mean serializing the transactions and that would slow down the database. Therefore there arefour transaction isolation levelsdefined and every of them represents a different balance between isolation and performance:

· Read uncommitted

· Read committed

· Repeatable read

· Serializable

Firebird doesn't match the standard (SQL92) isolation levels exactly. The following table compares the isolation levels that are supported by Firebird ADO.NET with the SQL92 standard.

Firebird ADO.NET Provider IsolationLevel

Firebird Isolation Level

Corresponding SQL92 Isolation Level

IsolationLevel.ReadUncommitted

READ COMMITTED
RECORD_VERSION

Read Committed

IsolationLevel.ReadCommitted (default)

READ COMMITTED
NO RECORD_VERSION

Read Committed

IsolationLevel.RepeatableRead

SNAPSHOT/CONCURRENCY

Repeatable Read

IsolationLevel.Serializable

SNAPSHOT TABLE STABILITY/CONSISTENCY

Serializable

All transactions have these additional Firebird transaction options set:

· WAIT - when using READ COMMITTED isolation level it waits for uncommitted transactions to finish before reading a row

· READ WRITE - allows data modifications

You can use the predefined isolation levels by callingpublic FbTransaction BeginTransaction(IsolationLevel)or you can tune the transaction options more precisely by using this BeginTransaction overload:public FbTransaction BeginTransaction(FbTransactionOptions).

IsolationLevel.ReadUncommitted

ANSI/ISO Read Uncommitted isolation level is not supported by Firebird. IsolationLevel.ReadUncommitted behaves like ReadCommitted but it returns the latest committed version of a rowand ignores any other (uncommitted) versions.

Reading

What it reads

The latest committed version of a row and ignores any other (uncommitted) versions.

Dirty reads

No

Phantom data

Possible

Nonrepeatable reads

Possible

Writing

Modification of data modified by other transactions since this transaction started

No

Other transactions can modify data read by this transaction

Yes

Usage

Suitable for

Short transactions that modify data.

IsolationLevel.ReadCommitted

Reading

What it reads

Waits for uncommitted transactions modifyinga row (to be either committed or rolled back).

Dirty reads

No

Phantom data

Possible

Nonrepeatable reads

Possible

Writing

Modification of data modified by other transactions since this transaction started

No

Other transactions can modify data read by this transaction

Yes

Usage

Suitable for

Short transactions that modify data.

IsolationLevel.RepeatableRead

Reading

What it reads

When the transaction starts, a snapshot of the whole database is made. The transaction reads from that snapshot.

Dirty reads

No

Phantom data

No

Nonrepeatable reads

Possible

Writing

Modification of data modified by other transactions since this transaction started

No

Other transactions can modify data read by this transaction

Yes

Usage

Suitable for

Long transactions that require lots of reading and stable data view (reports).

IsolationLevel.Serializable

Reading

What it reads

When the transaction starts, a snapshot of the whole database is made. The transaction reads from that snapshot.

Dirty reads

No

Phantom data

No

Nonrepeatable reads

Possible

Writing

Modification of data modified by other transactions since this transaction started

No

Other transactions can modify data read by this transaction

No. All tables that the transaction has read from are locked. Other transactions are prevented from writing to that tables.

Usage

Suitable for

Long transactions that require exclusive access to a table.

Detecting Firebird Server Installation

Each Firebird instance creates a REG_SZ value in the registry under the key

HKEY_LOCAL_MACHINE/SOFTWARE/Firebird Project/Firebird Server/Instances

The name for the default instance is "DefaultInstance". The value data contains the server root directory (e.g. "C:/Program Files/Firebird/Firebird_1_5/"). You can detect the server settings by reading firebird.conf (e.g. the TCP port) and aliases.conf (aliased databases).

Fulltext Search in Firebird

Firebird doesn't support fulltext search. You need to rely on third party tools. That seems odd, but it doesn't have to be so bad as it looks at first.

I am usingDotLucene. It is an open source .NET library (ported from Java) that can index any data (structured or unstructed) that you are able to convert to raw text.

On a server, it is no problem to store the index in a separate directory (you can also load it to RAM to make your searches super fast - if you have enough RAM, of course). In a desktop application, it might be usefulto store the index in a Firebird database.

For example: MySQL fulltext search has these drawbacks (compared to DotLucene):

.You can use it only in MyISAM tables (i.e. no transactions)
.You can't browse the index
.You need to store transformed text in the DB (i.e. HTML without tags)
.It doesn't support highlighting of the query words in the result
.You will hardly modify the sources to do custom changes
.The license doesn't allow to use it in commercial application for free
.It isreported to be slowon large data sets


Beginners: Stored Procedure Call Example (Non-query)

To call a stored procedure (that doesn't have return values) using Firebird ADO.NET Provider you can use the following code:

Beginners: Filling a DataSet Using a Stored Procedure

You can fill an untyped DataSet using the following code:

DataSet ds = new DataSet();

FbDataAdapter da = new FbDataAdapter("SELECTINGSTOREDPROCEDURE", connectionString);

da.SelectCommand.CommandType = CommandType.StoredProcedure;

da.SelectCommand.Parameters.Add("@firstparam", "firstparamvalue");

da.Fill(ds, "myTableNameInDataset");

This code is using a default transaction, i.e. new transaction is started just for this stored procedure call and is committed immediately after. If you are using a FbDataAdapter to load the data there is no need to open and close the connection.

Using FbConnectionStringBuilder

Firebird ADO.NET provider supports a new class (FbConnectionStringBuilder) that makes the work with connection strings much easier:

1. Parsing a connection string

Create a new instance usingpublic FbConnectionStringBuilder(string)constructor (it takes an existing connection string as a parameter). Then you can read the connection string values by using the FbConnectionStringBuilder properties. Example of reading theDataSource(server address) property:

FbConnectionStringBuilder csb = new FbConnectionStringBuilder("User=SYSDBA;Password=masterkey;Database=SampleDatabase.fdb;DataSource=localhost;Charset=NONE;");

Console.WriteLine(csb.DataSource);

2. Creating a connection string programmatically

You can create a connection string by specifying the properties item by item:

FbConnectionStringBuilder csb = new FbConnectionString();

csb.UserID = "SYSDBA";

csb.Password = "masterkey";

csb.Database = "mydb.fdb";

csb.ServerType = 1; // embedded Firebird


FbConnection c = new FbConnection(csb.ToString());

3. Modifying an existing connection string

You can also modify an existing connection string without complicated parsing. This example switches the type of server to embedded Firebird:

FbConnectionStringBuilder csb = new FbConnectionString(existingConnectionString);

csb.ServerType = 1;


FbConnection c = new FbConnection(csb.ToString());

Creating a Database Programmatically

The maximum you can specify:

Hashtable parameters = new Hashtable();

parameters.Add("User", "SYSDBA");

parameters.Add("Password", "masterkey");

parameters.Add("Database", @"c:/database.fdb");

parameters.Add("Dialect", 3);

parameters.Add("DataSource", "localhost");

parameters.Add("Port", "3050");

parameters.Add("Charset", "NONE");

parameters.Add("PageSize", "8192");

parameters.Add("ForcedWrite", true);

FbConnection.CreateDatabase(parameters);

The miminum you must specify:

Hashtable parameters = new Hashtable();

parameters.Add("User", "SYSDBA");

parameters.Add("Password", "masterkey");

parameters.Add("Database", @"c:/database.fdb");

FbConnection.CreateDatabase(parameters);

Batch SQL/DDL Execution

TheFirebirdSql.Data.Firebird.Isql namespaceprovides classes for batch SQL/DDL execution. This allows you to make such operations as:

    • creating the database structure programmatically
    • updating the database structure automatically
    • automating the maintenance procedure
    • etc.

Running a batch script is not difficult. First you need to parse the script usingFbScript class:

FbScript script = new FbScript("employee.sql");

script.Parse();

The result is stored inscript.Results, one item per command. Now we can useFbBatchExecution classto run the script:

FbConnection c = new FbConnection(@"Database=employee.fdb;User=SYSDBA;Password=masterkey");

c.Open();


FbBatchExecution fbe = new FbBatchExecution(c);

foreach (string cmd in script.Results) {

fbe.SqlStatements.Add(cmd);

}


fbe.Execute();

c.Close();

By default, the transaction is committed after each command. You can disable this by callingfbe.Execute(false).

Here is a complete example that uses an embedded Firebird to create a newemployee.fdbdatabase and initialize the structure using a DDL script. The DDL script is generated from the example employee.fdb (it's modified on two places because of the current bugs in the provider, see the Known Issues below).

On my Athlon XP 2000+ the execution of this batch script takes 0.921 seconds - which is very good I guess.

Migration from MySQL I.

Why you should do that:

    • stored procedures support
    • views support
    • transactions (these are also supported in InnoDB tables in MySQL)
    • friendly open source licensethat allows commercial use and embedding for free
    • embeddable(with a small runtime)
    • hot backup

How to:

1) Autoincrement fields

There are no autoincrement fields in Firebird. You need to use agenerator. It is a server variable that stores the last number used. You need to call it when inserting a new row:

    • in an inserting stored procedure
    • in a trigger

Given that we have a table

CREATE TABLE mytable (

id INTEGER,

mytext VARCHAR(20)

);

the generator would look like this:

CREATE GENERATOR GEN_MYTABLE_ID;

the trigger would look like this:

CREATE TRIGGER MYTABLE_BI FOR MYTABLE

ACTIVE BEFORE INSERT POSITION 0

AS

BEGIN

IF (NEW.ID IS NULL) THEN

NEW.ID = GEN_ID(GEN_MYTABLE_ID,1);

END

and the inserting stored procedure like this:

SET TERM ^ ;

CREATE PROCEDURE SP_MYTABLEINSERT (

MYTEXT VARCHAR(20))

AS

DECLARE VARIABLE ID INTEGER;

BEGIN

ID = GEN_ID(GEN_MYTABLE_ID,1);

INSERT INTO MYTABLE (ID, MYTEXT)VALUES (:ID, :MYTEXT);

END^

SET TERM ; ^

2) NOW()MySQL:

SELECT * FROM mytable WHERE mydate = NOW();

Firebird:

SELECT * FROM mytable WHERE mydate = CURRENT_TIMESTAMP;

There are three special variables for current date and time:

    • CURRENT_TIMESTAMP (date and time, TIMESTAMP type)
    • CURRENT_DATE (date, DATE type)
    • CURRENT_TIME (time, TIME type)

3) LIMIT x, y (return first y rows starting at offset x)

In Firebird it looks like this :

SELECT FIRST y SKIP x * FROM mytable;

LIMIT x (take first 10 rows) looks like this:

SELECT FIRST x * FROM mytable;

Date Functions

Cut and paste date functions. SeeIvan Prenosil's site.

Day of week (American format: week starting on Sunday, Sunday is 0):

EXTRACT(WEEKDAY FROM D)

Day of week (ISO 8601 format: week starting on Monday, Monday is 1):

EXTRACT(WEEKDAY FROM D-1)+1

First day of a month:

D - EXTRACT(DAY FROM D) + 1;

Last day of a month:

D - EXTRACT(DAY FROM D) + 33 - EXTRACT(DAY FROM D - EXTRACT(DAY FROM D) + 32)

Number of days in a month:

EXTRACT(DAY FROM (D - EXTRACT(DAY FROM D) + 32 - EXTRACT(DAY FROM D - EXTRACT(DAY FROM D) + 32))

Week of a year (ISO 8601) stored procedure:

CREATE PROCEDURE YearWeek (D DATE)

RETURNS (WEEK_NO VARCHAR(8)) AS

DECLARE VARIABLE W INTEGER; /* week number */

DECLARE VARIABLE Y INTEGER; /* year the week belongs to */

BEGIN

W = (EXTRACT(YEARDAY FROM D) - EXTRACT(WEEKDAY FROM D-1) + 7) / 7;

Y = EXTRACT(YEAR FROM D);

IF (W=0) THEN BEGIN

Y = Y - 1;

D = D - EXTRACT(YEARDAY FROM D) - 1; /* last day of previous year; D is used as temporary variable here */

W = (EXTRACT(YEARDAY FROM D) - EXTRACT(WEEKDAY FROM D-1) + 7) / 7;

END

ELSE

IF (W=53 AND 4>EXTRACT(WEEKDAY FROM (D - EXTRACT(DAY FROM D) + 31))) THEN BEGIN

Y = Y + 1;

W = 1;

END

/* This is just formatting; you may prefer to make W and Y return parameters instead. */

IF (W<10) THEN WEEK_NO = '0'; ELSE WEEK_NO = '';

WEEK_NO = Y'/'WEEK_NOW;

SUSPEND;

END

Is leap year stored procedure:

CREATE PROCEDURE Is_LeapYear (D DATE) RETURNS (LY INTEGER) AS

BEGIN

IF ( 2 = EXTRACT(MONTH FROM (D - EXTRACT(YEARDAY FROM D) + 59)) ) THEN

LY = 1;/* leap year */

ELSE

LY = 0;/* normal year */

END

Date and Time Calculations

When doing arithmetic operations with TIMESTAMP values, Firebird works with them as decimal number, where

    • the integral fraction is the number of days
    • the decimal fraction is the part of a day

For example:

    • Two days: 2.0
    • One hour: 1.0/24.0
    • One minute: 1.0/1440.0
    • One second: 1.0/86400.0

Extracting the smaller units from a TIMESTAMP value:

    • Number of seconds: VALUE*86400.0
    • Number of minutes: VALUE*1440.0
    • Number of hours: VALUE*24.0

Don't forget thedecimal point in the numbers(e.g. 1.0), otherwise the result will be integer. I spent a lot time debugging a calculation that didn't work because I was dividing by an integer. Since that time, I remember.

Posted by Dan : 2:04 PM

Joining a String with a NULL Value

If you need to join strings with other values in Firebird you need to use || (double pipe) operator:

SELECT 'col1: ' || col1 || ', col2: ' || col2 FROM mytable;

The problem is that if any of col1 or col2 fields are NULL, the whole joined string will be also NULL. If this is not the intended behavior (and it usually is not) you can use COALESCE function to replace the NULL value with another value:

SELECT 'col1: ' || COALESCE(col1, 'NULL') || ', col2: ' || COALESCE(col2, 'NULL') FROM mytable;

EXECUTE STATEMENT

This statement executes SQL or DDL command specified as a (string) parameter. It is a great tool for stored procedures where you can dynamically create the SQL code to be executed.

Syntax:

/* For statements that return nothing or a single row */

EXECUTE STATEMENT statement [INTO variable_list];


/* For statements that return multiple rows */

FOR EXECUTE STATEMENT statement INTO variable_list DO

compound_statement

Example:

SET TERM ;


CREATE PROCEDURE MYPROC

RETURNS (

ID INTEGER,

MYTEXT VARCHAR(20) CHARACTER SET UNICODE_FSS)

AS

BEGIN

FOR EXECUTE STATEMENT 'SELECT id, mytext FROM mytable' INTO :id, :mytext DO

BEGIN

SUSPEND;

END

END

SET TERM ;

参考地址:http://blog.csdn.net/justionpanye/article/details/1824979
分享到:
评论

相关推荐

Global site tag (gtag.js) - Google Analytics