NAMESPACE_UPP
String SqlStatement::Get(int dialect) const {
ASSERT(dialect == ORACLE || dialect == SQLITE3 || dialect == MY_SQL || dialect == MSSQL ||
dialect == PGSQL || dialect == FIREBIRD || dialect == DB2);
return SqlCompile(dialect, text);
}
void SqlMassInsert::Flush()
{
const dword DONE = 0xffffffff;
if(cache.GetCount() == 0)
return;
for(int ii = 0; ii < cache.GetCount(); ii++) {
dword nulls = cache[ii].nulls;
if(nulls != DONE) {
String insert;
insert << "insert into " + table + '(';
bool nextcol = false;
for(int i = 0; i < column.GetCount(); i++) {
if(!(nulls & (1 << i))) {
if(nextcol)
insert << ", ";
nextcol = true;
insert << column[i];
}
}
insert << ')';
bool nextsel = false;
for(int i = ii; i < cache.GetCount(); i++) {
Row& r = cache[i];
if(r.nulls == nulls) {
r.nulls = DONE;
if(nextsel)
insert << " union all";
nextsel = true;
insert << " select ";
bool nextval = false;
for(int i = 0; i < r.value.GetCount(); i++)
if(!(nulls & (1 << i))) {
if(nextval)
insert << ", ";
nextval = true;
insert << SqlCompile(sql.GetDialect(), SqlFormat(r.value[i]));
}
if(sql.GetDialect() == ORACLE)
insert << " from dual";
}
}
sql.Execute(insert);
}
}
cache.Clear();
column.Clear();
pos = 0;
}
void SqlMassInsert::Flush()
{
const uint64 DONE = (uint64)-1;
if(cache.GetCount() == 0)
return;
if(use_transaction)
sql.GetSession().Begin();
SqlBool remove;
bool doremove = false;
for(int ii = 0; ii < cache.GetCount(); ii++) {
SqlBool rm = cache[ii].remove;
if(!rm.IsEmpty()) {
doremove = true;
remove = remove || rm;
}
}
if(doremove)
sql * Delete(table).Where(remove);
String insert;
int dialect = sql.GetDialect();
if(findarg(dialect, MY_SQL, PGSQL, MSSQL) >= 0) {
insert << "insert into " + ~table + '(';
for(int i = 0; i < column.GetCount(); i++) {
if(i)
insert << ", ";
insert << column[i];
}
insert << ") values ";
for(int i = 0; i < cache.GetCount(); i++) {
Row& r = cache[i];
if(r.value.GetCount()) {
if(i)
insert << ", ";
insert << "(";
for(int i = 0; i < r.value.GetCount(); i++) {
if(i)
insert << ", ";
insert << SqlCompile(dialect, SqlFormat(r.value[i]));
}
insert << ")";
}
}
}
else
for(int ii = 0; ii < cache.GetCount(); ii++) {
uint64 nulls = cache[ii].nulls;
if(nulls != DONE) {
insert << "insert into " + ~table + '(';
bool nextcol = false;
for(int i = 0; i < column.GetCount(); i++) {
if(!(nulls & ((uint64)1 << i))) {
if(nextcol)
insert << ", ";
nextcol = true;
insert << column[i];
}
}
insert << ')';
bool nextsel = false;
for(int i = ii; i < cache.GetCount(); i++) {
Row& r = cache[i];
if(r.nulls == nulls && r.value.GetCount()) {
r.nulls = DONE;
if(nextsel)
insert << " union all";
nextsel = true;
insert << " select ";
bool nextval = false;
for(int i = 0; i < r.value.GetCount(); i++)
if(!(nulls & ((uint64)1 << i))) {
if(nextval)
insert << ", ";
nextval = true;
insert << SqlCompile(dialect, SqlFormat(r.value[i]));
}
if(dialect == ORACLE)
insert << " from dual";
}
}
}
}
sql.Execute(insert);
if(sql.WasError()) {
error = true;
if(use_transaction)
sql.GetSession().Rollback();
}
else
if(use_transaction)
sql.GetSession().Commit();
cache.Clear();
column.Clear();
pos = 0;
}
bool ODBCConnection::Execute()
{
LLOG("Execute " << (void *)this << " " << (void *)session << " " << statement);
if(session->hstmt == SQL_NULL_HANDLE)
return false;
session->FlushConnections();
last_insert_table.Clear();
number.Clear();
text.Clear();
time.Clear();
CParser p(statement);
if((p.Id("insert") || p.Id("INSERT")) && (p.Id("into") || p.Id("INTO")) && p.IsId())
last_insert_table = p.ReadId();
String query;
int pi = 0;
const char *s = statement;
while(s < statement.End())
if(*s == '\'' || *s == '\"')
s = ODBCReadString(s, query);
else {
if(*s == '?') {
if(pi >= param.GetCount()) {
session->SetError("Invalid number of parameters", statement);
return false;
}
Value v = param[pi++];
if(v.GetType() == 34)
query.Cat(SqlCompile(MSSQL, ~SqlBinary(SqlRaw(v))));
else
query.Cat(SqlCompile(MSSQL, ~SqlVal(v)));
}
else
query.Cat(*s);
s++;
}
param.Clear();
if(!IsOk(SQLPrepare(session->hstmt, (SQLCHAR *)~query, query.GetCount())))
return false;
/*
}
else {
if(!IsOk(SQLPrepare(session->hstmt, (SQLCHAR *)~statement, statement.GetCount())))
return false;
parse = false;
bparam = pick(param);
param.Clear();
for(int i = 0; i < bparam.GetCount(); i++) {
Param& p = bparam[i];
if(!IsOk(SQLBindParameter(session->hstmt, i + 1, SQL_PARAM_INPUT, p.ctype, p.sqltype,
p.width, 0, (SQLPOINTER)~p.data, p.data.GetLength(), &p.li)))
return false;
}
}
*/
SQLSMALLINT ncol;
if(!IsOk(SQLExecute(session->hstmt)) || !IsOk(SQLNumResultCols(session->hstmt, &ncol))) {
SQLFreeStmt(session->hstmt, SQL_CLOSE);
return false;
}
info.Clear();
string_type.Clear();
for(int i = 1; i <= ncol; i++) {
SQLCHAR ColumnName[256];
SQLSMALLINT NameLength;
SQLSMALLINT DataType;
SQLULEN ColumnSize;
SQLSMALLINT DecimalDigits;
SQLSMALLINT Nullable;
if(!IsOk(SQLDescribeCol(session->hstmt, i, ColumnName, 255, &NameLength, &DataType,
&ColumnSize, &DecimalDigits, &Nullable)))
return false;
string_type.Add(SQL_C_CHAR);
SqlColumnInfo& f = info.Add();
f.nullable = Nullable != SQL_NO_NULLS;
f.binary = false;
f.precision = DecimalDigits;
f.scale = 0;
f.width = ColumnSize;
f.name = (char *)ColumnName;
switch(DataType) {
case SQL_DECIMAL:
case SQL_NUMERIC:
case SQL_SMALLINT:
case SQL_INTEGER:
case SQL_REAL:
case SQL_FLOAT:
case SQL_DOUBLE:
case SQL_BIT:
case SQL_TINYINT:
f.type = DOUBLE_V;
break;
case SQL_BIGINT:
f.type = INT64_V;
break;
case SQL_TYPE_DATE:
f.type = DATE_V;
break;
case SQL_TYPE_TIMESTAMP:
f.type = TIME_V;
break;
case SQL_BINARY:
case SQL_VARBINARY:
case SQL_LONGVARBINARY:
f.type = STRING_V;
f.binary = true;
string_type.Top() = SQL_C_BINARY;
break;
case SQL_WCHAR:
case SQL_WVARCHAR:
case SQL_WLONGVARCHAR:
f.type = STRING_V;
f.binary = true;
string_type.Top() = SQL_C_WCHAR;
break;
default:
f.type = STRING_V;
break;
}
}
SQLLEN rc;
SQLRowCount(session->hstmt, &rc);
rowsprocessed = rc;
FetchAll();
return true;
}
void DlgSqlExport::Run(Sql& cursor, String command, String tablename)
{
Title(Nvl(tablename, t_("SQL query")) + t_(" export"));
object_name <<= tablename;
if(!cursor.Execute(command)) {
Exclamation(NFormat(t_("Error executing [* \1%s\1]: \1%s"), command, cursor.GetLastError()));
return;
}
for(int i = 0; i < cursor.GetColumns(); i++) {
const SqlColumnInfo& sci = cursor.GetColumnInfo(i);
String type;
switch(sci.type) {
case BOOL_V:
case INT_V: type = t_("integer"); break;
case DOUBLE_V: type = t_("real number"); break;
case STRING_V:
case WSTRING_V: type = t_("string"); break;
case DATE_V: type = t_("date"); break;
case TIME_V: type = t_("date/time"); break;
case /*ORA_BLOB_V*/-1: type = t_("BLOB"); break;
case /*ORA_CLOB_V*/-2: type = t_("CLOB"); break;
default: type = FormatInt(sci.type); break;
}
columns.Add(sci.name, sci.type, sci.width, 1);
}
static String cfg;
LoadFromString(*this, cfg);
SyncUI();
while(TopWindow::Run() == IDOK)
try {
String out_table = ~object_name;
String delim;
switch((int)~delimiters) {
case DELIM_TAB: delim = "\t"; break;
case DELIM_SEMICOLON: delim = ";"; break;
}
Vector<int> out;
String colstr;
String title;
for(int i = 0; i < columns.GetCount(); i++)
if(columns.Get(i, 3)) {
out.Add(i);
String cname = cursor.GetColumnInfo(i).name;
colstr << (i ? ", " : "") << cname;
if(i) title << delim;
title << cname;
}
if(out.IsEmpty()) {
throw Exc(t_("No columns selected!"));
continue;
}
String rowbegin, rowend;
int fmt = ~format;
FileSel fsel;
String ext;
switch(fmt) {
case FMT_TEXT: {
rowend = "";
ext = ".txt";
fsel.Type(t_("Text files (*.txt)"), "*.txt");
break;
}
case FMT_SQL: {
if(identity_insert)
rowbegin << "set identity_insert " << out_table << " on ";
rowbegin << "insert into " << out_table << "(" << colstr << ") values (";
rowend = ");";
ext = ".sql";
fsel.Type(t_("SQL scripts (*.sql)"), "*.sql");
break;
}
}
fsel.AllFilesType().DefaultExt(ext.Mid(1));
if(!IsNull(recent_file))
fsel <<= ForceExt(recent_file, ext);
if(!fsel.ExecuteSaveAs(t_("Save export as")))
continue;
recent_file = ~fsel;
FileOut fo;
if(!fo.Open(recent_file)) {
Exclamation(NFormat(t_("Error creating file [* \1%s\1]."), recent_file));
continue;
}
if(fmt == FMT_TEXT)
fo.PutLine(title);
Progress progress(t_("Exporting row %d"));
while(cursor.Fetch()) {
String script = rowbegin;
for(int i = 0; i < out.GetCount(); i++) {
Value v = cursor[out[i]];
switch(fmt) {
case FMT_TEXT: {
if(i)
script.Cat(delim);
if(IsString(v) && quote) {
String s = v;
script << '\"';
for(const char *p = s, *e = s.End(); p < e; p++)
if(*p == '\"')
script.Cat("\"\"");
else
script.Cat(*p);
script << '\"';
}
else
script << StdFormat(v);
break;
}
case FMT_SQL: {
if(i) script.Cat(", ");
script << SqlCompile(cursor.GetDialect(), SqlFormat(v));
break;
}
}
}
script << rowend;
fo.PutLine(script);
/*
if(autocommit && --left <= 0) {
fo.PutLine("commit;");
left = autocommit;
}
*/
if(progress.StepCanceled()) {
Exclamation(t_("Export aborted!"));
return;
}
}
fo.Close();
if(fo.IsError())
throw Exc(NFormat(t_("Error writing file %s."), recent_file));
break;
}
catch(Exc e) {
ShowExc(e);
}
cfg = StoreAsString(*this);
}