B4J Question SQLite not updating value - what am I doing wrong?

[kostefar]

Dear All,

The code below:

Log (d)
                Log (rs.GetInt ("rowid"))
                sql3.ExecNonQuery ("UPDATE table2 Set difference = " & d & " WHERE rowid = " & rs.GetInt ("rowid"))
                Log (sql3.ExecQuerySingleResult ("SELECT difference FROM table2 LIMIT 1 OFFSET " & (rs.GetInt ("rowid"))))

Shows the following in the log:

1.000723
516
(null string)

When logging the content of "difference" in the given rowid, it shows (null string) and not as expected 1.000723, any idea why that is?

FYI, "difference" is defined as REAL in table2.

Thanks in advance!

 

[behnam_tr]

sql3.ExecNonQuery2 ("UPDATE table2 Set difference =? WHERE rowid = ?",array(diffvalue,rowidvalue))

 

[kostefar]

sql3.ExecNonQuery2 ("UPDATE table2 Set difference =? WHERE rowid = ?",array(diffvalue,rowidvalue))

Thanks, but the result is the same. The code now looks like this:

                Log (d)
                Log (rs.GetInt ("rowid"))
                'sql3.ExecNonQuery ("UPDATE table2 Set difference = " & d & " WHERE rowid = " & rs.GetInt ("rowid"))
                sql3.ExecNonQuery2 ("UPDATE table2 Set difference =? WHERE rowid = ?", Array (d, rs.GetInt ("rowid")))
                Log (sql3.ExecQuerySingleResult ("SELECT difference FROM table2 LIMIT 1 OFFSET " & (rs.GetInt ("rowid"))))

And the log says:

1.00051814
497
(null string)

 

[behnam_tr]

Thanks, but the result is the same. The code now looks like this:

                Log (d)
                Log (rs.GetInt ("rowid"))
                'sql3.ExecNonQuery ("UPDATE table2 Set difference = " & d & " WHERE rowid = " & rs.GetInt ("rowid"))
                sql3.ExecNonQuery2 ("UPDATE table2 Set difference =? WHERE rowid = ?", Array (d, rs.GetInt ("rowid")))
                Log (sql3.ExecQuerySingleResult ("SELECT difference FROM table2 LIMIT 1 OFFSET " & (rs.GetInt ("rowid"))))

And the log says:

1.00051814
497
(null string)

What is the data type of the difference field in the database?
The record with number 497 is available in the database??

 

[aeric]

Log (sql3.ExecQuerySingleResult ("SELECT difference FROM table2 LIMIT 1 OFFSET " & (rs.GetInt ("rowid"))))

Not sure why you use OFFSET to check.

Can you try check using rowid?

Log(sql3.ExecQuerySingleResult2("SELECT difference FROM table2 WHERE rowid = ?", Array As String(rs.GetInt("rowid"))))

 

[kostefar]

Not sure why you use OFFSET to check.

Can you try check using rowid?

Log(sql3.ExecQuerySingleResult2("SELECT difference FROM table2 WHERE rowid = ?", Array As String(rs.GetInt("rowid"))))

Thanks mate, that worked!
Well, it´s been 8 years since I fiddled with SQL the last time, so I´m patching my knowledge with some code here and there, and apparently the OFFSET way of doing this was the wrong one. Any idea btw. why that´s not working? Then perhaps I could learn a bit.

Also, why not use

Log (sql3.ExecQuerySingleResult("SELECT difference FROM table2 WHERE rowid = " & (rs.GetInt("rowid"))))

Are there any advantages of the suggested method?

 

[kostefar]

What is the data type of the difference field in the database?
The record with number 497 is available in the database??

The datatype is REAL, and the record was available indeed.
The problem was something else, aeric gave me the solution.
But just out of curiosity: Is there a reason to believe that the Execnonquery2 way of doing things is more reliable, correct, efficient etc. than the original one with Execnonquery?

 

[aeric]

Are there any advantages of the suggested method?

No different but I think if your query involves quotes then it makes the code more clean and avoid mistakes.

 

[kostefar]

No different but I think if your query involves quotes then it makes the code more clean and avoid mistakes.

Thanks, that makes good sense!