[Qt-interest] mysql, windows and odbc

[Sandro Cavazzoni]

Il 07/10/2009 22:57, Nicolas ha scritto:
> Hello.
>
>> i'm using for my first time the QT framework.
>> I'm really impressed about this framework and it seem to work fine.
>> But i've encountered a problem with MYSQL under windows.
>>
>> I created my database and did some test with MySQL Query Browser. I did
>> an insert and after i did "SELECT @@IDENTITY" and i obtained the last id
>> inserted. All OK!
>> When i try it from QT nothing. The insert is ok but select @@identity
>> doesn't work.
>>
>> QSqlQuery query;
>> if (!query.exec("SELECT @@IDENTITY;")) // no error returned
> <snipped>
>
> Is your example truncated, or does your 'query' only does the 'select
> @@identity' part?
>
> I think you have to do:
>
> QSqlQuery query;
> query.exec('insert into ...');
> QVariant newId = query.lastInsertId();
>
> for the thing to work.
>
>
>  From the documentation:
>
> ----------------------------------
> QVariant QSqlQuery::lastInsertId () const
>
> Returns the object ID of the most recent inserted row if the database supports
> it. An invalid QVariant will be returned if the query did not insert any
> value or if the database does not report the id back. If more than one row
> was touched by the insert, the behavior is undefined.
>
> For MySQL databases the row's auto-increment field will be returned.
> --------------------------------------
>
>
> Nicolas

Hi Nicolas,
lastInsertId is really interesting (i did this work by hand... but with 
his api it's better).

But is still doesn't work.

This is my full function (yes.. the previous was truncated) after 
lastInsertId fix:

int Customer::apply()
{
	QSqlQuery query;
	this->m_LastSqlError = "";
	int err = this->validate();
	if (err != Customer::OK) return err;
	if (this->m_Id < 0)	query.prepare("INSERT INTO customers SET name = 
:name, zip = :zip, city = :city, state = :state, address = :address, 
phone = :phone, fax = :fax, email = :email, vat = :vat, tax = :tax, 
notes = :notes, multiAgencies = :multiAgencies, update_host = 
:update_host");
	else query.prepare("UPDATE customers SET name = :name, zip = :zip, city 
= :city, state = :state, address = :address, phone = :phone, fax = :fax, 
email = :email, vat = :vat, tax = :tax, notes = :notes, multiAgencies = 
:multiAgencies, update_host = :update_host WHERE id = :id");
	query.bindValue(":id", QVariant(this->m_Id));
	query.bindValue(":name", QVariant(this->m_Name));
	query.bindValue(":zip", QVariant(this->m_Zip));
	query.bindValue(":city", QVariant(this->m_City));
	query.bindValue(":state", QVariant(this->m_State));
	query.bindValue(":address", QVariant(this->m_Address));
	query.bindValue(":phone", QVariant(this->m_Phone));
	query.bindValue(":fax", QVariant(this->m_Fax));
	query.bindValue(":email", QVariant(this->m_Email));
	query.bindValue(":vat", QVariant(this->m_Vat));
	query.bindValue(":tax", QVariant(this->m_Tax));
	query.bindValue(":notes", QVariant(this->m_Notes));
	query.bindValue(":multiAgencies", QVariant(this->m_MultiAgencies));
	query.bindValue(":update_host", QVariant(QHostInfo::localHostName()));
	if (!query.exec())
	{
		this->m_LastSqlError = query.lastError().text();
		return Customer::ERR_SQL;
	}

	if (this->m_Id < 0)
		qDebug() << query.lastInsertId().toInt();

	return Customer::OK;
}

lastInsertId always return 0 so i read again qt documentation ad i found 
this api:
--------------------------------
bool QSqlDriver::hasFeature ( DriverFeature feature ) const   [pure virtual]

Returns true if the driver supports feature feature; otherwise returns 
false.

Note that some databases need to be open() before this can be determined.

See also DriverFeature.
--------------------------------
So i tested it with feature QSqlDriver::LastInsertId and it replied me 
"false"

So lastInsertId is not supported on mysql throught ODBC.

I think the best solution is change the db server with another one... 
something with a less restrictive license.