|
#include <Wt/WApplication>
|
|
#include <Wt/WLogger>
|
|
#include <Wt/Dbo/backend/Firebird>
|
|
|
|
#include "Wt/Dbo/Exception"
|
|
|
|
#include <Wt/Dbo/SqlConnection>
|
|
#include <Wt/Dbo/SqlStatement>
|
|
|
|
#include <boost/lexical_cast.hpp>
|
|
#include <iostream>
|
|
#include <vector>
|
|
#include <sstream>
|
|
#include <stdio.h>
|
|
|
|
#include <boost/date_time/posix_time/posix_time.hpp>
|
|
#include <boost/date_time/gregorian/gregorian.hpp>
|
|
#include <boost/date_time/posix_time/time_parsers.hpp>
|
|
|
|
#ifdef WIN32
|
|
#define snprintf _snprintf
|
|
#define strcasecmp _stricmp
|
|
#endif
|
|
|
|
//#define DEBUG(x) x
|
|
#define DEBUG(x)
|
|
|
|
#include <ibpp.h>
|
|
|
|
namespace Wt
|
|
{
|
|
namespace Dbo
|
|
{
|
|
namespace backend {
|
|
using namespace std;
|
|
using namespace Wt::Dbo;
|
|
using namespace Wt;
|
|
using namespace boost::gregorian;
|
|
using namespace boost::posix_time;
|
|
|
|
class FirebirdException : public Wt::Dbo::Exception
|
|
{
|
|
public:
|
|
FirebirdException(const std::string& msg)
|
|
: Wt::Dbo::Exception(msg)
|
|
{ }
|
|
};
|
|
|
|
class FirebirdStatement : public SqlStatement
|
|
{
|
|
public:
|
|
FirebirdStatement(Firebird& conn, const std::string& sql)
|
|
: conn_(conn),
|
|
sql_(convertToNumberedPlaceholders(sql))
|
|
{
|
|
lastId_ = -1;
|
|
row_ = affectedRows_ = 0;
|
|
//result_ = 0;
|
|
|
|
paramValues_ = 0;
|
|
paramTypes_ = paramLengths_ = 0;
|
|
|
|
snprintf(name_, 64, "SQL%p%08X", this, rand());
|
|
|
|
DEBUG(std::cerr << this << " for: " << sql_ << std::endl);
|
|
|
|
IBPP::Transaction tr = conn_.transaction();
|
|
|
|
m_stmt = IBPP::StatementFactory(conn_.connection(), tr);
|
|
|
|
try
|
|
{
|
|
this->m_stmt->Prepare(sql_);
|
|
}
|
|
catch(IBPP::LogicException &e)
|
|
{
|
|
throw FirebirdException(e.what());
|
|
}
|
|
|
|
state_ = Done;
|
|
}
|
|
|
|
virtual ~FirebirdStatement()
|
|
{
|
|
//PQclear(result_);
|
|
if (paramValues_)
|
|
delete[] paramValues_;
|
|
if (paramTypes_)
|
|
delete[] paramTypes_;
|
|
}
|
|
|
|
virtual void reset()
|
|
{
|
|
state_ = Done;
|
|
}
|
|
|
|
virtual void bind(int column, const std::string& value)
|
|
{
|
|
DEBUG(std::cerr << this << " bind " << column << " " << value << std::endl);
|
|
|
|
setValue(column, value);
|
|
}
|
|
|
|
virtual void bind(int column, short value)
|
|
{
|
|
bind(column, static_cast<int>(value));
|
|
}
|
|
|
|
virtual void bind(int column, int value)
|
|
{
|
|
DEBUG(std::cerr << this << " bind " << column << " " << value << std::endl);
|
|
|
|
setValue(column, boost::lexical_cast<std::string>(value));
|
|
}
|
|
|
|
virtual void bind(int column, long long value)
|
|
{
|
|
DEBUG(std::cerr << this << " bind " << column << " " << value << std::endl);
|
|
|
|
setValue(column, boost::lexical_cast<std::string>(value));
|
|
}
|
|
|
|
virtual void bind(int column, float value)
|
|
{
|
|
DEBUG(std::cerr << this << " bind " << column << " " << value << std::endl);
|
|
|
|
setValue(column, boost::lexical_cast<std::string>(value));
|
|
}
|
|
|
|
virtual void bind(int column, double value)
|
|
{
|
|
DEBUG(std::cerr << this << " bind " << column << " " << value << std::endl);
|
|
|
|
setValue(column, boost::lexical_cast<std::string>(value));
|
|
}
|
|
|
|
virtual void bind(int column, const boost::posix_time::time_duration & value)
|
|
{
|
|
DEBUG(std::cerr << this << " bind " << column << " " << boost::posix_time::to_simple_string(value) << std::endl);
|
|
|
|
std::string v = boost::posix_time::to_simple_string(value);
|
|
|
|
setValue(column, v);
|
|
}
|
|
|
|
virtual void bind(int column, const boost::posix_time::ptime& value,
|
|
SqlDateTimeType type)
|
|
{
|
|
DEBUG(std::cerr << this << " bind " << column << " "
|
|
<< boost::posix_time::to_simple_string(value) << std::endl);
|
|
|
|
std::string v;
|
|
if (type == SqlDate)
|
|
v = boost::gregorian::to_iso_extended_string(value.date());
|
|
else
|
|
{
|
|
v = boost::posix_time::to_iso_extended_string(value);
|
|
v[v.find('T')] = ' ';
|
|
string::size_type i = v.find(',');
|
|
if (i != string::npos)
|
|
v[i] = '.';
|
|
}
|
|
|
|
setValue(column, v);
|
|
}
|
|
|
|
virtual void bind(int column, const std::vector<unsigned char>& value)
|
|
{
|
|
DEBUG(std::cerr << this << " bind " << column << " (blob, size=" <<
|
|
value.size() << ")" << std::endl);
|
|
|
|
for (int i = (int)params_.size(); i <= column; ++i)
|
|
params_.push_back(Param());
|
|
|
|
Param& p = params_[column];
|
|
p.value.resize(value.size());
|
|
if (value.size() > 0)
|
|
memcpy(const_cast<char *>(p.value.data()), &(*value.begin()),
|
|
value.size());
|
|
p.isbinary = true;
|
|
p.isnull = false;
|
|
|
|
// FIXME if first null was bound, check here and invalidate the prepared
|
|
// statement if necessary because the type changes
|
|
}
|
|
|
|
virtual void bindNull(int column)
|
|
{
|
|
DEBUG(std::cerr << this << " bind " << column << " null" << std::endl);
|
|
|
|
for (int i = (int)params_.size(); i <= column; ++i)
|
|
params_.push_back(Param());
|
|
|
|
params_[column].isnull = true;
|
|
}
|
|
|
|
virtual void execute()
|
|
{
|
|
if (conn_.showQueries())
|
|
std::cerr << sql_ << std::endl;
|
|
|
|
paramValues_ = new char *[params_.size()];
|
|
|
|
for (unsigned i = 0; i < params_.size(); ++i)
|
|
{
|
|
if (params_[i].isbinary)
|
|
{
|
|
paramLengths_ = new int[params_.size()];
|
|
for (unsigned j = 0; j < params_.size(); ++j)
|
|
{
|
|
paramLengths_[j] = 0;
|
|
}
|
|
break;
|
|
}
|
|
|
|
}
|
|
|
|
for (unsigned i = 0; i < params_.size(); ++i)
|
|
{
|
|
if (params_[i].isnull)
|
|
{
|
|
paramValues_[i] = 0;
|
|
this->m_stmt->SetNull(i + 1);
|
|
}
|
|
else
|
|
if (params_[i].isbinary)
|
|
{
|
|
paramValues_[i] = const_cast<char *>(params_[i].value.data());
|
|
paramLengths_[i] = (int) params_[i].value.length();
|
|
this->m_stmt->Set(i + 1, paramValues_[i], paramLengths_[i]);
|
|
}
|
|
else
|
|
{
|
|
SDT parameterType = this->m_stmt->ParameterType(i + 1);
|
|
switch(parameterType)
|
|
{
|
|
case sdString:
|
|
this->m_stmt->Set(i + 1, params_[i].value);
|
|
break;
|
|
case sdBlob:
|
|
{
|
|
IBPP::Blob b = IBPP::BlobFactory(this->conn_.connection(), this->conn_.transaction());
|
|
b->Write(params_[i].value.data(), params_[i].value.length());
|
|
b->Close();
|
|
this->m_stmt->Set(i + 1, b);
|
|
break;
|
|
}
|
|
case sdDate:
|
|
{
|
|
date d = from_string(params_[i].value);
|
|
IBPP::Date idate(d.year(), d.month(), d.day());
|
|
this->m_stmt->Set(i + 1, idate);
|
|
break;
|
|
}
|
|
case sdTime:
|
|
{
|
|
time_duration td = duration_from_string(params_[i].value);
|
|
IBPP::Time t(td.hours(), td.seconds(), td.minutes(), (int) (((td.fractional_seconds() * 1.0) / time_duration::ticks_per_second()) * 10000));
|
|
this->m_stmt->Set(i + 1, t);
|
|
break;
|
|
}
|
|
case sdTimestamp:
|
|
{
|
|
ptime p = time_from_string(params_[i].value);
|
|
IBPP::Timestamp ts(p.date().year(), p.date().month(), p.date().day()
|
|
, p.time_of_day().hours(), p.time_of_day().minutes()
|
|
, p.time_of_day().seconds(), (int) (((p.time_of_day().fractional_seconds() * 1.0) / time_duration::ticks_per_second()) * 10000));
|
|
this->m_stmt->Set(i + 1, ts);
|
|
break;
|
|
}
|
|
case sdFloat:
|
|
{
|
|
float f;
|
|
istringstream s(params_[i].value);
|
|
s >> f;
|
|
this->m_stmt->Set(i + 1, f);
|
|
break;
|
|
}
|
|
case sdDouble:
|
|
{
|
|
double d;
|
|
istringstream s(params_[i].value);
|
|
s >> d;
|
|
this->m_stmt->Set(i + 1, d);
|
|
break;
|
|
}
|
|
case sdInteger:
|
|
{
|
|
int integer;
|
|
istringstream s(params_[i].value);
|
|
s >> integer;
|
|
this->m_stmt->Set(i + 1, integer);
|
|
break;
|
|
}
|
|
case sdSmallint:
|
|
{
|
|
int16_t int16;
|
|
istringstream s(params_[i].value);
|
|
s >> int16;
|
|
this->m_stmt->Set(i + 1, int16);
|
|
break;
|
|
}
|
|
case sdLargeint:
|
|
{
|
|
int64_t int64;
|
|
istringstream s(params_[i].value);
|
|
s >> int64;
|
|
this->m_stmt->Set(i + 1, int64);
|
|
break;
|
|
}
|
|
break;
|
|
}
|
|
}
|
|
}
|
|
try
|
|
{
|
|
this->m_stmt->Execute();
|
|
affectedRows_ = this->m_stmt->AffectedRows();
|
|
|
|
row_ = 0;
|
|
}
|
|
catch(IBPP::LogicException &e)
|
|
{
|
|
throw FirebirdException(e.what());
|
|
}
|
|
}
|
|
|
|
virtual long long insertedId()
|
|
{
|
|
return lastId_;
|
|
}
|
|
|
|
virtual int affectedRowCount()
|
|
{
|
|
return affectedRows_;
|
|
}
|
|
|
|
virtual bool nextRow()
|
|
{
|
|
try
|
|
{
|
|
if (this->m_stmt.intf())
|
|
return this->m_stmt->Fetch();
|
|
else
|
|
throw FirebirdException("Sql statement not assigned (bool nextRow())");
|
|
}
|
|
catch(IBPP::Exception &e)
|
|
{
|
|
throw FirebirdException(e.what());
|
|
}
|
|
return false;
|
|
}
|
|
|
|
virtual bool getResult(int column, std::string *value, int size)
|
|
{
|
|
if (this->m_stmt.intf())
|
|
{
|
|
if (this->m_stmt->IsNull(++column))
|
|
return false;
|
|
|
|
this->m_stmt->Get(column, *value);
|
|
//value->resize(size);
|
|
}
|
|
else
|
|
throw FirebirdException("Sql statement not assigned (bool getResult(int column, std::string *value, int size))");
|
|
|
|
DEBUG(std::cerr << this
|
|
<< " result string " << column << " " << *value << std::endl);
|
|
|
|
return true;
|
|
}
|
|
|
|
virtual bool getResult(int column, short *value)
|
|
{
|
|
if (this->m_stmt.intf())
|
|
{
|
|
if (this->m_stmt->IsNull(++column))
|
|
return false;
|
|
|
|
this->m_stmt->Get(column, *value);
|
|
//value->resize(size);
|
|
}
|
|
else
|
|
throw FirebirdException("Sql statement not assigned (bool getResult(int column, short *value))");
|
|
|
|
DEBUG(std::cerr << this
|
|
<< " result string " << column << " " << *value << std::endl);
|
|
|
|
return true;
|
|
}
|
|
|
|
virtual bool getResult(int column, int *value)
|
|
{
|
|
if (this->m_stmt.intf())
|
|
{
|
|
if (this->m_stmt->IsNull(++column))
|
|
return false;
|
|
|
|
this->m_stmt->Get(column, *value);
|
|
}
|
|
else
|
|
throw FirebirdException("Sql statement not assigned (bool getResult(int column, int *value))");
|
|
|
|
DEBUG(std::cerr << this
|
|
<< " result string " << column << " " << *value << std::endl);
|
|
|
|
return true;
|
|
}
|
|
|
|
virtual bool getResult(int column, long long *value)
|
|
{
|
|
if (this->m_stmt.intf())
|
|
{
|
|
if (this->m_stmt->IsNull(++column))
|
|
return false;
|
|
|
|
this->m_stmt->Get(column, *((int64_t *)value));
|
|
}
|
|
else
|
|
throw FirebirdException("Sql statement not assigned (bool getResult(int column, long long *value))");
|
|
|
|
DEBUG(std::cerr << this
|
|
<< " result string " << column << " " << *value << std::endl);
|
|
|
|
return true;
|
|
}
|
|
|
|
virtual bool getResult(int column, float *value)
|
|
{
|
|
if (this->m_stmt.intf())
|
|
{
|
|
if (this->m_stmt->IsNull(++column))
|
|
return false;
|
|
|
|
this->m_stmt->Get(column, *value);
|
|
}
|
|
else
|
|
throw FirebirdException("Sql statement not assigned (bool getResult(int column, float *value))");
|
|
|
|
DEBUG(std::cerr << this
|
|
<< " result string " << column << " " << *value << std::endl);
|
|
|
|
return true;
|
|
}
|
|
|
|
virtual bool getResult(int column, double *value)
|
|
{
|
|
if (this->m_stmt.intf())
|
|
{
|
|
if (this->m_stmt->IsNull(++column))
|
|
return false;
|
|
|
|
this->m_stmt->Get(column, *value);
|
|
}
|
|
else
|
|
throw FirebirdException("Sql statement not assigned (bool getResult(int column, double *value))");
|
|
|
|
DEBUG(std::cerr << this
|
|
<< " result string " << column << " " << *value << std::endl);
|
|
|
|
return true;
|
|
}
|
|
|
|
virtual bool getResult(int column, boost::posix_time::ptime *value,
|
|
SqlDateTimeType type)
|
|
{
|
|
if (this->m_stmt.intf())
|
|
{
|
|
if (this->m_stmt->IsNull(++column))
|
|
return false;
|
|
|
|
switch(type)
|
|
{
|
|
case SqlDate:
|
|
{
|
|
Date d;
|
|
this->m_stmt->Get(column, d);
|
|
*value = boost::posix_time::ptime(boost::gregorian::date(d.Year(), d.Month(), d.Day()));
|
|
break;
|
|
}
|
|
case SqlDateTime:
|
|
{
|
|
Timestamp tm;
|
|
this->m_stmt->Get(column, tm);
|
|
*value = boost::posix_time::ptime(boost::gregorian::date(tm.Year(), tm.Month(), tm.Day()), boost::posix_time::hours(tm.Hours()) + boost::posix_time::minutes(tm.Minutes()) + boost::posix_time::seconds(tm.Seconds()) + boost::posix_time::microseconds(tm.SubSeconds() * 100));
|
|
break;
|
|
}
|
|
case SqlTime:
|
|
{
|
|
Time t;
|
|
this->m_stmt->Get(column, t);
|
|
*value = boost::posix_time::ptime(boost::gregorian::date(0, 0, 0), boost::posix_time::hours(t.Hours()) + boost::posix_time::minutes(t.Minutes()) + boost::posix_time::seconds(t.Seconds()) + boost::posix_time::microseconds(t.SubSeconds() * 100));
|
|
break;
|
|
}
|
|
}
|
|
}
|
|
else
|
|
throw FirebirdException("Sql statement not assigned (bool getResult(int column, boost::posix_time::ptime *value, SqlDateTimeType type))");
|
|
|
|
DEBUG(std::cerr << this
|
|
<< " result string " << column << " " << *value << std::endl);
|
|
|
|
return true;
|
|
}
|
|
|
|
virtual bool getResult(int column, boost::posix_time::time_duration *value)
|
|
{
|
|
if (this->m_stmt.intf())
|
|
{
|
|
if (this->m_stmt->IsNull(column))
|
|
return false;
|
|
Time t;
|
|
this->m_stmt->Get(column, t);
|
|
*value = boost::posix_time::time_duration(boost::posix_time::hours(t.Hours()) + boost::posix_time::minutes(t.Minutes()) + boost::posix_time::seconds(t.Seconds()) + boost::posix_time::microseconds(t.SubSeconds() * 100));
|
|
}
|
|
else
|
|
throw FirebirdException("Sql statement not assigned (bool getResult(int column, boost::posix_time::time_duration *value))");
|
|
|
|
DEBUG(std::cerr << this
|
|
<< " result string " << column << " " << *value << std::endl);
|
|
|
|
return true;
|
|
}
|
|
|
|
virtual bool getResult(int column, std::vector<unsigned char> *value,
|
|
int size)
|
|
{
|
|
if (this->m_stmt.intf())
|
|
{
|
|
char *result = new char[size];
|
|
if (this->m_stmt->IsNull(++column))
|
|
{
|
|
delete[] result;
|
|
return false;
|
|
}
|
|
|
|
this->m_stmt->Get(column, result, size);
|
|
|
|
for(int i = 0; i < size; i++)
|
|
{
|
|
value->push_back(result[i]);
|
|
}
|
|
}
|
|
else
|
|
throw FirebirdException("Sql statement not assigned (bool getResult(int column, std::vector<unsigned char> *value, int size))");
|
|
|
|
DEBUG(std::cerr << this
|
|
<< " result string " << column << " " << *value << std::endl);
|
|
|
|
return true;
|
|
}
|
|
|
|
virtual std::string sql() const {
|
|
return sql_;
|
|
}
|
|
|
|
private:
|
|
struct Param {
|
|
std::string value;
|
|
bool isnull, isbinary;
|
|
|
|
Param() : isnull(true), isbinary(false) { }
|
|
};
|
|
|
|
Firebird& conn_;
|
|
std::string sql_;
|
|
IBPP::Statement m_stmt;
|
|
char name_[64];
|
|
//PGresult *result_;
|
|
enum { NoFirstRow, FirstRow, NextRow, Done } state_;
|
|
std::vector<Param> params_;
|
|
|
|
char **paramValues_;
|
|
int *paramTypes_, *paramLengths_;
|
|
|
|
int lastId_, row_, affectedRows_;
|
|
|
|
// void handleErr(int err)
|
|
// {
|
|
// if (err != PGRES_COMMAND_OK && err != PGRES_TUPLES_OK)
|
|
// throw PostgresException(PQerrorMessage(conn_.connection()));
|
|
// }
|
|
|
|
void setValue(int column, const std::string& value)
|
|
{
|
|
for (int i = (int)params_.size(); i <= column; ++i)
|
|
params_.push_back(Param());
|
|
|
|
params_[column].value = value;
|
|
params_[column].isnull = false;
|
|
}
|
|
|
|
std::string convertToNumberedPlaceholders(const std::string& sql)
|
|
{
|
|
return sql;
|
|
}
|
|
};
|
|
|
|
Firebird::Firebird()
|
|
: m_writableTransaction(false)
|
|
{
|
|
}
|
|
|
|
Firebird::Firebird(IBPP::Database db)
|
|
: m_writableTransaction(false)
|
|
{
|
|
this->m_db = db;
|
|
}
|
|
|
|
Firebird::Firebird(const std::string& ServerName,
|
|
const std::string& DatabaseName, const std::string& UserName,
|
|
const std::string& UserPassword, const std::string& RoleName,
|
|
const std::string& CharSet, const std::string& CreateParams)
|
|
{
|
|
this->m_db = IBPP::DatabaseFactory(ServerName, DatabaseName, UserName, UserPassword, RoleName, CharSet, CreateParams);
|
|
this->m_db->Connect();
|
|
}
|
|
|
|
Firebird::Firebird(const Firebird& other)
|
|
: SqlConnection(other), m_tra(other.m_tra)
|
|
, m_writableTransaction(other.m_writableTransaction)
|
|
{
|
|
this->m_db = other.m_db;
|
|
}
|
|
|
|
Firebird::~Firebird()
|
|
{
|
|
clearStatementCache();
|
|
}
|
|
|
|
Firebird *Firebird::clone() const
|
|
{
|
|
return new Firebird(*this);
|
|
}
|
|
|
|
bool Firebird::connect(const std::string& ServerName,
|
|
const std::string& DatabaseName, const std::string& UserName,
|
|
const std::string& UserPassword, const std::string& RoleName,
|
|
const std::string& CharSet, const std::string& CreateParams)
|
|
{
|
|
|
|
this->m_db = IBPP::DatabaseFactory(ServerName, DatabaseName, UserName, UserPassword, RoleName, CharSet, CreateParams);
|
|
this->m_db->Connect();
|
|
return true;
|
|
}
|
|
|
|
SqlStatement *Firebird::prepareStatement(const std::string& sql)
|
|
{
|
|
return new FirebirdStatement(*this, sql);
|
|
}
|
|
|
|
void Firebird::executeSql(const std::string &sql)
|
|
{
|
|
|
|
if (showQueries())
|
|
std::cerr << sql << std::endl;
|
|
SqlStatement *stmt = 0;
|
|
|
|
try
|
|
{
|
|
this->startTransaction();
|
|
stmt = this->prepareStatement(sql);
|
|
|
|
stmt->execute();
|
|
|
|
this->commitTransaction();
|
|
}
|
|
catch(...)
|
|
{
|
|
if (this->m_tra.intf())
|
|
if (this->m_tra->Started())
|
|
this->m_tra->Rollback();
|
|
}
|
|
if (stmt)
|
|
delete stmt;
|
|
}
|
|
|
|
/**
|
|
* Auto increment in Firebird is done thru Before Insert Trigger...
|
|
*/
|
|
std::string Firebird::autoincrementType() const
|
|
{
|
|
return std::string();
|
|
}
|
|
|
|
std::string Firebird::autoincrementSql() const
|
|
{
|
|
return std::string();
|
|
}
|
|
|
|
std::string Firebird::autoincrementInsertSuffix() const
|
|
{
|
|
return std::string();
|
|
}
|
|
|
|
const char *Firebird::dateTimeType(SqlDateTimeType type) const
|
|
{
|
|
switch (type) {
|
|
case SqlDate:
|
|
return "date";
|
|
case SqlDateTime:
|
|
return "timestamp";
|
|
case SqlTime:
|
|
return "time";
|
|
}
|
|
|
|
std::stringstream ss;
|
|
ss << __FILE__ << ":" << __LINE__ << ": implementation error";
|
|
throw FirebirdException(ss.str());
|
|
}
|
|
|
|
const char *Firebird::blobType() const
|
|
{
|
|
return "blob";
|
|
}
|
|
|
|
void Firebird::startTransaction()
|
|
{
|
|
if (!this->m_tra.intf())
|
|
{
|
|
if (this->m_writableTransaction)
|
|
{
|
|
m_tra = IBPP::TransactionFactory(this->m_db, amWrite, ilReadCommitted, lrWait);
|
|
}
|
|
else
|
|
{
|
|
m_tra = IBPP::TransactionFactory(this->m_db, amRead, ilReadCommitted, lrWait);
|
|
}
|
|
}
|
|
this->m_tra->Start();
|
|
}
|
|
|
|
void Firebird::commitTransaction()
|
|
{
|
|
if (this->m_tra.intf())
|
|
this->m_tra->Commit();
|
|
}
|
|
|
|
void Firebird::rollbackTransaction()
|
|
{
|
|
if (this->m_tra.intf())
|
|
this->m_tra->Rollback();
|
|
}
|
|
|
|
}
|
|
}
|
|
}
|
