How to pass last inserted id of one table to other table for sql database in qt?
-
In my case i have to pass last auto increment id From file log table's fileid column to pass to reading log table's file id column.
-
@Qt-embedded-developer
Nobody can even begin to answer this if you don't say anything about what database you are using.... Do you not think that is relevant?Nor do you say whether you want to do this "standalone", or whether you mean you have just done an
INSERT
and expect to get back the ID created. -
@Qt-embedded-developer said in How to pass last inserted id of one table to other table for sql database in qt?:
fileid column to pass to reading log table's file id column.
In what way "pass"? Do you want to use last insert id in a query? If so, simply use it the query. But I don't know what the problem is you have...
-
@jsulm dear jsulm,
i used sql database. i used insert query
FUNCTION_IN;
QSqlQuery qQuery;qQuery.prepare("INSERT INTO "+sDBTableName[DBTABLE_FILELOG]+"\ (FilePath,\ FileType,\ FileTagName,\ Comment,\ InsertedDate,\ IsDirty)" "VALUES (?, ?, ?, ?, ?, ?)"); qQuery.addBindValue(sFilePath); qQuery.addBindValue(u8FileType); qQuery.addBindValue(sFileTagName); qQuery.addBindValue(sComment); qQuery.addBindValue(dCurrentDate); qQuery.addBindValue(u8Dirty); if(!pMainApp.ObjDbOperations.ExecuteQuery(qQuery)) return qQuery.lastError(); u32FileId = qQuery.lastInsertId().toInt(); //Get Last Auto Increment ID FUNCTION_OUT; return QSqlError();
QSqlError ReadingLog::InsertData()
{
FUNCTION_IN;
QSqlQuery qQuery;
//qDistance = AddFloatTwoPointprecision(qDistance);
//qPeakDistance = AddFloatTwoPointprecision(qPeakDistance);qQuery.prepare("INSERT INTO ReadingLog \ (SurveyId,\ SessionId,\ TagName, \ DateTimeStamp,\ TimezoneOffset,\ DistanceUnit,\ ConcentrationUnit,\ Distance,\ PeakDistance,\ Concentration,\ PeakConcentration,\ Lattitude, \ Longitude, \ Tilt, \ Direction, \ FileId, \ AlarmLevel, \ IsDirty)" "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?,?)"); qQuery.addBindValue(u32SurveyId); qQuery.addBindValue(u32SessionId); qQuery.addBindValue(sSessionTagName); qQuery.addBindValue(sDateTimeStamp); qQuery.addBindValue(sTimezoneOffset); qQuery.addBindValue(eDistanceUnit); qQuery.addBindValue(eConcUnit); qQuery.addBindValue(sDistance); qQuery.addBindValue(sPeakDistance); qQuery.addBindValue(sConcentration); qQuery.addBindValue(sPeakConcentration); qQuery.addBindValue(sLattitude); qQuery.addBindValue(sLongitude); qQuery.addBindValue(sAccTilt); qQuery.addBindValue(sMagDirection); qQuery.addBindValue(u32FileId); qQuery.addBindValue(eCommunicationAlarmLevel); qQuery.addBindValue(u8Dirty); if(!pMainApp.ObjDbOperations.ExecuteQuery(qQuery)) return qQuery.lastError(); FUNCTION_OUT; return QSqlError();
}
My problem is that in my reading log table i get inserted last id of file log table sometimes correctly.
but sometimes instead file log table's lat inserted id i get reading log table's last inserted id
-
@Qt-embedded-developer said in How to pass last inserted id of one table to other table for sql database in qt?:
i used sql database
u32FileId = qQuery.lastInsertId().toInt(); //Get Last Auto Increment ID
Since you refuse to say what database engine you are using, how can you/anyone know what this will do?
Why not at least test and tell us via
bool QSqlDriver::hasFeature(QSqlDriver::LastInsertId) const
?but sometimes instead file log table's lat inserted id i get reading log table's last inserted id
How do these differ? You state both are "log table's last inserted id"? What is your "file log" vs "reading log"? Two different tables? Are both inserted into by your
INSERT
statement? In which case you may not reliably know which "last insert ID" you get back, this a problem with various SQL implementations.If that is the case, maybe you can query for the current highest ID without relying on
INSERT
statement telling you. Depends what you are doing when.Finally, is your
sDateTimeStamp
unique? Can this table have duplicate rows? -
@JonB 1] sqlite is my database engine
2] QSqlDriver::hasFeature return 'true'
3] "file log" & "reading log" both are different table in same database
4] on click of button slot this MakeFileEntry() called. it consist global variable u32CamFileId. below i show functions how from one table to other table last inserted id passed.void Camera::MakeFileEntry(QString sFilePath, CAPTURED_FILETYPE eFileType)
{
FileLog *ObjFileLog = NULL;
ObjFileLog = new FileLog();
if(IS_VALID_OBJ(ObjFileLog))
{
ObjFileLog->sFilePath = sFilePath;
ObjFileLog->u8FileType = eFileType;
ObjFileLog->dCurrentDate = QDate::currentDate();
ObjFileLog->u8Dirty = DIRTYBIT_SET;ObjFileLog->InsertData(u32CamFileId); DELETE_OBJ(ObjFileLog); } }
QSqlError FileLog::InsertData(UINT32 &u32FileId)
{
FUNCTION_IN;
QSqlQuery qQuery;qQuery.prepare("INSERT INTO "+sDBTableName[DBTABLE_FILELOG]+"\ (FilePath,\ FileType,\ FileTagName,\ Comment,\ InsertedDate,\ IsDirty)" "VALUES (?, ?, ?, ?, ?, ?)"); qQuery.addBindValue(sFilePath); qQuery.addBindValue(u8FileType); qQuery.addBindValue(sFileTagName); qQuery.addBindValue(sComment); qQuery.addBindValue(dCurrentDate); qQuery.addBindValue(u8Dirty); if(!pMainApp.ObjDbOperations.ExecuteQuery(qQuery)) return qQuery.lastError(); u32FileId = qQuery.lastInsertId().toInt(); //Get Last Auto Increment ID FUNCTION_OUT; return QSqlError();
}
ObjReadingLog->u32FileId = u32CamFileId;
ObjReadingLog->InsertData();
My problem is that in my reading log table i get inserted last id of file log table sometimes correctly.
but sometimes instead file log table's lat inserted id i get reading log table's last inserted id
i have to solve this problem but i not get it how to solve ?
-
@Qt-embedded-developer said in How to pass last inserted id of one table to other table for sql database in qt?:
but sometimes instead file log table's lat inserted id i get reading log table's last inserted id
So, you're using same global(!) variable for both? Why? That could explain the problem: let's imagine last insert id in both tables is 3 and you insert into one table and get 4 as last insert id and then you insert into the other table and you again get 4 as last insert id...
-
@jsulm yes. but sometime why wrong inserted id comes? this wrong id some times comes -1 or some time it comes second table last inserted id .
actually it need to use only first table's last inserted id
this is bug. i am get into this but how to conquer this i need help
-
@Qt-embedded-developer said in How to pass last inserted id of one table to other table for sql database in qt?:
this wrong id some times comes -1
We already discussed this: I guess nothing was inserted so you get -1. Check the error status.
"actually it need to use only first table's last inserted id" - do you use this global variable for both tables or not? -
if -1 stored then you said that will happen
but what is reason when second table last inserted id comes ?
i use this global variable to store last inserted id of first table and pass this variable to second table for store last inserted id.
for example
table 1
fileid file path
1 /media/a.avi2 /media/b.avi
table 2
id last inserted id of table 1's file id column
.
.
34 035 34 or -1 [ actually here 2 need to comes]
-
@Qt-embedded-developer said in How to pass last inserted id of one table to other table for sql database in qt?:
My problem is that in my reading log table i get inserted last id of file log table sometimes correctly.
As I wrote earlier:
Are both inserted into by your INSERT statement? In which case you may not reliably know which "last insert ID" you get back, this a problem with various SQL implementations.
SQL's (those I know of) "last insert id" is a global variable. It refers to whatever ID was last inserted into any table. You do not know which table.