/*!
Binds the value \a val of parameter type \a paramType to the next
available position in the current record (row).
\sa bindValue()
*/
void QSqlResult::addBindValue(const QVariant& val, QSql::ParamType paramType)
{
Q_D(QSqlResult);
d->binds = PositionalBinding;
bindValue(d->bindCount, val, paramType);
++d->bindCount;
}
bool XSqlQuery::prepare(const char *pSql)
{
bool ret;
ret = QSqlQuery::prepare(QString(pSql));
bindValue(":firstnullfix", QVariant());
return ret;
}
bool XSqlQuery::prepare(const QString &pSql)
{
bool ret;
if(_data && _data->_emulatePrepare)
{
// In 4.4.1 Qt started supporting true prepared queries on the PostgreSQL driver and this
// caused several problems with all our code and the way it worked so this is a modified copy
// of their code to use the implemented prepare if we have that option set so we can use the method
// that works best in the case we are using it for.
((XSqlResultHelper*)result())->setActive(false);
((XSqlResultHelper*)result())->setLastError(QSqlError());
((XSqlResultHelper*)result())->setAt(QSql::BeforeFirstRow);
if (!driver()) {
qWarning("XSqlQuery::prepare: no driver");
return false;
}
if (!driver()->isOpen() || driver()->isOpenError()) {
qWarning("XSqlQuery::prepare: database not open");
return false;
}
if (pSql.isEmpty()) {
qWarning("XSqlQuery::prepare: empty query");
return false;
}
#ifdef QT_DEBUG_SQL
qDebug("\n XSqlQuery::prepare: %s", query.toLocal8Bit().constData());
#endif
ret = ((XSqlResultHelper*)result())->XSqlResultHelper::savePrepare(pSql);
}
else
ret = QSqlQuery::prepare(pSql);
if(ret && ((driver() && !driver()->hasFeature(QSqlDriver::PreparedQueries)) || (_data && _data->_emulatePrepare)))
bindValue(":firstnullfix", QVariant());
return ret;
}
void MSqlQuery::bindValues(const MSqlBindings &bindings)
{
MSqlBindings::const_iterator it;
for (it = bindings.begin(); it != bindings.end(); ++it)
{
bindValue(it.key(), it.value());
}
}
void MySqlDataProvider::bindValue(int place, int value)
{
MYSQL_BIND* bind = new MYSQL_BIND;
bind->buffer_type= MYSQL_TYPE_LONG;
bind->buffer= &value;
bind->is_null = 0;
bindValue(place, bind);
}
int bindValues(sqlite3_stmt* stmt, sqlite_value* values, uint8_t nCol) {
for (int i=0; i < nCol; i++) {
int rc = bindValue(stmt, i+1, &values[i]);
if (rc != SQLITE_OK) {
return rc;
}
}
return SQLITE_OK;
}
KDbSqlResult* SqlitePreparedStatement::execute(
KDbPreparedStatement::Type type,
const KDbField::List& selectFieldList,
KDbFieldList* insertFieldList,
const KDbPreparedStatementParameters& parameters, bool *resultOwned)
{
Q_UNUSED(insertFieldList);
Q_UNUSED(resultOwned);
if (!m_sqlResult->prepared_st) {
return nullptr;
}
int par = 1; // par.index counted from 1
KDbField::ListIterator itFields(selectFieldList.constBegin());
for (QList<QVariant>::ConstIterator it = parameters.constBegin();
itFields != selectFieldList.constEnd();
it += (it == parameters.constEnd() ? 0 : 1), ++itFields, par++)
{
if (!bindValue(*itFields, it == parameters.constEnd() ? QVariant() : *it, par))
return nullptr;
}
//real execution
const int res = sqlite3_step(m_sqlResult->prepared_st);
if (type == KDbPreparedStatement::InsertStatement) {
const bool ok = res == SQLITE_DONE;
if (ok) {
m_result = KDbResult();
} else {
m_result.setServerErrorCode(res);
storeResult(&m_result);
sqliteWarning() << m_result << QString::fromLatin1(sqlite3_sql(m_sqlResult->prepared_st));
}
(void)sqlite3_reset(m_sqlResult->prepared_st);
return m_sqlResult.data();
}
else if (type == KDbPreparedStatement::SelectStatement) {
//! @todo fetch result
const bool ok = res == SQLITE_ROW;
storeResult(&m_result);
if (ok) {
m_result = KDbResult();
} else {
m_result.setServerErrorCode(res);
storeResult(&m_result);
sqliteWarning() << m_result << QString::fromLatin1(sqlite3_sql(m_sqlResult->prepared_st));
}
(void)sqlite3_reset(m_sqlResult->prepared_st);
return m_sqlResult.data();
}
return nullptr;
}
void MySqlDataProvider::bindValue(int place, const std::string &value)
{
unsigned long* size = new unsigned long;
*size = value.size();
MYSQL_BIND* bind = new MYSQL_BIND;
bind->buffer_type= MYSQL_TYPE_STRING;
bind->buffer= (void*) value.c_str();
bind->buffer_length= value.size();
bind->length = size;
bind->is_null = 0;
bindValue(place, bind);
}
/*! \internal
\since 4.2
Executes a prepared query in batch mode if the driver supports it,
otherwise emulates a batch execution using bindValue() and exec().
QSqlDriver::hasFeature() can be used to find out whether a driver
supports batch execution.
Batch execution can be faster for large amounts of data since it
reduces network roundtrips.
For batch executions, bound values have to be provided as lists
of variants (QVariantList).
Each list must contain values of the same type. All lists must
contain equal amount of values (rows).
NULL values are passed in as typed QVariants, for example
\c {QVariant(QVariant::Int)} for an integer NULL value.
Example:
\snippet code/src_sql_kernel_qsqlresult.cpp 0
Here, we insert two rows into a SQL table, with each row containing three values.
\sa exec(), QSqlDriver::hasFeature()
*/
bool QSqlResult::execBatch(bool arrayBind)
{
Q_UNUSED(arrayBind);
QVector<QVariant> values = d->values;
if (values.count() == 0)
return false;
for (int i = 0; i < values.at(0).toList().count(); ++i) {
for (int j = 0; j < values.count(); ++j)
bindValue(j, values.at(j).toList().at(i), QSql::In);
if (!exec())
return false;
}
return true;
}
void
TestBookCategory::testUpdateCategory() {
QFETCH(QString, name);
QFETCH(QString, newName);
QFETCH(chancho::Category::Type, type);
QFETCH(QString, color);
QFETCH(QString, newColor);
// create the category and store it
auto category = std::make_shared<PublicCategory>(name, type, color);
PublicBook book;
book.store(category);
QVERIFY(category->wasStoredInDb());
// update the category and assert the values
category->name = newName;
category->color = newColor;
book.store(category);
QVERIFY(category->wasStoredInDb());
// assert that the db is present in the db
auto dbPath = PublicBook::databasePath();
auto db = sys::DatabaseFactory::instance()->addDatabase("QSQLITE", QTest::currentTestFunction());
db->setDatabaseName(dbPath);
auto opened = db->open();
QVERIFY(opened);
// create the required tables and indexes
auto query = db->createQuery();
query->prepare(SELECT_CATEGORY_QUERY);
query->bindValue(":uuid", category->_dbId.toString());
auto success = query->exec();
QVERIFY(success);
QVERIFY(query->next());
QCOMPARE(query->value("name").toString(), newName); // test against new name to be 100% sure
QCOMPARE(query->value("color").toString(), newColor);
QCOMPARE(query->value("type").toInt(), static_cast<int>(category->type));
QVERIFY(query->value("parent").isNull());
db->close();
}
/*! \internal
\since 4.2
Executes a prepared query in batch mode if the driver supports it,
otherwise emulates a batch execution using bindValue() and exec().
QSqlDriver::hasFeature() can be used to find out whether a driver
supports batch execution.
Batch execution can be faster for large amounts of data since it
reduces network roundtrips.
For batch executions, bound values have to be provided as lists
of variants (QVariantList).
Each list must contain values of the same type. All lists must
contain equal amount of values (rows).
NULL values are passed in as typed QVariants, for example
\c {QVariant(QVariant::Int)} for an integer NULL value.
Example:
\snippet doc/src/snippets/code/src_sql_kernel_qsqlresult.cpp 0
Here, we insert two rows into a SQL table, with each row containing three values.
\sa exec(), QSqlDriver::hasFeature()
*/
bool QSqlResult::execBatch(bool arrayBind)
{
if (driver()->hasFeature(QSqlDriver::BatchOperations)) {
virtual_hook(BatchOperation, &arrayBind);
d->resetBindCount();
return d->error.type() == QSqlError::NoError;
} else {
QVector<QVariant> values = d->values;
if (values.count() == 0)
return false;
for (int i = 0; i < values.at(0).toList().count(); ++i) {
for (int j = 0; j < values.count(); ++j)
bindValue(j, values.at(j).toList().at(i), QSql::In);
if (!exec())
return false;
}
return true;
}
return false;
}
void
TestBookCategory::testRemoveCategoryNoParent() {
QString name = "Salary";
auto type = chancho::Category::Type::INCOME;
auto category = std::make_shared<PublicCategory>(name, type);
PublicBook book;
book.store(category);
// assert that both the parent and the category are added
QVERIFY(category->wasStoredInDb());
// method under test
book.remove(category);
QVERIFY(!category->wasStoredInDb());
// assert that the db is present in the db
auto dbPath = PublicBook::databasePath();
auto db = sys::DatabaseFactory::instance()->addDatabase("QSQLITE", QTest::currentTestFunction());
db->setDatabaseName(dbPath);
auto opened = db->open();
QVERIFY(opened);
// create the required tables and indexes
auto query = db->createQuery();
query->prepare(SELECT_CATEGORY_QUERY);
query->bindValue(":uuid", category->_dbId.toString());
auto success = query->exec();
QVERIFY(success);
QVERIFY(!query->next());
db->close();
}
ZExport(ZSqlCursor&) ZSqlCursor::operator<<(const ZString& val)
{
ZFUNCTRACE_DEVELOP("ZSqlCursor::operator<<(const ZString& val)");
bindValue(ZSqlChar((const char*)val));
return *this;
} // operator<<
/**
* @brief Repository::bindValues Bind all the placeholders to their values in the given query.
* @param query Query for which values will be bound.
* @param args QHash associating a value to a placeholder.
*/
void Repository::bindValues(QSqlQuery *query, QHash<QString, QVariant> bindValues) {
for (QString placeholder : bindValues.keys()) {
bindValue(query, placeholder, bindValues.value(placeholder));
}
}
/*!
\overload
Binds the placeholder at position \a pos with type \c QSql::In.
*/
void QSqlQuery::bindValue( int pos, const QVariant& val )
{
bindValue( pos, val, QSql::In );
}
/*!
\overload
Binds the placeholder with type \c QSql::In.
*/
void QSqlQuery::bindValue( const QString& placeholder, const QVariant& val )
{
bindValue( placeholder, val, QSql::In );
}
void QSqlExtension::addBindValue( const QVariant& val, QSql::ParameterType tp )
{
bindm = BindByPosition;
bindValue( bindCount++, val, tp );
}
int applyUpdate(sqlite3* db, const Instruction* instr)
{
int nCol = instr->table->nCol;
sqlite_value* valsBefore = instr->values;
sqlite_value* valsAfter = instr->values + nCol;
std::vector<std::string> columnNames = getColumnNames(db, instr->table->tableName);
std::string sql;
sql = sql + "UPDATE " + instr->table->tableName + " SET";
// sets
for (int n=0, i=0; i < nCol; i++) {
const auto& name = columnNames.at(i);
const auto val = valsAfter[i];
if (val.type) {
if (n > 0) {
sql += ", ";
}
sql = sql + " " + name + " = " + "?";
n++;
}
}
//wheres
sql += " WHERE ";
for (int n=0, i=0; i < nCol; i++) {
const auto& name = columnNames.at(i);
const auto val = valsBefore[i];
if (val.type) {
if (n > 0) {
sql += " AND";
}
sql = sql + " " + name + " = " + "?";
n++;
}
}
sqlite3_stmt* stmt; int rc;
rc = sqlite3_prepare_v2(db, sql.data(), sql.size(), &stmt, nullptr);
if (rc != SQLITE_OK) {
std::cerr << "applyUpdate: Failed preparing sql " << sql << std::endl;
return 1;
}
int n = 1;
for (int i=0; i < nCol; i++) {
sqlite_value* val = &valsAfter[i];
if (val->type) {
if (bindValue(stmt, n, val)) {
return 1;
}
n++;
}
}
for (int i=0; i < nCol; i++) {
sqlite_value* val = &valsBefore[i];
if (val->type) {
if (bindValue(stmt, n, val)) {
return 1;
}
n++;
}
}
rc = sqlite3_step(stmt);
sqlite3_finalize(stmt);
if (rc != SQLITE_DONE) {
return rc;
}
return SQLITE_OK;
}