B4J Question SQL check rows problem

[Peter Lewis]

Hi
I have many subs where this is workign but all of a sudden , this does not work. I have checked the strings are the same and still it does not pick up that there are records already in the database. If there is already the same date (String) in the database then it should do nothing. If the result is equal to 0 then it should insert...
This is from the log

Today is: 05/20/2017
05/20/2017

and the fields in the database are TEXT for the Date and Numberic for the Rate


Here is the snip of code

Thank you

    txtDte.Text=DateTime.Date(DateTime.Now)
    Log(txtDte.Text)
   
    If sql1.ExecQuerySingleResult("SELECT count(*) FROM xrate WHERE Date = "&txtDte.text) <> 0 Then
       
    Else
       
        sql1.ExecNonQuery2("INSERT INTO xrate VALUES (?,?)", Array As Object(txtDte.text, xrate)) ' add field
       
    End If

 

[EnriqueGonzalez]

most likely, this is returning an object:

sql1.ExecQuerySingleResult("SELECT count(*) FROM xrate WHERE Date = "&txtDte.text)

and an object compared to a int will always be false. ie.

dim var1 as string = "0"
dim var2 as int = 0

log(var1 = var2) ' this will be false

you can force to be an int like this:

dim counter as int = sql1.ExecQuerySingleResult("SELECT count(*) FROM xrate WHERE Date = "&txtDte.text)

 

[Peter Lewis]

most likely, this is returning an object:

sql1.ExecQuerySingleResult("SELECT count(*) FROM xrate WHERE Date = "&txtDte.text)

and an object compared to a int will always be false. ie.

dim var1 as string = "0"
dim var2 as int = 0

log(var1 = var2) ' this will be false

you can force to be an int like this:

dim counter as int = sql1.ExecQuerySingleResult("SELECT count(*) FROM xrate WHERE Date = "&txtDte.text)

I updated the code to include what you suggested and it does not count any , even though it is in the database. It gives an error that the data is already in and connot add due to duplication

java.sql.SQLException: [SQLITE_CONSTRAINT] Abort due to constraint violation (column Date is not unique)

        Dim counter As Int = sql1.ExecQuerySingleResult("SELECT count(*) FROM xrate WHERE Date = "&txtDte.text)
        If counter = 1 Then
        Log("already in DB")
    Else
            sql1.ExecNonQuery2("INSERT INTO xrate VALUES (?,?)", Array As Object(txtDte.text, xrate)) ' add field
                       
    End If

 

[EnriqueGonzalez]

whats the log? for

log(counter)

if you use

sql1.ExecQuerySingleResult2("SELECT count(*) FROM xrate WHERE Date = ?",array as string(txtDte.text))

does it change?

 

[Peter Lewis]

whats the log? for

log(counter)

if you use

sql1.ExecQuerySingleResult2("SELECT count(*) FROM xrate WHERE Date = ?",array as string(txtDte.text))

does it change?

the Log(counter) will be to check how many records in the database have this date. If non then add the date

with
sql1.ExecQuerySingleResult2("SELECT count(*) FROM xrate WHERE Date = ?",array as string(txtDte.text)) there is no variable counter

UPDATE:::

Tried with the new format as String and it worked..

Thank you

 

[EnriqueGonzalez]

No no, i am sorry i did not explain myself

this is to check in your logs, what value is holding the counter variable

Dim counter As Int = sql1.ExecQuerySingleResult("SELECT count(*) FROM xrate WHERE Date = "&txtDte.text)
log("dates in db " &counter)

this is just a change.

Dim counter As Int = sql1.ExecQuerySingleResult2("SELECT count(*) FROM xrate WHERE Date = ?",array as string(txtDte.text))
log("dates in db " &counter)

 

[Peter Lewis]

No no, i am sorry i did not explain myself

this is to check in your logs, what value is holding the counter variable

Dim counter As Int = sql1.ExecQuerySingleResult("SELECT count(*) FROM xrate WHERE Date = "&txtDte.text)
log("dates in db " &counter)

this is just a change.

Dim counter As Int = sql1.ExecQuerySingleResult2("SELECT count(*) FROM xrate WHERE Date = ?",array as string(txtDte.text))
log("dates in db " &counter)

So from what I have seen here, I should always use the ?,?,? and either Array as String or Array as object ?

 

[EnriqueGonzalez]

So from what I have seen here, I should always use the ?,?,? and either Array as String or Array as object ?

Yes!
Totally, it is safer to use, both for avoiding hacks and to avoid manually parsing values.

A joke on not using the ?
https://xkcd.com/327/

 

[Peter Lewis]

Yes!
Totally, it is safer to use, both for avoiding hacks and to avoid manually parsing values.

A joke on not using the ?
https://xkcd.com/327/

Ouch, i have a lot of code to change...LOL

 

[Daestrum]

I think your sql line in post #1 would have worked had you quoted the date string ie

 If sql1.ExecQuerySingleResult("SELECT count(*) FROM xrate WHERE Date = '" & txtDte.text & "'") <> 0 Then

 

[Erel]

Never build queries like this.

It should be:

If sql1.ExecQuerySingleResult2("SELECT count(*) FROM xrate WHERE Date = ?", Array As String(txtDte.Text)) <> 0 Then

No need to worry about escaping characters such as the single quote character and no need to worry about SQL injection.