Android Question Generate a row of numbers and store them into SQLite

[JOTHA]

Hello forum community,

I would like to generate
a row of numbers and store them into SQLite
in each line.

Does anyone know why this is NOT going with this code:

    Cursor = SQL1.ExecQuery("SELECT Feld_006 FROM Abrechnung")
        For i = 0 To Cursor.RowCount -1
            Cursor.Position = i
           
            Dim Abrechnung_IDNR As String
            Abrechnung_IDNR = i +1        '-- Position of Cursor +1 --
           
        '-- Save to SQLite --
            SQL1.ExecNonQuery("UPDATE Abrechnung SET Feld_006 = '"&Abrechnung_IDNR&"' ") 'WHERE Feld_006 = '"&Cursor.Position&"' +1 ")
                    Log("-- 3196 -- Abrechnung-IDNR: "&Abrechnung_IDNR&"")
        Next
        Cursor.Close

In the log file the count are displayed , but in SQLite only the " last " IDNR is stored the same in all the rows .

Can anybody give advice?

 

[DonManfred]

If you want to STORE (implies for me insert), why are you use UPDATE.
Update will UPDATE one or more entries.

You should learn SQLite basics.

https://www.sqlite.org/lang_insert.html
https://www.sqlite.org/lang_update.html

 

[klaus]

In the log file the count are displayed , but in SQLite only the " last " IDNR is stored the same in all the rows .

That's normal, because you don't specify any row so you set the counter in all rows.

Try this code, I haven't tested it.

Private Curs As Cursor
Private rowI As Int
Private Abrechnung_IDNR As String
Curs = SQL1.ExecQuery("SELECT rowID FROM Abrechnung")
SQL1.BeginTransaction
Try
    For i = 0 To Curs.RowCount - 1
        Curs.Position = i
        Abrechnung_IDNR = i + 1        '-- Position of Cursor + 1
        rowI = Curs.GetInt2(0)
        SQL1.ExecNonQuery("UPDATE Abrechnung SET Feld_006 = '" & Abrechnung_IDNR & "' WHERE rowI = rowID"
    Next
    SQL1.TransactionSuccessful
Catch
    Log(LastException.Message) 'no changes will be made
End Try
SQL1.EndTransaction
Curs.Close

The code above has an error the correct one is in post#12

 

[JOTHA]

If you want to STORE (implies for me insert), why are you use UPDATE.
Update will UPDATE one or more entries.

You should learn SQLite basics.

https://www.sqlite.org/lang_insert.html
https://www.sqlite.org/lang_update.html

Hello DonManfred,
the translation was not correct (I know the difference between INSERT and UPDATE)
thank you for the links to www.sqlite.org

... and YES, I want to UPDATE, because if I INSERT I will generate a new row ...

@klaus: Hi klaus,

nice to hear from you, there is a long time ago since our last contact!

Thank you for your code

WHERE rowI = rowID

... I will try and respond the success!

 

[JOTHA]

Hello Klaus,

it didn't work. The errror message:

java.lang.NullPointerException: Attempt to invoke virtual method 'java.lang.String java.lang.String.trim()' on a null object reference

I'll try it again with some other code-experiments ...

 

[JOTHA]

Hi klaus,

rowI = Cursor.GetInt2(0)
Log("-- 3211 -- rowI: "&rowI&"")

... in the Log the rowI always says "0" ---> "rowI: 0"

 

[mc73]

Take a backup and then you may want to try this (one line nonQuery):

update Abrechnung SET Feld_006=(select count(1) from Abrechnung tbltemp where tbltemp.rowid<Abrechnung.rowid)+1

 

[JOTHA]

Hi folks,

the solution is as follows:

    Cursor = SQL1.ExecQuery("SELECT rowID FROM Abrechnung")
        For i = 0 To Cursor.RowCount -1
            Cursor.Position = i
            Dim Abrechnung_IDNR As String
            Abrechnung_IDNR = i +1
            SQL1.ExecNonQuery("UPDATE Abrechnung SET Feld_006 = '"&Abrechnung_IDNR&"' WHERE rowID = '"&Cursor.Position&"' +1 ")
        Next
Cursor.Close

Thanks again klaus for this Tip:

rowID

 

[JOTHA]

Hello mc73,
althougt my code works well, I'll try your code, because I#m interested in checking if it works ...

update Abrechnung SET Feld_006=(select count(1) from Abrechnung tbltemp where tbltemp.rowid<Abrechnung.rowid)+1

 

[JOTHA]

Hello mc73,

I've checked your code and it works ... but later in my app there is a message:

java.lang.NullPointerException: Attempt to invoke virtual method 'java.lang.String java.lang.String.trim()' on a null object reference

With your code the result is UPDATED in the SQLite, but maybe the type of the result is something like "virtual" and so I use my own code to get a better success.

Thank you for your response!

 

[Mahares]

the solution is as follows

I still do not get it.
You do this: Dim Abrechnung_IDNR As String and then turn around and do this:
UPDATE Abrechnung SET Feld_006 = '"&Abrechnung_IDNR&"'
This means that Feld_006 is always updated to a blank string. Are you sure you want to do that?

 

[klaus]

There is an error in the code in post#3, rowI is a variable.
The correct code is this:

Private Curs As Cursor
Private rowI As Int
Private Abrechnung_IDNR As String
Curs = SQL1.ExecQuery("SELECT rowID FROM Abrechnung")
SQL1.BeginTransaction
Try
    For i = 0 To Curs.RowCount - 1
        Curs.Position = i
        Abrechnung_IDNR = i + 1        '-- Position of Cursor + 1
        rowI = Curs.GetInt2(0)
        SQL1.ExecNonQuery("UPDATE Abrechnung SET Feld_006 = '" & Abrechnung_IDNR & "' WHERE " & rowI & " = rowID")
    Next
    SQL1.TransactionSuccessful
Catch
    Log(LastException.Message) 'no changes will be made
End Try
SQL1.EndTransaction
Curs.Close

The propblem with your code is if once you delete a row the rowIDs will not change!

EDIT: Amended the code after the report in post#16.

 

[mc73]

Hello mc73,

I've checked your code and it works ... but later in my app there is a message:

With your code the result is UPDATED in the SQLite, but maybe the type of the result is something like "virtual" and so I use my own code to get a better success.

Thank you for your response!

What do you mean with "later"? You should check and debug the piece of code that brings up this error (if of course it is not the query itself which produces this error).

 

[Mahares]

What do you mean with "later"? You should check and debug

Hi mc73. Here is your code . If he used it like this, it works very well.

For i = 0 To curs.RowCount - 1
           curs.Position = i
            SQL1.ExecNonQuery("update Abrechnung SET Feld_006=(select count(1) from Abrechnung tbltemp where tbltemp.rowid < Abrechnung.rowid)+1")
       Next

Also, @klaus code in post #12 works well. Both of your codes are correct as I tested both. Good to hear from you again. Don't be a stranger. We need your input in the forum like you used to.

 

[mc73]

Hi mc73. Here is your code . If he used it like this, it works very well.

For i = 0 To curs.RowCount - 1
           curs.Position = i
            SQL1.ExecNonQuery("update Abrechnung SET Feld_006=(select count(1) from Abrechnung tbltemp where tbltemp.rowid < Abrechnung.rowid)+1")
       Next

Also, @klaus code in post #12 works well. Both of your codes are correct as I tested both. Good to hear from you again. Don't be a stranger. We need your input in the forum like you used to.

But, my friend, Mahares, why using a loop? The query I posted, internally loops through all records.

 

[JOTHA]

The propblem with your code is if once you delete a row the rowIDs will not change!

I need the rowIDs just temporarely for a calculation in the columns. After the calculation is done, these rowIDs are not necessary again.

P.S.: @klaus -- I've tested your code ... it works when you add a paranthese at the end of that line:

...
WHERE " & rowI & " = rowID")

 

[JOTHA]

What do you mean with "later"?

This Message is generated in the Log:

main_abrechnung_nachbearbeiten (java line: 537)
java.lang.NullPointerException: Attempt to invoke virtual method 'java.lang.String java.lang.String.trim()' on a null object reference
at java.lang.StringToReal.parseDouble(StringToReal.java:263)
at java.lang.Double.parseDouble(Double.java:301)
at jothasoft.shoporganizer.android.main._abrechnung_nachbearbeiten(main.java:537)
at jothasoft.shoporganizer.android.main._panel_099_einstellungen_button_abrechnung_click(main.java:3887)
at jothasoft.shoporganizer.android.main._button1_click(main.java:927)
at java.lang.reflect.Method.invoke(Native Method)
at java.lang.reflect.Method.invoke(Method.java:372)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:169)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:157)
at anywheresoftware.b4a.BA.raiseEvent(BA.java:153)
at anywheresoftware.b4a.objects.ViewWrapper$1.onClick(ViewWrapper.java:78)
at android.view.View.performClick(View.java:4856)
at android.view.View$PerformClick.run(View.java:20268)
at android.os.Handler.handleCallback(Handler.java:815)
at android.os.Handler.dispatchMessage(Handler.java:104)
at android.os.Looper.loop(Looper.java:194)
at android.app.ActivityThread.main(ActivityThread.java:5624)
at java.lang.reflect.Method.invoke(Native Method)
at java.lang.reflect.Method.invoke(Method.java:372)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:959)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:754)
java.lang.NullPointerException: Attempt to invoke virtual method 'java.lang.String java.lang.String.trim()' on a null object reference

