How to pass last inserted id of one table to other table for sql database in qt?

Qt embedded developer

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.

JonB

@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.

jsulm

@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...

Qt embedded developer

@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

JonB

@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?

Qt embedded developer

@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 ?

jsulm

@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...

Qt embedded developer

@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

jsulm

@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?

Qt embedded developer

@jsulm

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.avi

2 /media/b.avi

table 2
id last inserted id of table 1's file id column
.
.
34 0

35 34 or -1 [ actually here 2 need to comes]

JonB

@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.

Qt embedded developer

@JonB i know that table . it happens but some time it comes -1 or last inserted id of 2nd table instead 1st table's last inserted id