返回> 网站首页
vc++操作firebird数据库
yoours2011-07-14 16:31:38
简介一边听听音乐，一边写写文章。
针对嵌入式版火鸟
#include "Other\ibpp.h"
using namespace std;
//数据库名，根据你的情况更改。
const char* g_szDBName = "d:\\test.fdb";
//服务器名，针对服务器版本。对于嵌入式，它应该是""
const char* g_szServerName = "";
//这里的用户名和密码是FireBird默认值，对于服务器版，用你自己的密码
//对于嵌入式，就是这个（FireBird嵌入式版没有加密功能）。
const char* g_szUserName = "SYSDBA";
const char* g_szPassword = "masterkey";
string extra_params;
CString strPageSize;
GetDlgItem(IDC_COMBO_PAGESIZE)->GetWindowTextA(strPageSize);
//int pagesize = atoi(strPageSize.GetBuffer());
// if (pagesize)
// extra_params << " PAGE_SIZE " << pagesize;
CString strCharset;
GetDlgItem(IDC_COMBO_CHARSET)->GetWindowTextA(strCharset);
string charset = strCharset.GetBuffer();
// if (!charset.empty())
// extra_params << " DEFAULT CHARACTER SET " << charset;
CString strDb;
GetDlgItem(IDC_EDIT_DATABASE_PATH)->GetWindowTextA(strDb);
string DBName = strDb.GetBuffer();
CString strUserName;
GetDlgItem(IDC_EDIT_USERNAME)->GetWindowTextA(strUserName);
string Username = strUserName.GetBuffer();
CString strPassword;
GetDlgItem(IDC_EDIT_PASSWORD)->GetWindowTextA(strPassword);
string DecryptedPassword = strPassword.GetBuffer();
CString strSqlDialect;
GetDlgItem(IDC_COMBO_SQL_DIALECT)->GetWindowTextA(strSqlDialect);
int dialect = atoi(strSqlDialect.GetBuffer());
// 创建数据库
::DeleteFile("d:\\dfd.FDB");
IBPP::Database db1;
db1 = IBPP::DatabaseFactory("", /*DBName*/"d:\\dfd.FDB", Username, DecryptedPassword, "", charset, extra_params);
db1->Create(dialect);
db1->Connect();
IBPP::Transaction tr3 = IBPP::TransactionFactory(db1, IBPP::amWrite, IBPP::ilConcurrency, IBPP::lrWait);
tr3->Start();
IBPP::Statement st1 = IBPP::StatementFactory(db1, tr3);
//建立数据表
st1->Execute("CREATE TABLE TESTTABLE("
" ID INTEGER NOT NULL PRIMARY KEY,"
" RNO VARCHAR(10) NOT NULL UNIQUE,"
" SHIFT VARCHAR(5) NOT NULL"
" CHECK(SHIFT IN('A','B','C','D')),"
" LINE CHAR(20) NOT NULL,"
" SL COMPUTED BY(SHIFT||'.'||LINE),"
" EMP CHAR(20)"
" )"
);
tr3->Commit();
try{
tr3->Start();
//插入数据,华安和小强
st1->Execute(
"INSERT INTO TESTTABLE(ID,RNO,SHIFT,LINE,EMP)"
"VALUES(1,'B9527','B','DAZHA','Hua,An')"
);
st1->Execute(
"INSERT INTO TESTTABLE(ID,RNO,SHIFT,LINE,EMP)"
"VALUES(2,'B9528','B','ZHUANGSI','Xiao,Qiang')"
);
tr3->CommitRetain();
//插入数据,石榴和祝枝山
st1->Execute(
"INSERT INTO TESTTABLE(ID,RNO,SHIFT,LINE,EMP)"
"VALUES(3,'B9525','A','DAZHA','Shi,Liu')"
);
//SHIFT只能是ABCD，看看写X会有什么情况发生
st1->Execute(
"INSERT INTO TESTTABLE(ID,RNO,SHIFT,LINE,EMP)"
"VALUES(4,'B9526','X','DAZHA','Zhu,ZiShang')"
);
tr3->Commit();
}catch(IBPP::SQLException &e){
TRACE("%s", e.what());
tr3->Rollback();
}
db1->Disconnect();
// 以下打开存在的数据库
CString strRole;
GetDlgItem(IDC_EDIT_ROLE)->GetWindowTextA(strRole);
string Role = strRole.GetBuffer();
// 打开数据库
IBPP::Database db = IBPP::DatabaseFactory("", DBName, Username, DecryptedPassword, Role, "", "");
db->Connect();
IBPP::Transaction tr = IBPP::TransactionFactory(db, IBPP::amRead);
tr->Start();
string sql = "select rdb$character_set_name from rdb$database";
IBPP::Statement stmt = IBPP::StatementFactory(db, tr, sql);
stmt->Execute();
std::string s;
if (stmt->Fetch())
{
stmt->Get(1, s);
TRACE("1 %s\r\n", s.c_str());
}
tr->Commit();
int odsM;
int odsMinorM;
int pageSizeM;
int pagesM;
int buffersM;
int sweepM;
bool forcedWritesM;
bool reserveM;
db->Info(&odsM, &odsMinorM, &pageSizeM, &pagesM, &buffersM, &sweepM, &forcedWritesM, &reserveM);
int dialectM = db->Dialect();
// SQL 字符串使用单引号
// Table
sql = "select rdb$relation_name from rdb$relations where (rdb$system_flag = 0 or rdb$system_flag is null) and rdb$view_source is null order by 1";
tr->Start();
stmt = IBPP::StatementFactory(db, tr, sql);
stmt->Execute();
while (stmt->Fetch())
{
if (!stmt->IsNull(1))
{
stmt->Get(1, s);
TRACE("Table: %s\r\n", s.c_str());
}
}
tr->Commit();
// 字段
sql = "SELECT r.RDB$FIELD_NAME AS field_name, r.RDB$DESCRIPTION AS field_description, r.RDB$DEFAULT_value AS field_default_value, r.RDB$NULL_FLAG AS field_not_null_constraint, \
f.RDB$FIELD_LENGTH AS field_length, f.RDB$FIELD_PRECISION AS field_precision, f.RDB$FIELD_SCALE AS field_scale, CASE f.RDB$FIELD_TYPE WHEN 261 THEN 'BLOB'WHEN 14 THEN 'CHAR' \
WHEN 40 THEN 'CSTRING' WHEN 11 THEN 'D_FLOAT' WHEN 27 THEN 'DOUBLE' WHEN 10 THEN 'FLOAT' WHEN 16 THEN 'INT64' WHEN 8 THEN 'INTEGER' WHEN 9 THEN 'QUAD' WHEN 7 THEN 'SMALLINT' \
WHEN 12 THEN 'DATE' WHEN 13 THEN 'TIME' WHEN 35 THEN 'TIMESTAMP' WHEN 37 THEN 'VARCHAR' ELSE 'UNKNOWN' END AS field_type, f.RDB$FIELD_SUB_TYPE AS field_subtype, \
coll.RDB$COLLATION_NAME AS field_collation, cset.RDB$CHARACTER_SET_NAME AS field_charset FROM RDB$RELATION_FIELDS r LEFT JOIN RDB$FIELDS f ON r.RDB$FIELD_SOURCE = f.RDB$FIELD_NAME \
LEFT JOIN RDB$COLLATIONS coll ON f.RDB$COLLATION_ID = coll.RDB$COLLATION_ID LEFT JOIN RDB$CHARACTER_SETS cset ON f.RDB$CHARACTER_SET_ID = cset.RDB$CHARACTER_SET_ID \
WHERE r.RDB$RELATION_NAME = ? ORDER BY r.RDB$FIELD_POSITION";
tr->Start();
stmt = IBPP::StatementFactory(db, tr, sql);
stmt->Set(1, "TESTTABLE");
stmt->Execute();
while (stmt->Fetch())
{
if (!stmt->IsNull(1))
{
stmt->Get(1, s);
string field_type;
stmt->Get("field_type", field_type);
int field_length;
stmt->Get("field_length", field_length);
TRACE(" TESTTABLE Field: %s %s(%d)\r\n", s.c_str(), field_type.c_str(), field_length);
}
}
tr->Commit();
// ADD Field
sql = "ALTER TABLE TESTTABLE ADD ddd Char(10) CHARACTER SET NONE";
//sql = "ALTER TABLE TESTTABLE ADD dfd Integer";
IBPP::Transaction tr1 = IBPP::TransactionFactory(db, IBPP::amWrite);
tr1->Start();
stmt = IBPP::StatementFactory(db, tr1, sql);
stmt->Execute();
TRACE(" TestTable Add Field success!\r\n");
tr1->Commit();
// SysTable
sql = "select rdb$relation_name from rdb$relations where rdb$system_flag = 1 and rdb$view_source is null order by 1";
tr->Start();
stmt = IBPP::StatementFactory(db, tr, sql);
stmt->Execute();
while (stmt->Fetch())
{
if (!stmt->IsNull(1))
{
stmt->Get(1, s);
TRACE("SysTable: %s\r\n", s.c_str());
}
}
tr->Commit();
// View
sql = "select rdb$relation_name from rdb$relations where (rdb$system_flag = 0 or rdb$system_flag is null) and rdb$view_source is not null order by 1";
tr->Start();
stmt = IBPP::StatementFactory(db, tr, sql);
stmt->Execute();
while (stmt->Fetch())
{
if (!stmt->IsNull(1))
{
stmt->Get(1, s);
TRACE("View: %s\r\n", s.c_str());
}
}
tr->Commit();
// Procedures
sql = "select rdb$procedure_name from rdb$procedures where (rdb$system_flag = 0 or rdb$system_flag is null) order by 1";
tr->Start();
stmt = IBPP::StatementFactory(db, tr, sql);
stmt->Execute();
while (stmt->Fetch())
{
if (!stmt->IsNull(1))
{
stmt->Get(1, s);
TRACE("Procedures: %s\r\n", s.c_str());
}
}
tr->Commit();
// Triggers
sql = "select rdb$trigger_name from rdb$triggers where (rdb$system_flag = 0 or rdb$system_flag is null) order by 1";
tr->Start();
stmt = IBPP::StatementFactory(db, tr, sql);
stmt->Execute();
while (stmt->Fetch())
{
if (!stmt->IsNull(1))
{
stmt->Get(1, s);
TRACE("Triggers: %s\r\n", s.c_str());
}
}
tr->Commit();
// Roles
sql = "select rdb$role_name from rdb$roles";
if (odsM > 11 || (odsM == 11 && odsMinorM >= 1))
sql += " where (rdb$system_flag = 0 or rdb$system_flag is null)";
sql += " order by 1";
tr->Start();
stmt = IBPP::StatementFactory(db, tr, sql);
stmt->Execute();
while (stmt->Fetch())
{
if (!stmt->IsNull(1))
{
stmt->Get(1, s);
TRACE("Roles: %s\r\n", s.c_str());
}
}
tr->Commit();
// SystemRoles
if (odsM > 11 || (odsM == 11 && odsMinorM >= 1))
{
sql = "select rdb$role_name from rdb$roles where (rdb$system_flag > 0) order by 1";
tr->Start();
stmt = IBPP::StatementFactory(db, tr, sql);
stmt->Execute();
while (stmt->Fetch())
{
if (!stmt->IsNull(1))
{
stmt->Get(1, s);
TRACE("SystemRoles: %s\r\n", s.c_str());
}
}
tr->Commit();
}
// Domains
sql = "select f.rdb$field_name from rdb$fields f left outer join rdb$types t on f.rdb$field_type=t.rdb$type where t.rdb$field_name='RDB$FIELD_TYPE' and f.rdb$field_name not starting with 'RDB$' order by 1";
tr->Start();
stmt = IBPP::StatementFactory(db, tr, sql);
stmt->Execute();
while (stmt->Fetch())
{
if (!stmt->IsNull(1))
{
stmt->Get(1, s);
TRACE("Domains: %s\r\n", s.c_str());
}
}
tr->Commit();
//Functions
sql = "select rdb$function_name from rdb$functions where (rdb$system_flag = 0 or rdb$system_flag is null) order by 1";
tr->Start();
stmt = IBPP::StatementFactory(db, tr, sql);
stmt->Execute();
while (stmt->Fetch())
{
if (!stmt->IsNull(1))
{
stmt->Get(1, s);
TRACE("Functions: %s\r\n", s.c_str());
}
}
tr->Commit();
//Generators
sql = "select rdb$generator_name from rdb$generators where (rdb$system_flag = 0 or rdb$system_flag is null) order by 1";
tr->Start();
stmt = IBPP::StatementFactory(db, tr, sql);
stmt->Execute();
while (stmt->Fetch())
{
if (!stmt->IsNull(1))
{
stmt->Get(1, s);
TRACE("Generators: %s\r\n", s.c_str());
}
}
tr->Commit();
//Exceptions
sql = "select rdb$exception_name from rdb$exceptions order by 1";
tr->Start();
stmt = IBPP::StatementFactory(db, tr, sql);
stmt->Execute();
while (stmt->Fetch())
{
if (!stmt->IsNull(1))
{
stmt->Get(1, s);
TRACE("Exceptions: %s\r\n", s.c_str());
}
}
tr->Commit();
// User
sql = "SELECT DISTINCT RDB$USER FROM RDB$USER_PRIVILEGES";
tr->Start();
stmt = IBPP::StatementFactory(db, tr, sql);
stmt->Execute();
while (stmt->Fetch())
{
if (!stmt->IsNull(1))
{
stmt->Get(1, s);
TRACE("User: %s\r\n", s.c_str());
}
}
tr->Commit();
TRACE("\r\n");
// 其他信息
int Fetches, Marks, Reads, Writes;
db->Statistics(&Fetches, &Marks, &Reads, &Writes);
TRACE(" Fetches : %d\r\n", Fetches);
TRACE(" Marks : %d\r\n", Marks);
TRACE(" Reads : %d\r\n", Reads);
TRACE(" Writes : %d\r\n", Writes);
TRACE("\r\n");
int Inserts, Updates, Deletes, ReadIdx, ReadSeq;
db->Counts(&Inserts, &Updates, &Deletes, &ReadIdx, &ReadSeq);
TRACE(" Inserts : %d\r\n", Inserts);
TRACE(" Updates : %d\r\n", Updates);
TRACE(" Deletes : %d\r\n", Deletes);
TRACE(" ReadIdx : %d\r\n", ReadIdx);
TRACE(" ReadSeq : %d\r\n", ReadSeq);
db->Disconnect();
TRACE("\r\n");
注意：
FireBird的动态链接库。对于嵌入式版本来说，下载后把压缩包中的fbembed.dll, firebird.conf,firebird.msg,ib_util.dll以 及intl文件夹解压到你的程序目录中。
步骤五：按下F5进行运行就可以了。查找D盘是否存在一个名为Test.FDB的文件。
备注：这里所说的Embedded版不是用在嵌入式下的版本，这里指的是Firebird的单机版，但是可以用在嵌入式下。
实现内容：
1. 创建数据库、表、字段
2. 枚举所有存在的表
3. 枚举所有存在的字段
4. 为其中一个表增加一个字段
5. 增加数据（删除修改sql都差不多）
6. 事物操作
7. 其他部分数据库信息
参考：
flamerobin-0.9.3.2106
https://flamerobin.svn.sourceforge.net/svnroot/flamerobin/trunk
ibpp-2-5-3-1-src.zip
文章评论
1136人参与，0条评论