[Gambas-user] Database Results

[tobias]

hi,

> The biggest help for him will be to read a sql book :)... it was for
> me the only way to quit the M$ ADO in the past. But gb.db and result
> is suffisely powerfull to manage most part of the db capabilities in a
> first time . Then he can learn sql do to more.
> 
> 
> All depend of the project ... for a little project with 10  to 10000
> entry ... sqlite is good enouth. For more take a look to mysql or
> postgresql ... i'm agree with richard , postgre is powerfull ... (Both
> are good enouth for non professionnal in fact)
> 
> 2010/9/12 Caveat <Gambas at ...1950...>:
>> 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
>>
>>
>> ------------------------------------------------------------------------------
>> Start uncovering the many advantages of virtual appliances
>> and start using them to simplify application deployment and
>> accelerate your shift to cloud computing
>> http://p.sf.net/sfu/novell-sfdev2dev
>> _______________________________________________
>> Gambas-user mailing list
>> Gambas-user at lists.sourceforge.net
>> https://lists.sourceforge.net/lists/listinfo/gambas-user
>>

wow, why didn't i receive the quoted message from Caveat??
this really helped me out of this problem.

@richard: no, postgresql is no possibility because my skilled labour 
should be written according to its topic: gambas + sqlite3 ;)

@benoit: thanks for the class but i think it would be better to use the 
plain gambas for the labour :)
well, looking at Caveat's explanation, an array won't be an improvement 
and i won't need your class, i just wanted to know, why this is the way 
a result is stored.

thanks a lot,
tobi