[Gambas-user] Database Results

[Caveat]

Hi Tobias

I can understand your frustration, programming with databases is not so
simple in the beginning and there are some new concepts to get your head
around.  I think the Result object is quite often misunderstood and
people often ask:

"Why can't the SQL stuff return something simple I can understand, like
an Array (or a Collection if you've gone a little further in Gambas), in
place of this complicated Result object?"

Before answering these questions, it's probably a good idea to start
with an analogy, and hopefully by the time I'm done, you won't need the
answer you thought you needed.

Let's imagine you've placed an ad for a job in the local paper and a few
people turn up for the interview.  No problem, you just ask everyone
into your little interview room, you go through the candidates and
choose one.

Now let's imagine you've placed an ad for a job as a lady's underwear
salesman.  Suddenly you have thousands of applicants and they won't fit
into your little interview room (hint: memory!).  So you place your
thousands of applicants in the local sports hall (hint: database!) and
employ an assistant whose job it is to go and fetch the Next candidate
from the sports hall and bring them to your little interview room.  You
process each applicant in your little interview room and leave it to
your assistant (hint: Result!) to keep track of who you've already seen,
who's Next on the list, and to let you know when you've interviewed all
the candidates.

I hope it's clear from this somewhat imperfect analogy that a Result
object doesn't hold all the records you've selected in memory and only
provides a mechanism for looking at each record matching your criteria
in turn.

Databases are designed to hold huge numbers of records (think of the
government, a car manufacturer, a utility company...) often running into
the millions of records.  If you got into the habit of reading all the
records you've selected into memory (or even if the Result object worked
that way behind the scenes...), you'd soon find everything breaking with
Out Of Memory errors as soon as you start doing anything serious.

Now we've cleared all that up, let's look at just how simple the Result
object can be (we have a candidates table, with columns like name,
canReadAndWrite, address, date of birth etc.):

Once you've established a Connection to your database, let's say in
myConn...

Dim sql as String
Dim name as String
Dim canReadAndWrite as String
Dim candidateList as Result
sql = "select name, canReadAndWrite from candidates"
candidateList = myConn.Exec(sql)
' This is the important line... see how simple it can be to navigate
round a Result object
FOR EACH candidateList
	name = candidateList["name"]
	canReadAndWrite = candidateList["canReadAndWrite"]
	IF Ucase$(canReadAndWrite) = "YES" THEN
		Print "Candidate " & name & " selected!"
	ELSE
		Print "Candidate " & name & " NOT selected!"
	END IF
NEXT

Ignoring all the obvious flaws in my example ("Why didn't you just add a
WHERE clause to preselect only the candidates who can read and write?",
"Why do you have a canReadAndWrite column in place of 2 separate canRead
and canWrite columns?", "Why isn't canReadAndWrite a boolean?") it does
hopefully serve to illustrate just how simple dealing with Result
objects can be:

1. You write your SQL statement String
2. You assign your Result object to the return from myConn.Exec(String)
3. You process each record inside a FOR EACH... NEXT on your Result

Hope this helps a little
Regards,
Caveat