I mean with "later" ... there is a calculation formula to get the sum of a column, after the rows are calculated with a multiplication from the number of items in every row.

(if of course it is not the query itself which produces this error)

I believe you ...

 

[klaus]

P.S.: @klaus -- I've tested your code ... it works when you add a paranthese at the end of that line:
WHERE " & rowI & " = rowID")

Sorry, I missed that one.
I amended the code in post#12.
The rowID is an internal numbering of the rows in a database which begins with 1.
If you add five rows, the rowIDs will be 1,2,3,4,5.
Now, if you remove row 3, the rowIDs will be 1,2,4,5.
The row with rowID = 3 is removed but all the other rowIDs remain the same and are univoque.
In the second example if you use your code you will get an error because rowID = 3 doesn't exist anymore.

 

[JOTHA]

For i = 0 To curs.RowCount - 1
curs.Position = i
SQL1.ExecNonQuery("update Abrechnung SET Feld_006=(select count(1) from Abrechnung tbltemp where tbltemp.rowid < Abrechnung.rowid)+1")
Next

... is the same Error-Message as at the code from mc73 ... but this is really not your fault, because you dont know the rest of the code an all my own possible mistakes in the rest of the code!

But thank you all for the solutions and the help ... and the discussion ... great forum!

 

[JOTHA]

but all the other rowIDs remain the same and are univoque.

klaus, your code is the better way, of course!

For the case that I will need to remove any row in the future, this is the better solution.
For my case (I told before) I just need this temporarely ... but I'll use now your solution. Thank you!