Special characters and empty fields from database

Merlot2309 · Jan 29, 2011

Hello,

Added row fields from an sql database in a listview and some fields contain names with a single quote, like Cetti's.
Values like this don't give any result with the next ExecQuery.
Special characters like é, ï and ñ work fine.

The database also contains several empty fields.
How can I skip those lines from being added to a Listview? (Using "laguages" as a var)

If Cursor.GetString(languages).Lenght > 0 Then
lvNames.AddSingleLine(Cursor.GetString(languages)
End if

Thanks,
Helen.

Erel · Jan 30, 2011

Can you upload a small program that demonstrates the missing rows?

About the empty fields, you can filter those out in your SQL query by adding a WHERE clause. Something like WHERE langauges != NULL.

nfordbscndrd · Jan 30, 2011

Merlot2309 said:
Added row fields from an sql database in a listview and some fields contain names with a single quote, like Cetti's.
Values like this don't give any result with the next ExecQuery.

I had the same problem and came up with this:

Your query looks like this to sqlite:

SELECT * FROM table WHERE Name = 'Cetti's'

where the apostrophe in Cetti's is taken to be the end of the Name.

Instead of using apostrophes (single quotes) as delimiters around the word, use double quotes:

SELECT * FROM table WHERE Name = "Cetti's"

The B4A code would look like this:

nm = "Cetti's"
Cursor = SQL1.ExecQuery("SELECT * FROM table WHERE name = " & QUOTE & nm & QUOTE)

Erel · Jan 31, 2011

The best solution is to use SQL.ExecuteQuery2 which lets you pass parameter outside of the query: Basic4android - SQL

nfordbscndrd · Jan 31, 2011

Erel said:
The best solution is to use SQL.ExecuteQuery2 which lets you pass parameter outside of the query: Basic4android - SQL

I looked at that and couldn't figure out how it worked -- mainly, what is supposed to be passed in the array, as I recall. I'll look at it again.

Erel · Feb 1, 2011

Here is a short example:

B4X:

Cursor = SQL1.ExecQuery2("SELECT * FROM table WHERE name = ?", Array As String("Cetti's"))

The values in the array replace the question marks.

nfordbscndrd · Feb 1, 2011

Erel said:
Here is a short example:

B4X:

Cursor = SQL1.ExecQuery2("SELECT * FROM table WHERE name = ?", Array As String("Cetti's"))

The values in the array replace the question marks.

Thanks. That is very helpful. I thought that "Array" meant that you to pass an array rather than a single parameter.

I assume that the main advantage over the method I posted is that in my method, if the parameter contains quotes, then using quotes as a delimiter as I did will still cause a problem (which I didn't consider) while passing the field separately eliminates any conflict between the delimiter and the contents of the parameter. Is that right?

Erel · Feb 1, 2011

I thought that "Array" meant that you to pass an array rather than a single parameter.

That is correct. In this specific case we have an array with one item. You can however have several question marks in your query and then you can pass an array with several items.

I assume that the main advantage over the method I posted is that in my method, if the parameter contains quotes, then using quotes as a delimiter as I did will still cause a problem (which I didn't consider) while passing the field separately eliminates any conflict between the delimiter and the contents of the parameter. Is that right?

This is one advantage. It also makes your code simpler as it easier to combine the variables in the query string. And it also protects against SQL injections which can happen if you take some string value from an untrusted source and directly add it to your query.

Merlot2309 · Feb 1, 2011

Hello gentlemen,

Brillant :sign0060:. The little app runs good and stable now.

To get it working I already changed cursor.GetString to cursor.GetString2 and selected the specific columns from the db.

The part of the code that may help others:

Cursor = ST.ExecQuery("Select * FROM Birds WHERE " & talen & " IS NOT NULL ORDER BY " & talen & " Asc")
lvNames.Clear

For i = 0 To Cursor.RowCount - 1
Cursor.Position = i
lvNames.AddSingleLine(Cursor.GetString(talen))
Next
Cursor.Close
End Sub
Sub lvNames_ItemClick(Position As Int, Value As Object)

vNaam = value
pnLV.Visible = False
StartActivity("Main")

Cursor = ST.ExecQuery2("Select ScientificName, NL, EN, ES, FR, DE, PT, IT, DK FROM Birds WHERE " & talen & " = ?", Array As String(vNaam))
Cursor.Position = 0

If Cursor.GetString2(0) <> Null Then
lblSC.Text = Cursor.GetString("ScientificName")
Else
lblSC.Text = "-"
End If

Thank you for your help,
Helen.

francoisg · Jun 30, 2011

Hi,
also you can do something like the following (replace single quote chars with two single quote chars) ...

dim mysql as SQL
dim s, v as string
v = "My 'test' string"
' *** Replace single ' charcater with double ' caracters ...
s = "insert into table values ('" & v.Replace("'", "''") & "')"
mysql.Initialize(File.DirDefaultExternal, "dbname.db", true)
mysql.ExecNonQuery(s)
.
.
.

Regards,
M. le Roux

nemethv · May 20, 2012

nfordbscndrd: Bit of a belated 'thanks' for that one...(though I never asked.)

Special characters and empty fields from database

Merlot2309

Active Member

Erel

B4X founder

nfordbscndrd

Well-Known Member

Erel

B4X founder

nfordbscndrd

Well-Known Member

Erel

B4X founder

nfordbscndrd

Well-Known Member

Erel

B4X founder

Merlot2309

Active Member

francoisg

Active Member

nemethv

Member

Similar Threads

Special characters and empty fields from database

Active Member

B4X founder

Well-Known Member

B4X founder

Well-Known Member

B4X founder

Well-Known Member

B4X founder

Active Member

Active Member

Member

Similar Threads

Privacy & Transparency

Privacy & Transparency