- 浏览: 1124996 次
文章分类
最新评论
-
624121550:
[b][b][b]引用引用[list]
[*][img][/i ...
android.os.SystemClock类概述 -
whmyahoo:
zjjsjlr 写道求指教,中国地图里各个省份的矢量坐标是怎么 ...
Raphael JS实现的中国省份热点地图 -
zjjsjlr:
求指教,中国地图里各个省份的矢量坐标是怎么获取的啊?
Raphael JS实现的中国省份热点地图 -
wiserdiaosi:
...
js动态给对象加属性 -
su1216:
你的理论有问题,比如你说dip与屏幕density无关,比如你 ...
android——dip与屏幕density无关的解读
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.
ReadingvaluesYou 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 |
Read Committed |
IsolationLevel.ReadCommitted (default) |
READ COMMITTED |
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
参考地址:http://blog.csdn.net/justionpanye/article/details/1824979SET TERM ;
相关推荐
C#实现对火鸟数据库数据的增删改查并与datagridview进行绑定
在VisualStudio中使用EF操作Firebird数据库博文所需的安装软件
C#使用Embedded Firebird数据库示例 Firebird 数据库 内嵌数据库 .net data provider for firebird
C# FireBird 工具类 封装对FireBird数据库的所有操作,例如建立连接,返回DataSet,返回DataReader等
测试环境:vs2008+firebird2.5 C#连接firebird嵌入版数据库实例
用C#写的实现数据库备份与还原的工程(VS2008下可直接运行,低于08的版本可以拷贝代码实现,记得要在COM中添加SQLSMO引用,本例数据库为SuperMarket,请更改为你所需备份的数据库),个人特意将两种方法放在同一工程...
网上整理的c#链接access,sqlserver,oracle,sqlite,firebird数据库的链接类,主要是根据msdn上的sqlhelper改写过来的,文件并非原创,希望能各位高手指点指点。
C#操作firebird数据库,解决了中文路径等诸多问题
Firbird内嵌函数极少,本示例介绍了如何利用ibexpert创建使用UDF的存储过程,并在C#(winform)中调用该存储过程. 打包部署时只需如下: 1.GDS32.DLL (1.5的版本) 2.FirebirdSql.Data.FirebirdClient.dll 3.ib_util.dll ...
这里的版本firebird2.5.8,demo里面包含测试数据库和必要的支持dll,可直接运行
C#连接嵌入式小型数据库firebird,操作数据-附件资源
2. 用数据库类库 DC.CommonDbLiteLib针对SqlServer,MySQL,SQLite,Oracle,ODBC,OleDb,Firebird,PostgreSql,DB2,Informix,SqlServerCe连接进行了封装,一般情况下只需要调用一个IDatabaseInfo接口即可使用,...
demo是C#写的控制台程序,里面有个测试数据库,項目結構比之前那個有優化,demo可以直接運行
包含两个类库,分别为C#对FIREBIRD数据库的增删改查等操作方法的类库 +C#对ACCESS数据库的增删改查等操作方法的类库 使用winform程序对类库进行调用,例子简单,但是类库功能强大,可以直接放在需要的项目中使用 ...
SqlServer, MySql, SQLite, Oracle, ODBC, OleDb, Firebird, PostgreSql, DB2, Informix, SqlServerCe
关于FireBird 数据库的资料的整理 以及在vs里面操作FB数据库的一些知识 附源代码例子
Firebird .NET Data Provider 用于.Net C# 针对 firebird 2.0 和 firebird 2.5 版本的数据库. 两个dll 文件. 下载后去掉 ver2.0 或2.5 初始解压密码123654
比较全面的数据库操作类,C#写的,支持Oracle,Sql Server,MySql,Access,FireBird等数据库,支持ODBC,OLEDB数据库访问方式。
Firebird .NET数据提供程序 更多的 配套 NuGet 版本 资料下载 EntityFramework.Firebird FirebirdSql.EntityFrameworkCore.Firebird 资源 资料下载 问题追踪器 建物