SQL: how to determine valid return value

[Steve Wood]

How do you determine if a SELECT statement with a WHERE clause returned any records? For instance:

"select name from company where id = 1000"

Will either return one record, or no records.

Need to know before attempting to use values in the record set. If the recordset is empty it throws an error rather than returning a nice "null" or 0.

------------EXAMPLE--------------
'example if the table does not contain a record with ID = 1000
mySelect = "select name from company where id = 1000"
conn.execute(mySelect)
rs=conn.ResultSet
?rs.data(1)
ERROR: Result set row requested not found while getting result set data column.'

------------------

Also, I've lobbied Martin to create an SQL forum on our board, what do you think?

[mmaisterrena]

Re: SQL: how to determine valid return value

mySelect = "select name from company where id = 1000"
sql_execute = conn.execute(mySelect)
rs=conn.ResultSet

if the query returns anything sql_execute variable will return = t

[Steve Wood]

Re: SQL: how to determine valid return value

That's what you would think glancing at the Alpha Help examples, but it is wrong. It returns 1 as long as the statement is valid, nothing to do with the number of records returned. This is why I test.

[marvinbase]

Re: SQL: how to determine valid return value

This works in Q&D testing with my database.

'example if the table does not contain a record with ID = 1000
mySelect = "select name from company where id = 1000"
conn.execute(mySelect)
rs=conn.ResultSet
if rs.tostring() = "" then
'error handler
end if
?rs.data(1)

[Steve Wood]

Re: SQL: how to determine valid return value

A variation of that will work. You didn't mention rs.tostring, you said that conn.execute would return true if a record was found, which is incorrect.

I found a solution that looks similar using the count function:

mySelect = "select count(*),id FROM company where id = xxx"
?rs.data(1)
= 0 'if not found

[mmaisterrena]

Re: SQL: how to determine valid return value

Sorry, BTW Actually Jeff and I are different persons ;)

you can also use:

mySelect = "select name from company where id = 1000"
data = conn.Tostring(mySelect)
w_count(data,crlf())

it will return the number of resulting rows, plus you have the actual data available in the "data" variable

[Steve Wood]

Re: SQL: how to determine valid return value

Sorry, I've been awake since for 28 hours working on the same project conversion from DBF to MySQL. Its really mind boggling converting SET-based xbasic routines to SQL.

[Steve Wood]

Re: SQL: how to determine valid return value

On additional testing, putting count(*) in the list of fields to return always renders ONE record. The record does include the proper count though. So to use this method you have to run the query twice, once with only count(*) in the field list. Here is part of my select statement to illustrate:

This will yield a list of field values

select mbi.id,mbi.name,mbio.id,mbio.name
from menu_bundleitems as mbi

inner join menu_bundleitemoptions as mbio
on mbio.BUNDLEITEM_ID=mbi.ID
where mbi.id = 100002 
order by mbi.sequence, mbio.sequence

This just lists one record, but with the proper count:

select count(*)
from menu_bundleitems as mbi

inner join menu_bundleitemoptions as mbio
on mbio.BUNDLEITEM_ID=mbi.ID
where mbi.id = 100002 
order by mbi.sequence, mbio.sequence

Your examples are easier than running the query twice, but I wonder which is faster for the computer, converting the results in to a string in order to count, or re-running the whole query twice.

[Pat Bremkamp]

Re: SQL: how to determine valid return value

Steve,

Hard to know which is faster, but SELECT statements run very fast, so I'm guessing that the real difference is not very much. The connection.toString() is an Alpha function that must be running the select to get the result set, and may even be doing the count as part of the function in order to prevent an error.

When I run the count, I usually just count one field, typically the record id, rather than using count(*).

There is also a technique using EXISTS with a subquery, but I've never tried it. It yields a true/false, so I think you still need to run two selects.


Pat

[JerryBrightbill]

Re: SQL: how to determine valid return value

If you want to get a result set into a character string, use ResultSet.toString. This is a real common method to build lists for dropdowns, checkbox, and radio controls. You can also use ResultSet.toPropertyArray to build an array. This will retain the data type for each element in the result set and will size the array to the number of records returned.

If you want to step through the resultSet or just see if the SELECT returned any records, just use ResultSet.NextRow(). Here is a simple example using the Access AlphaSports to see if a single record is returned.

dim Fname as c
dim cn as sql::connection
if cn.open("::name::connection1")
	if cn.Execute("SELECT Firstname FROM Customer WHERE customer_id = 26") 'should have one record
		if cn.ResultSet.nextRow() 'places cursor on first row
			fname = cn.ResultSet.data("Firstname")
		end if
	end if
	cn.close()
end if 
ui_msg_box("",fname)

When the result set is returned, the cursor is not initially on any row, so the first nextNow() just places the cursor on the first row, if one exists. If there are no records, the nextRow() will return false. If you wanted to return a list of first names, you could use ResultSet.toString() or something like this

dim Fname as c
dim cn as sql::connection
if cn.open("::name::connection1")
	if cn.Execute("SELECT Firstname FROM Customer WHERE customer_id > 26") 'multiple
		while cn.ResultSet.nextRow() 
			fname = fname + cn.ResultSet.data("Firstname")+crlf()
		end while
	end if
	cn.close()
end if 
ui_msg_box("",fname)

[Bob Moore]

Re: SQL: how to determine valid return value

On additional testing, putting count(*) in the list of fields to return always renders ONE record. The record does include the proper count though.

Here is a helpful reference on the SQL count function:

http://www.techonthenet.com/sql/count.php

However, if you are just trying to determine the number of rows in the result set, you can check the ResultSet.RowCount property.

[Steve Wood]

Re: SQL: how to determine valid return value

Good advice, needed. I've been dropping results (if more than one record) to a property array because that's the only way I know how to iterate through the results

[mmaisterrena]

Re: SQL: how to determine valid return value

I use something like this

mySelect = "select name from company where id = 1000"
data = conn.Tostring(mySelect)
n = w_count(data,crlf())

for i=1 to n
Value = word(data,i,crlf())
some action here
next

[Ezslim]

Re: SQL: how to determine valid return value

Steve,
Here is how I iterate through the results returned by mysql

dim cn as sql::connection
cn.open("::name::connection1")

cn.Execute("SELECT Firstname FROM Customer WHERE customer_id > 26") 'multiple

rs=cn.resultset
if eval_valid("rs.currentrow") 'if valid, there is data to process; if not, sql statement did not return any rows
rows_to_process=rs.rowcount
loopflag=.t.
while loopflag
'do work here on the data
loopflag=rs.nextrow()
end while
end if
cn.close()

[JerryBrightbill]

Re: SQL: how to determine valid return value

Here is a helpful reference on the SQL count function:

http://www.techonthenet.com/sql/count.php

However, if you are just trying to determine the number of rows in the result set, you can check the ResultSet.RowCount property.

Some SQL sources such as Access and SQL Server do not return the row count so ResultSet.RowCount will always return -1

dim cn as sql::connection
?cn.open("::name::connection1")
= .T.

?cn.execute("Select Firstname from customer")
= .T.

?cn.ResultSet.RowCount
= -1

Therefore, it is an unreliable way to get the count of records in a select.