How to tell if item is not in database?

Databases
#1

I have some code like this:

dim ol as recordset= db.SQLSelect(“select * from users where email=’”+TextField1.text.trim+"’")
if ol.Field(“email”)=nil then
msgbox “not here!”
exit function
end if

This isn’t firing if the email (which is TextField1.text) is not listed.
What syntax should I use for

if ol.Field("email")=nil then
#2

If there are no rows, the RecordSet will have a RecordCount of 0.

#3

dim ol as recordset= db.SQLSelect(“select * from users where email=’”+TextField1.text.trim+"’")
if ol.EOF then
msgbox “not here!”
exit function
end if

#4

Xojo docs states that sometimes it can falsely return -1 (not available) depending on which database you are using, e.g. PostGreSQL and some ODBC drivers.

#5

“Select count(*) from users where email=’”+TextField1.text.trim+"’)"

You will get a 0 if there are no records with that email address. This would work with any DB engine.

#6

Just a side note: where email='" + TextField.text + "'" is very dangerous. You should always use prepared statements when using user supplied data in your query.

#7

actually, it’s looking for an email address, I could have used any number of different fields :slight_smile:
But thanks guys :slight_smile:

#8

Jeremy is right, better use something like

Function SQL_Friendly(data as String) As String data = ReplaceAll(data, "'", "''") Return data End Function
when the user is in control of the input. :wink:

#9

[quote=20273:@Sascha S]Jeremy is right, better use something like

Function SQL_Friendly(data as String) As String data = ReplaceAll(data, "'", "''") Return data End Function
when the user is in control of the input. ;-)[/quote]
NO!!!

While it’s better than nothing, using prepared statements is the correct solution.

#10

So, for mySQL you would recommend:

[code]Function SQL_Friendly(data as String) As PreparedSQLStatement
Dim ps As PreparedSQLStatement = db.Prepare(“SELECT * FROM Contacts WHERE email = ?”)

ps.BindType(0, MySQLPreparedStatement.MYSQL_TYPE_STRING)

ps.Bind(0, data)
Return ps
End Function
[/code]

Coded on the fly, outside of ide = untested

#11

Dim ps As PreparedSQLStatement = db.Prepare("SELECT * FROM Contacts WHERE email = ?") Dim rs As RecordSet = ps.SQLSelect(TextField1.Text)

Although I do not use MySQL, I am assuming the above works on MySQL. I use PostgreSQL and SQLite.

#12

I think the class in MySQL is MySQLPreparedStatement, but Jeremy has the right idea.

It’s hard to write a generic convenience function for this. At first glance, you could define an extension method like SQLSelect(Query As String, ParamArray Data() As Variant) but variant arrays can prove to be problematic and more trouble than it is worth. You’re better off just getting into the habit of writing new code with prepared statements.

FWIW, I’ve found using them produces shorter lines and easier to read code, at the expensive of an additional line or two.

Also be aware that different engines have different parameter markings. PostgreSQL uses $1, $2, etc which I prefer actually. You’re able to reuse the same parameter multiple times in the same query. For example WHERE (col1 = $1 AND col2 = $2) OR (col1 = $2 AND col2 = $1) and only need to provide the values once.

#13

and with user input you should use like not equals I know many people that use both Caps and Mins in email addresses as for the most part they are not case sensitive.

#14

A LIKE will not help with case insensitivity. You should use the LOWER fiction instead.

#15

Also, don’t do Select * as there is no need to push the entire record over the wire if all you want to know is if it exists. Select 1 serves the same purpose.

#16