Qt-interest Archive, April 2008
getting last insert id with QSqlDatabase
Message 1 in thread
Hi all,
Recently I rewrote drivers for Kommander using QSqlDatabase on Qt 3.3.8. I'm
also porting to Qt 4.4.0 development. One thing that bothers me about SQL is
that any relational database using a subform needs the last index key and
sanity dictates to let the database do it for you. Yet there is no standard
way to retrive this AFAIK and none I have seen in the docs.
My problem is that with Postgres and MySql everything is working fine, except
this. In Postgres I encountered the problem where my previous drivers
(hk_classes) handled this fine. I was using QSqlQuery and passing
"SELECT currtval('\"testseq_ID_seq\"')"
I ran the query right after an insert and was told the sequence wasn't valid.
(Sorry I forget the exact error. It was really late) Eventually I succeeded
by running a select nextval() prior to the insert, then manually inserting
the returned value to the auto increment.
I realize this is pretty ugly for an easy tool, so I thought I'd make make a
function that took the query and the key (I can't be absolutely certain with
what I get from field type and record index that I can identify the key) and
returned the last id inserted. Unfortunately running MySql feeding QSqlQuery
"SELECT LAST_INSERT_ID()"
I get confirmation that the query ran, but I get 0 as the id.
It is possible I messed up code somewhere or don't understand the drivers. I
actually haven't coded that much C++ myself.
My two questions.
1) does anyone know how to make this work on Qt 3.3?
2) When I do my port to Qt 4.4 will it be better?
In case it's not just me being bone headed I hope this functionality will be
looked at as including a member function to get the last autogenerated key.
--
[ signature omitted ]
Message 2 in thread
Hi,
I'm using the following (with Postgres 8.3) to get a new record with new
primary key:
QSqlQuery query ;
query.prepare("INSERT INTO atlas.vehicle (v_id, v_license) VALUES
(DEFAULT, 'strLicense') RETURNING v_id");
query.exec();
if( query.isActive())
{
query.next();
m_LastVehicleId = query.record().value("v_id").toInt();
}
Works fine, v_id is defined as serial in the table.
Hth,
Jan
Eric Laffoon wrote:
> Hi all,
> Recently I rewrote drivers for Kommander using QSqlDatabase on Qt 3.3.8. I'm
> also porting to Qt 4.4.0 development. One thing that bothers me about SQL is
> that any relational database using a subform needs the last index key and
> sanity dictates to let the database do it for you. Yet there is no standard
> way to retrive this AFAIK and none I have seen in the docs.
>
> My problem is that with Postgres and MySql everything is working fine, except
> this. In Postgres I encountered the problem where my previous drivers
> (hk_classes) handled this fine. I was using QSqlQuery and passing
> "SELECT currtval('\"testseq_ID_seq\"')"
> I ran the query right after an insert and was told the sequence wasn't valid.
> (Sorry I forget the exact error. It was really late) Eventually I succeeded
> by running a select nextval() prior to the insert, then manually inserting
> the returned value to the auto increment.
>
> I realize this is pretty ugly for an easy tool, so I thought I'd make make a
> function that took the query and the key (I can't be absolutely certain with
> what I get from field type and record index that I can identify the key) and
> returned the last id inserted. Unfortunately running MySql feeding QSqlQuery
> "SELECT LAST_INSERT_ID()"
> I get confirmation that the query ran, but I get 0 as the id.
>
> It is possible I messed up code somewhere or don't understand the drivers. I
> actually haven't coded that much C++ myself.
>
> My two questions.
> 1) does anyone know how to make this work on Qt 3.3?
> 2) When I do my port to Qt 4.4 will it be better?
>
> In case it's not just me being bone headed I hope this functionality will be
> looked at as including a member function to get the last autogenerated key.
--
[ signature omitted ]
Message 3 in thread
Hi Eric,
Nice to see you here :-)
Looking at the docs, I see two references to what you are looking for:
1) The driver has to support the feature. Check out the Qt 4.4 docs on
QSqlDriver::hasFeature. You will be interested in feature
QSqlDriver::LastInsertId. I do not know if the MySql driver supports this
feature. The ODBC driver does not, unfortunately :-(
2) If 1) is taken care of, then you can use QSqlQuery::lastInsertId() to
retreive the value. For more low level access, QSqlResult::lastInsertId()
returns the same information.
I hope this helps.
André
"Eric Laffoon" <sequitur@xxxxxxx> wrote in message
news:200804101502.18674.sequitur@xxxxxxxxxx
> Hi all,
> Recently I rewrote drivers for Kommander using QSqlDatabase on Qt 3.3.8.
> I'm
> also porting to Qt 4.4.0 development. One thing that bothers me about SQL
> is
> that any relational database using a subform needs the last index key and
> sanity dictates to let the database do it for you. Yet there is no
> standard
> way to retrive this AFAIK and none I have seen in the docs.
>
> My problem is that with Postgres and MySql everything is working fine,
> except
> this. In Postgres I encountered the problem where my previous drivers
> (hk_classes) handled this fine. I was using QSqlQuery and passing
> "SELECT currtval('\"testseq_ID_seq\"')"
> I ran the query right after an insert and was told the sequence wasn't
> valid.
> (Sorry I forget the exact error. It was really late) Eventually I
> succeeded
> by running a select nextval() prior to the insert, then manually inserting
> the returned value to the auto increment.
>
> I realize this is pretty ugly for an easy tool, so I thought I'd make make
> a
> function that took the query and the key (I can't be absolutely certain
> with
> what I get from field type and record index that I can identify the key)
> and
> returned the last id inserted. Unfortunately running MySql feeding
> QSqlQuery
> "SELECT LAST_INSERT_ID()"
> I get confirmation that the query ran, but I get 0 as the id.
>
> It is possible I messed up code somewhere or don't understand the drivers.
> I
> actually haven't coded that much C++ myself.
>
> My two questions.
> 1) does anyone know how to make this work on Qt 3.3?
> 2) When I do my port to Qt 4.4 will it be better?
>
> In case it's not just me being bone headed I hope this functionality will
> be
> looked at as including a member function to get the last autogenerated
> key.
> --
> Eric Laffoon
> Project Lead Quanta plus/Kommander
> http://kdewebdev.org
>
> --
> To unsubscribe - send a mail to qt-interest-request@xxxxxxxxxxxxx with
> "unsubscribe" in the subject or the body.
> List archive and information: http://lists.trolltech.com/qt-interest/
--
[ signature omitted ]
Message 4 in thread
André Somers wrote:
> I do not know if the MySql driver supports
> this feature.
It does.
Cheers,
Darrik
>
> "Eric Laffoon" <sequitur@xxxxxxx> wrote in message
> news:200804101502.18674.sequitur@xxxxxxxxxx
>> Hi all,
>> Recently I rewrote drivers for Kommander using QSqlDatabase on Qt
>> 3.3.8. I'm
>> also porting to Qt 4.4.0 development. One thing that bothers me about
>> SQL is
>> that any relational database using a subform needs the last index key and
>> sanity dictates to let the database do it for you. Yet there is no
>> standard
>> way to retrive this AFAIK and none I have seen in the docs.
>>
>> My problem is that with Postgres and MySql everything is working fine,
>> except
>> this. In Postgres I encountered the problem where my previous drivers
>> (hk_classes) handled this fine. I was using QSqlQuery and passing
>> "SELECT currtval('\"testseq_ID_seq\"')"
>> I ran the query right after an insert and was told the sequence wasn't
>> valid.
>> (Sorry I forget the exact error. It was really late) Eventually I
>> succeeded
>> by running a select nextval() prior to the insert, then manually
>> inserting
>> the returned value to the auto increment.
>>
>> I realize this is pretty ugly for an easy tool, so I thought I'd make
>> make a
>> function that took the query and the key (I can't be absolutely
>> certain with
>> what I get from field type and record index that I can identify the
>> key) and
>> returned the last id inserted. Unfortunately running MySql feeding
>> QSqlQuery
>> "SELECT LAST_INSERT_ID()"
>> I get confirmation that the query ran, but I get 0 as the id.
>>
>> It is possible I messed up code somewhere or don't understand the
>> drivers. I
>> actually haven't coded that much C++ myself.
>>
>> My two questions.
>> 1) does anyone know how to make this work on Qt 3.3?
>> 2) When I do my port to Qt 4.4 will it be better?
>>
>> In case it's not just me being bone headed I hope this functionality
>> will be
>> looked at as including a member function to get the last autogenerated
>> key.
>> --
>> Eric Laffoon
>> Project Lead Quanta plus/Kommander
>> http://kdewebdev.org
>>
>> --
>> To unsubscribe - send a mail to qt-interest-request@xxxxxxxxxxxxx with
>> "unsubscribe" in the subject or the body.
>> List archive and information: http://lists.trolltech.com/qt-interest/
>
> --
> To unsubscribe - send a mail to qt-interest-request@xxxxxxxxxxxxx with
> "unsubscribe" in the subject or the body.
> List archive and information: http://lists.trolltech.com/qt-interest/
>
>
--
[ signature omitted ]
Message 5 in thread
On Friday 11 April 2008 12:24:00 am André Somers wrote:
> Hi Eric,
>
> Nice to see you here :-)
>
> Looking at the docs, I see two references to what you are looking for:
> 1) The driver has to support the feature. Check out the Qt 4.4 docs on
> QSqlDriver::hasFeature. You will be interested in feature
> QSqlDriver::LastInsertId. I do not know if the MySql driver supports this
> feature. The ODBC driver does not, unfortunately :-(
> 2) If 1) is taken care of, then you can use QSqlQuery::lastInsertId() to
> retreive the value. For more low level access, QSqlResult::lastInsertId()
> returns the same information.
>
> I hope this helps.
>
> André
>
That is nice. Unfortunately, or perhaps fortunately, I discovered the big
problem with my code was forgetting to scroll down a few hundred lines and
add the two new paramaters to the call. Now I have rewritten the whole thing,
thank to the help of people here, and it sort of works. It has three
parameters, examples included.
1) the sql insert - "INSERT INTO testseq ("ID", junk) VALUES(DEFAULT, 'foo
footy')"
2) table (optional) testseq
3) key (optional) ID
I have isolated this to the problem here. If I pass "" from Kommander it
works, but if I don't and try to set the string to "" internally it doesn't.
QStringList s = QStringList::split(" ", sql);
for (QStringList::Iterator it = s.begin(); it != s.end(); ++it)
{
if (QString(*it).upper() == "INTO")
{
++it;
utable = QString(*it);
break;
}
Late when I try to use this here
query = QSqlQuery( "SELECT
pg_get_serial_sequence('"+utable+"', '"+ukey+"')", db );
my debug returns the value of varable 1 (sql) in the place of varable 3
(ukey). I get that by running requesting the last SQL.
It's got to be something simple I don't yet understand about using iterators
or something, but I'm going nuts here.
--
[ signature omitted ]
Message 6 in thread
On Friday 11 April 2008 1:14:53 pm Eric Laffoon wrote:
> I have isolated this to the problem here. If I pass "" from Kommander it
> works, but if I don't and try to set the string to "" internally it
> doesn't.
I solved the problem, if inelegantly. The important part is it works
perfectly, even if there was some way to do it with one less line of
code. ;-)
--
[ signature omitted ]
