Android Question Problem with SQL and debugging

grafsoft · Aug 18, 2022

Hi,

I create a table with

B4X:

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

B4X:

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 · Aug 18, 2022

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

LucaMs · Aug 18, 2022

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

aeric · Aug 18, 2022

Code Smell detected.

1. I don't recommend writing query like this

SQL:

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

I recommend :

B4X:

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

Then:

B4X:

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

B4X:

Dim cs As Cursor

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

grafsoft · Aug 18, 2022

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 · Aug 18, 2022

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 · Aug 18, 2022

grafsoft said:
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 · Aug 18, 2022

grafsoft said:
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 · Aug 18, 2022

Heuristx said:
I guess it wants to be a DateTime

the column is defined as TEXT. See #1

Heuristx · Aug 18, 2022

grafsoft said:

Hi,

I create a table with

B4X:

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

B4X:

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?

Android Question Problem with SQL and debugging

grafsoft

Well-Known Member

LucaMs

LucaMs

Expert

aeric

Expert

grafsoft

Well-Known Member

aeric

Expert

Heuristx

Active Member

Heuristx

Active Member

DonManfred

Expert

Heuristx

Active Member

Similar Threads

Android Question Problem with SQL and debugging

Well-Known Member

Expert

Expert

Well-Known Member

Expert

Active Member

Active Member

Expert

Active Member

Similar Threads

Privacy & Transparency

Privacy & Transparency