Android Question Problem with SQL and debugging

[grafsoft]

Hi,

I create a table with

t = "CREATE TABLE if not exists sessions (ID INTEGER PRIMARY KEY, zeit text, name text)"
 sql1.ExecNonQuery (t)

This works. Then I try to insert values

t="insert into sessions values (NULL, '" & dt & "','" & EditText1.Text & "')"
 Log (t)
' Shows "insert into sessions values (NULL, '2022.08.18 12:35:13','test')"
 Try
       Tracker.sql1.ExecNonQuery (t)
 Catch
       Log (LastException.Message) ' Debugger does not get here
       ' Message is not logged
 End Try
Dim cs As Cursor
' Activity freezes at the above line. But the error is in the sql.insert statement. If I omit the try ... catch it freezes after        Tracker.sql1.ExecNonQuery (t)

I have a little experience with B4A and SQL, do not expect to make big errors. But I have no idea about how to proceed further.

 

[LucaMs]

I'm too tired to read (well).
Use ALWAYS version 2 of queries, the parametric ones (Tracker.sql1.ExecNonQuery2).

 

[aeric]

Code Smell detected.

1. I don't recommend writing query like this

t="insert into sessions values (NULL, '" & dt & "','" & EditText1.Text & "')"

I recommend :

Dim strSQL As String = "INSERT INTO sessions (zeit , name) VALUES (?, ?)"

Then:

Try
    Dim strName As String = EditText1.Text.Trim
    Tracker.sql1.ExecNonQuery2 (strSQL, Array As String(dt, strName))
Catch
    Log (LastException.Message) ' Debugger does not get here
    ' Message is not logged
End Try

Avoid using Cursor

Dim cs As Cursor

Use Resultset instead. But not sure why you need this line since you are not executing an ExecQuery.

 

[grafsoft]

Thanks to both of you!

What I am wondering: In every programming or database language I know a statement is WRONG (produces errors) or RIGHT. Of course there are also criteria such as speed, readability, compatibility and the like. But with this SQL I often read only RECOMMENDED, And the not recommended statements are likely to produce errors or funny results ... but not always ...

 

[aeric]

Your code should work.
Why it is not working may due to other factor.
Since you didn’t provide the complete project, we can only guess and and advice better way to avoid problem, when your input values are invalid.

 

[Heuristx]

Thanks to both of you!

What I am wondering: In every programming or database language I know a statement is WRONG (produces errors) or RIGHT. Of course there are also criteria such as speed, readability, compatibility and the like. But with this SQL I often read only RECOMMENDED, And the not recommended statements are likely to produce errors or funny results ... but not always ...

What is dt? I guess it wants to be a DateTime, but where is it assigned?

 

[Heuristx]

Thanks to both of you!

What I am wondering: In every programming or database language I know a statement is WRONG (produces errors) or RIGHT. Of course there are also criteria such as speed, readability, compatibility and the like. But with this SQL I often read only RECOMMENDED, And the not recommended statements are likely to produce errors or funny results ... but not always ...

never mind, I see the log.

 

[DonManfred]

I guess it wants to be a DateTime

the column is defined as TEXT. See #1

 

[Heuristx]

Hi,

I create a table with

t = "CREATE TABLE if not exists sessions (ID INTEGER PRIMARY KEY, zeit text, name text)"
 sql1.ExecNonQuery (t)

This works. Then I try to insert values

t="insert into sessions values (NULL, '" & dt & "','" & EditText1.Text & "')"
 Log (t)
' Shows "insert into sessions values (NULL, '2022.08.18 12:35:13','test')"
 Try
       Tracker.sql1.ExecNonQuery (t)
 Catch
       Log (LastException.Message) ' Debugger does not get here
       ' Message is not logged
 End Try
Dim cs As Cursor
' Activity freezes at the above line. But the error is in the sql.insert statement. If I omit the try ... catch it freezes after        Tracker.sql1.ExecNonQuery (t)

I have a little experience with B4A and SQL, do not expect to make big errors. But I have no idea about how to proceed further.

How to proceed further:

1) You can test a simple case: t = "INSERT INTO Sessions VALUES(NULL, 'abc', xyz')"

You will probably see that this ExecNonQuery line has nothing to do with the problem.
But what is Tracker? What object? Is it initialized? In what state is it?
What about Tracker.sql1? Is it initailized? Opened? In what state? Are there transactions or open ResultSets on it? What is going on in the program before the lines you posted?