Special characters and empty fields from database

[Merlot2309]

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]

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]

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]

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

 

[nfordbscndrd]

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]

Here is a short example:

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

The values in the array replace the question marks.

 

[nfordbscndrd]

Here is a short example:

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]

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]

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]

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]

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