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);
}
Exemple #2
0
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;
}
Exemple #3
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;
}
Exemple #4
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);
}