Android Question sqlite read data from database

hamedaz · Jun 6, 2016

Hi you dear friends
I've written this code. I want to get information from the database based on the following conditions. But I am faced with the following error message.

android.database.sqlite.SQLiteException: near "of": syntax error (code 1): , while compiling: SELECT * FROM idom WHERE name=Horse of a different color

B4X:

b="Horse of a different color"
cur1=sql1.ExecQuery("SELECT * FROM idom WHERE name="&b)
For i=0 To cur1.RowCount -1
lbl.Initialize("")
        'lbl.Gravity=Gravity.CENTER_HORIZONTAL
        lbl.Gravity= Bit.Or(Gravity.CENTER_HORIZONTAL,Gravity.FILL)

        lbl.TextSize=20
        lbl.TextColor=Colors.Black
        lbl.Text=cur1.GetString("text")
next

Erel · Jun 6, 2016

Always prefer parameterized queries:

B4X:

cur1 = sql1.ExecQuery2("SELECT * FROM ido WHERE name = ?" ,Array As String(b))

hamedaz · Jun 6, 2016

Erel said:
Always prefer parameterized queries:

B4X:

cur1 = sql1.ExecQuery2("SELECT * FROM ido WHERE name = ?" ,Array As String(b))

I've used this code, I do not get an error message. But empty information from the database will show me

Erel · Jun 6, 2016

This means that there is no record where the name field equals to "Horse of a different color".

hamedaz · Jun 6, 2016

Erel said:
This means that there is no record where the name field equals to "Horse of a different color".

But the amount of data available in table

Erel · Jun 6, 2016

What is the output of:

B4X:

Dim rs As ResultSet = sql.ExecQuery("SELECT name FROM ido")
Do While rs.NextRow
   Log(rs.GetString("name"))
Loop
rs.Close

hamedaz · Jun 6, 2016

Erel said:
What is the output of:

B4X:

Dim rs As ResultSet = sql.ExecQuery("SELECT name FROM ido") Do While rs.NextRow Log(rs.GetString("name")) Loop rs.Close

The output is as follows:
Horse of a different color
Horse of a different color

mangojack · Jun 6, 2016

@Erel ... slightly confused . any difference between ResultSet and Cursor in B4A / B4j. ?

@hamedaz .. try

B4X:

b="Horse of a different color"
    cur1 = SQL1.ExecQuery2("SELECT * FROM idom WHERE name = ?" ,Array As String(b))  '?? table name = idom / ido
   
    Log (cur1.RowCount)   
    For i = 0 To Cursor1.RowCount - 1
        Cursor1.Position = i   
        log(cur1.GetString("name"))    '?? there is a Column / field =  "text"  or "name"    
    Next

hamedaz · Jun 6, 2016

0"mangojack said:

@Erel ... slightly confused . any difference between ResultSet and Cursor in B4A / B4j. ?

@hamedaz .. try

B4X:

b="Horse of a different color"
    cur1 = SQL1.ExecQuery2("SELECT * FROM idom WHERE name = ?" ,Array As String(b))  '?? table name = idom / ido
  
    Log (cur1.RowCount)  
    For i = 0 To Cursor1.RowCount - 1
        Cursor1.Position = i  
        log(cur1.GetString("name"))    '?? there is a Column / field =  "text"  or "name"   
    Next

I'm writing code in b4a
You wrote commands output were as follows:
0

Erel · Jun 6, 2016

mangojack said:
any difference between ResultSet and Cursor in B4A / B4j. ?

ResultSet is a small extension to Cursor. The advantage of code based on ResultSet is that it is compatible with B4i and B4J.

@hamedaz please upload the database file.

mangojack · Jun 6, 2016

are you able to upload you db ?

hamedaz · Jun 6, 2016

Erel said:
ResultSet is a small extension to Cursor. The advantage of code based on ResultSet is that it is compatible with B4i and B4J.

@hamedaz please upload the database file.

This link database files were
http://s7.picofile.com/d/1e0f20ea-1787-4307-ac36-0dbfd9d08fe4/s.rar

http://expirebox.com/download/da597a806c0a9411a132ba790e849202.html

hamedaz · Jun 6, 2016

mangojack said:
are you able to upload you db ?

apload file:
http://expirebox.com/download/da597a806c0a9411a132ba790e849202.html

mangojack · Jun 6, 2016

Your db is good .. try

B4X:

Dim b as string ="Horse of a different color"
 cur1 = SQL1.ExecQuery2("SELECT * FROM idom WHERE name = ?" ,Array As String(b)) 
 Log (cur1.RowCount)  'Should = 2

For i = 0 To cur1.RowCount - 1
  cur1.Position = i 
  Log(cur1.GetString("text"))
Next

hamedaz · Jun 6, 2016

mangojack said:

Your db is good .. try

B4X:

Dim b as string ="Horse of a different color"
cur1 = SQL1.ExecQuery2("SELECT * FROM idom WHERE name = ?" ,Array As String(b))
Log (cur1.RowCount)  'Should = 2

For i = 0 To cur1.RowCount - 1
  cur1.Position = i
  Log(cur1.GetString("text"))
Next

I get the following output again:
0

mangojack · Jun 6, 2016

hamedaz said:
I get the following output again: 0

Can you upload project IDE > File > Export as Zip ... or paste a segment of code .

hamedaz · Jun 6, 2016

mangojack said:
Can you upload project IDE > File > Export as Zip ... or paste a segment of code .

I uploaded my project:
http://expirebox.com/download/551c470906d38bc4f3a1ee15eb4b1515.html

fixit30 · Jun 6, 2016

Sound's to me like you have an old/empty copy of the database on your device.

Try uninstalling the app and installing a fresh copy and try again.

hamedaz · Jun 6, 2016

fixit30 said:
Sound's to me like you have an old/empty copy of the database on your device.

Try uninstalling the app and installing a fresh copy and try again.

I am using the simulator

fixit30 · Jun 6, 2016

Hi Hamedaz.

The problem is with your database file. It contains two carriage returns after the text, and therefore is not an exact match for your search text.

Try the corrected database attached, or change your code to:

B4X:

b = "%Horse of a different color%"
cur1=sql1.ExecQuery2("SELECT * FROM idom WHERE name Like ?", Array As String(b))

Android Question sqlite read data from database

Member

B4X founder

Member

B4X founder

Member

B4X founder

Member

Expert

Member

B4X founder

Expert

Member

Member

Expert

Member

Expert

Member

Active Member

Member

Active Member

Attachments

Similar Threads

Privacy & Transparency

Privacy & Transparency