[Gambas-user] Database Results

[tobias]

hello mailing list,

i am absolutely new to databases in general and so to databases in
gambas, too. i plan to use sqlite3 and in general i am understanding the
related stuff part by part but it is very confusing with the different
classes and i think this complicates my learning process.
but to my questions now. i want to make a database table just in memory
to get me a bit closer to this all:

hConnection = NEW Connection
hConnection.Type = "sqlite3"
hConnection.Open()
hTable = hConnection.Tables.Add("test")
hTable.Fields.Add("id", db.Serial)
hTable.Fields.Add("name", db.String)
hTable.PrimaryKey = ["id"]
hTable.Update()
hResult = hConnection.Create("test")

and in the following i want to add three names to the database and then
hResult.Update() them, but how do i create more records than one? (i
used hResult["name"] = "tobi" to create the first, this works)

the next question.
i got a result from a database with some records predefined (fields are
id (serial) and name (string)):

hResult = hConnection.Exec("select * from test")

in a for loop i go through all the records by doing:

FOR iCount = 0 TO hResult.Count - 1 STEP 1
hResult.MoveTo(iCount)
FOR EACH hField IN hResult.Fields
TextArea1.Insert(hResult[hField.Name])
NEXT
TextArea1.Insert("\n")
NEXT

which works also fine but i really don't like the hResult.MoveTo() part
of this code, isn't there any way to handle this result simply as an
array? this would solve both problems and i could do database stuff with
ease?

regards, tobi

[Fabien Bodard]

like that ?

For each hresult

print hResult!MyField

next

2010/9/11 tobias <***@web.de>:
> hello mailing list,
>
> i am absolutely new to databases in general and so to databases in
> gambas, too. i plan to use sqlite3 and in general i am understanding the
> related stuff part by part but it is very confusing with the different
> classes and i think this complicates my learning process.
> but to my questions now. i want to make a database table just in memory
> to get me a bit closer to this all:
>
> hConnection = NEW Connection
> hConnection.Type = "sqlite3"
> hConnection.Open()
> hTable = hConnection.Tables.Add("test")
> hTable.Fields.Add("id", db.Serial)
> hTable.Fields.Add("name", db.String)
> hTable.PrimaryKey = ["id"]
> hTable.Update()
> hResult = hConnection.Create("test")
>
> and in the following i want to add three names to the database and then
> hResult.Update() them, but how do i create more records than one? (i
> used hResult["name"] = "tobi" to create the first, this works)
>
> the next question.
> i got a result from a database with some records predefined (fields are
> id (serial) and name (string)):
>
> hResult = hConnection.Exec("select * from test")
>
> in a for loop i go through all the records by doing:
>
> FOR iCount = 0 TO hResult.Count - 1 STEP 1
>  hResult.MoveTo(iCount)
>  FOR EACH hField IN hResult.Fields
>    TextArea1.Insert(hResult[hField.Name])
>  NEXT
>  TextArea1.Insert("\n")
>  NEXT
>
> which works also fine but i really don't like the hResult.MoveTo() part
> of this code, isn't there any way to handle this result simply as an
> array? this would solve both problems and i could do database stuff with
> ease?
>
> regards, tobi
>
> ------------------------------------------------------------------------------
> 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-***@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/gambas-user
>



--
Fabien Bodard

[Tobias Boege]

hi,

>like that ?
>
>For each hresult
>
> print hResult!MyField
>
>next
>

hmm, this works fine and looks like a pretty good solution! but there's no way to handle a result the same way like an array? this FOR EACH construct is especially for the result records? i need to understand this stuff for a skilled labour completely, for this reason understanding the structure of the Result-Object is neccessary. it seems to be the most complicated thing in gambas :)

and can you tell me something about my first problem? this also shouldn't be so defficult, i think?

regards, tobi
___________________________________________________________
WEB.DE DSL SOMMER-SPECIAL: Surf & Phone Flat 16.000 für
nur 19,99 &euro;/mtl.!* http://produkte.web.de/go/DSL-Doppel-Flatrate/2

[Ricardo Díaz Martín]

For the first question I recomend to you use SQL sentence for insert. You
can write a function where you past name of database table, name of fields
and values and it returns the SQL INSERT sentence. I think it's all people
do (me too!)


SQL = "INSERT INTO test (id, name) VALUES ('1', 'name01')"
hConnection.Exec(SQL)

Regards,
Ricardo Díaz



2010/9/11 Tobias Boege <***@web.de>

> hi,
>
> >like that ?
> >
> >For each hresult
> >
> > print hResult!MyField
> >
> >next
> >
>
> hmm, this works fine and looks like a pretty good solution! but there's no
> way to handle a result the same way like an array? this FOR EACH construct
> is especially for the result records? i need to understand this stuff for a
> skilled labour completely, for this reason understanding the structure of
> the Result-Object is neccessary. it seems to be the most complicated thing
> in gambas :)
>
> and can you tell me something about my first problem? this also shouldn't
> be so defficult, i think?
>
> regards, tobi
> ___________________________________________________________
> WEB.DE DSL SOMMER-SPECIAL: Surf & Phone Flat 16.000 für
> nur 19,99 &euro;/mtl.!* http://produkte.web.de/go/DSL-Doppel-Flatrate/2
>
>
> ------------------------------------------------------------------------------
> 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-***@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/gambas-user
>

[tobias]

hi,

> For the first question I recomend to you use SQL sentence for insert. You
> can write a function where you past name of database table, name of fields
> and values and it returns the SQL INSERT sentence. I think it's all people
> do (me too!)
>
>
> SQL = "INSERT INTO test (id, name) VALUES ('1', 'name01')"
> hConnection.Exec(SQL)
>
> Regards,
> Ricardo Díaz
>
>
>
> 2010/9/11 Tobias Boege <***@web.de>
>
>
>> hi,
>>
>>
>>> like that ?
>>>
>>> For each hresult
>>>
>>> print hResult!MyField
>>>
>>> next
>>>
>>>
>> hmm, this works fine and looks like a pretty good solution! but there's no
>> way to handle a result the same way like an array? this FOR EACH construct
>> is especially for the result records? i need to understand this stuff for a
>> skilled labour completely, for this reason understanding the structure of
>> the Result-Object is neccessary. it seems to be the most complicated thing
>> in gambas :)
>>
>> and can you tell me something about my first problem? this also shouldn't
>> be so defficult, i think?
>>
>> regards, tobi
>> ___________________________________________________________
>> WEB.DE DSL SOMMER-SPECIAL: Surf & Phone Flat 16.000 für
>> nur 19,99 &euro;/mtl.!* http://produkte.web.de/go/DSL-Doppel-Flatrate/2
>>
>>
>> ------------------------------------------------------------------------------
>> 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-***@lists.sourceforge.net
>> https://lists.sourceforge.net/lists/listinfo/gambas-user
>>
>>
> ------------------------------------------------------------------------------
> 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-***@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/gambas-user
>
okay, i'll do so too ;) in the picture database example there's a separate function, too, in which the table is opened for create and the one record created. this seems more
backend independent, but there's no need of it because i have to write about gamabs + sqlite3.

hmm, i hope anybody could tell me about the Result-Object structure, for the reason that i don't understand it, it would be very interesting in the skilled labour :)

thank you very much,
tobi

[Fabien Bodard]

hResult = hConnection.Create("test")
hResult!Name = "Name01"
hResult = hConnection.Create("test")
hResult!Name = "Name02"
hResult = hConnection.Create("test")
hResult!Name = "Name03"

or

for i=0 to 2
hResult = hConnection.Create("test")
hResult!Name = "Name0" & i
next


with db.serial the id is auto incremented

2010/9/11 tobias <***@web.de>:
> hi,
>
>> For the first question I recomend to you use SQL sentence for insert. You
>> can write a function where you past name of database table, name of fields
>> and values and it returns the SQL INSERT sentence. I think it's all people
>> do (me too!)
>>
>>
>> SQL = "INSERT INTO test (id, name) VALUES ('1', 'name01')"
>> hConnection.Exec(SQL)
>>
>> Regards,
>> Ricardo Díaz
>>
>>
>>
>> 2010/9/11 Tobias Boege <***@web.de>
>>
>>
>>> hi,
>>>
>>>
>>>> like that ?
>>>>
>>>> For each hresult
>>>>
>>>>  print hResult!MyField
>>>>
>>>> next
>>>>
>>>>
>>> hmm, this works fine and looks like a pretty good solution! but there's no
>>> way to handle a result the same way like an array? this FOR EACH construct
>>> is especially for the result records? i need to understand this stuff for a
>>> skilled labour completely, for this reason understanding the structure of
>>> the Result-Object is neccessary. it seems to be the most complicated thing
>>> in gambas :)
>>>
>>> and can you tell me something about my first problem? this also shouldn't
>>> be so defficult, i think?
>>>
>>> regards, tobi
>>> ___________________________________________________________
>>> WEB.DE DSL SOMMER-SPECIAL: Surf & Phone Flat 16.000 für
>>> nur 19,99 &euro;/mtl.!* http://produkte.web.de/go/DSL-Doppel-Flatrate/2
>>>
>>>
>>> ------------------------------------------------------------------------------
>>> 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-***@lists.sourceforge.net
>>> https://lists.sourceforge.net/lists/listinfo/gambas-user
>>>
>>>
>> ------------------------------------------------------------------------------
>> 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-***@lists.sourceforge.net
>> https://lists.sourceforge.net/lists/listinfo/gambas-user
>>
> okay, i'll do so too ;) in the picture database example there's a separate function, too, in which the table is opened for create and the one record created. this seems more
> backend independent, but there's no need of it because i have to write about gamabs + sqlite3.
>
> hmm, i hope anybody could tell me about the Result-Object structure, for the reason that i don't understand it, it would be very interesting in the skilled labour :)
>
> thank you very much,
> tobi
>
>
> ------------------------------------------------------------------------------
> 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-***@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/gambas-user
>



--
Fabien Bodard

[richard terry]

Hi Tobias,

If you are new to databases, you could choose to go to the best open source
database from the beginning - postgresql.

There are a wealth of postgresql books and tutorials around on the web. in
Linux, youve got the pgadmin graphical tool and if you also run windows or a
virtual machine, and intend to do any serious database programming, then a
copy of sqlmanager for windows is worth purchasing. http://www.sqlmanager.net/
which will solve you hours of grief for complex views and functions. The
lastest pgadmin also has a rudimentary sql query builder.

There is also a really helpful mail list:

pgsql-***@postgresql.org


which is monitored by Tom Lane one of the postgresql authors/gurus, even the
simplest questions are answered, or the most complex.

Also, you don't have to use the gambas database gui tools, you can have your
own routines to connect to the database if you desire, simply establish a
connection and use that connection for all your work:

Personally I've found it much easier to create all my tables by hand in
pgadmin or from the command line passing a text file to psql, and just do the
database acessing from gambas to obtain or insert data, put the data into
collections, and manipulate and display as needed in the gui.

I never managed to get the gambas gui database connect to link to my database.

One other hint - maybe not that important, but I always backup the database as
plain and no owner.

Lastly if you've not already got this basic book which, despite its age is not
really out of date, source this from the command line:

wget http://vectorlinux.osuosl.org/Uelsk8s/gambas-beginner-guide.pdf


Hope this is of some general assistance.

Regards

Richard


> hello mailing list,
>
> i am absolutely new to databases in general and so to databases in
> gambas, too. i plan to use sqlite3 and in general i am understanding the
> related stuff part by part but it is very confusing with the different
> classes and i think this complicates my learning process.
> but to my questions now. i want to make a database table just in memory
> to get me a bit closer to this all:
>
> hConnection = NEW Connection
> hConnection.Type = "sqlite3"
> hConnection.Open()
> hTable = hConnection.Tables.Add("test")
> hTable.Fields.Add("id", db.Serial)
> hTable.Fields.Add("name", db.String)
> hTable.PrimaryKey = ["id"]
> hTable.Update()
> hResult = hConnection.Create("test")
>
> and in the following i want to add three names to the database and then
> hResult.Update() them, but how do i create more records than one? (i
> used hResult["name"] = "tobi" to create the first, this works)
>
> the next question.
> i got a result from a database with some records predefined (fields are
> id (serial) and name (string)):
>
> hResult = hConnection.Exec("select * from test")
>
> in a for loop i go through all the records by doing:
>
> FOR iCount = 0 TO hResult.Count - 1 STEP 1
> hResult.MoveTo(iCount)
> FOR EACH hField IN hResult.Fields
> TextArea1.Insert(hResult[hField.Name])
> NEXT

> TextArea1.Insert("\n")
> NEXT
>
> which works also fine but i really don't like the hResult.MoveTo() part
> of this code, isn't there any way to handle this result simply as an
> array? this would solve both problems and i could do database stuff with
> ease?
>
> regards, tobi
>
> ---------------------------------------------------------------------------
> --- 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-***@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/gambas-user
>

[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

[Fabien Bodard]

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 <***@caveat.demon.co.uk>:
> 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-***@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/gambas-user
>



--
Fabien Bodard

[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 <***@caveat.demon.co.uk>:
>> 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-***@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

[Benoît Minisini]

> the next question.
> i got a result from a database with some records predefined (fields are
> id (serial) and name (string)):
>
> hResult = hConnection.Exec("select * from test")
>
> in a for loop i go through all the records by doing:
>
> FOR iCount = 0 TO hResult.Count - 1 STEP 1
> hResult.MoveTo(iCount)
> FOR EACH hField IN hResult.Fields
> TextArea1.Insert(hResult[hField.Name])
> NEXT
> TextArea1.Insert("\n")
> NEXT
>
> which works also fine but i really don't like the hResult.MoveTo() part
> of this code, isn't there any way to handle this result simply as an
> array? this would solve both problems and i could do database stuff with
> ease?
>
> regards, tobi
>

Why just not an array? Ha! Good question...

When I design Gambas, I try to use a property only when the process behind is
usually immediate (like reading a value in memory).

Database drivers are not obliged to return the entire result of a SQL query.
They can return the records one by one, at client request. So accessing the i-
th record of a Result object can take time.

But at the moment only the mysql driver is clever enough for doing that.

Moreover, The other reason I didn't use the array interface is a bad one: I
stupidly copied the MS interface! :-)

And the Result object internally stores its data in a memory array

So I could provide the same interface to the Gambas user, and finally you are
mainly right.

But I don't think I can change the interface anymore, it will break a lot of
code. Unless someone finds an unexpected solution.

For only you, there is a "syntactic sugar" solution.

Create a class named MyResult that way:

' Class MyResult

Private $hResult As Result

Static Public _call(hResult As Result) As MyResult

Dim hMyResult As MyResult = New MyResult(hResult)
Return hMyResult

End

Public Sub _new(hResult As Result)

$hResult = hResult

End

Public Sub _get(Index As Integer) As Result

$hResult.MoveTo(Index)
return $hResult

End

Then use it that way:

Dim hMyResult As MyResult

hMyResult = MyResult(DB.Exec("SELECT * FROM ..."))

Print hMyResult[0][Field]
Print hMyResult[1][Field]
...

Regards,

--
Benoît Minisini

[Rolf Schmidt]

Hi Benoît

> But I don't think I can change the interface anymore, it will break a lot
> of code. Unless someone finds an unexpected solution.

What about a property, which indicate how the result should be delivered. For
example in PHP Database Objects you can give a value that says how the data
should be given back.
Possible types (and some more) are:
ASSOC for an associative array with the fieldnames as key
NUM as a numerical index into the array of fields
CLASS says the the result shoud be given back as a new object of a give class
with members which are named as the fields of the query.

Of course, if non of the above listed property is given, the old behavior of
the gambas database resultset should take place.

May be this gives you an idea.
Fine regards
Rolf

[tobias]

hi,

another question about the databases and gambas to the developers:
how do i have to imagine exactly the interaction between the (sqlite)
driver and my gambas program? what exactly is this 'driver'? is it just
a kind of library? (sorry, i'm not so good at this level and i never had
to do anything with drivers)

regards,
tobi

[Fabien Bodard]

It's a kind of lbrary ... you select gb.db in thecomponents.


then

private hcon as new connection


Publc sub Main()

hCon.type = "sqlite3"
hCon.Host=user.home &/ "dbpath"
hcon.Name = "mydatabase"
hcon.open

end

2010/9/12 tobias <***@web.de>:
> hi,
>
> another question about the databases and gambas to the developers:
> how do i have to imagine exactly the interaction between the (sqlite)
> driver and my gambas program? what exactly is this 'driver'? is it just
> a kind of library? (sorry, i'm not so good at this level and i never had
> to do anything with drivers)
>
> regards,
> tobi
>
> ------------------------------------------------------------------------------
> 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-***@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/gambas-user
>



--
Fabien Bodard

[tobias]

hi,

> It's a kind of lbrary ... you select gb.db in thecomponents.
>
>
> then
>
> private hcon as new connection
>
>
> Publc sub Main()
>
> hCon.type = "sqlite3"
> hCon.Host=user.home &/ "dbpath"
> hcon.Name = "mydatabase"
> hcon.open
>
> end
>

yeah, so this 'kind of library' it loaded by the gambas component (?)
loader and there are the several sqlite3 functionalities for my program?

regards,
tobi

[Fabien Bodard]

2010/9/12 tobias <***@web.de>:
> hi,
>
>> It's a kind of lbrary ... you select gb.db in thecomponents.
>>
>>
>> then
>>
>> private hcon as new connection
>>
>>
>> Publc sub Main()
>>
>>   hCon.type = "sqlite3"
>>   hCon.Host=user.home &/ "dbpath"
>>   hcon.Name = "mydatabase"
>>   hcon.open
>>
>> end
>>
>
> yeah, so this 'kind of library' it loaded by the gambas component (?)
> loader and there are the several sqlite3 functionalities for my program?
it"s a layer that use sql queries in background ... The same
functionalities are usable with all database type managed by gb.db
(mysql, postgresql, sqlite,odbc, firebird)
>
> regards,
> tobi
>
> ------------------------------------------------------------------------------
> 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-***@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/gambas-user
>



--
Fabien Bodard

[Caveat]

Hi Tobias

Fabien is correct in saying it's a kind of library.

For Gambas2 in my Ubuntu installation, the following libraries are
automatically installed along with Gambas2:

gambas2-gb-db
gambas2-gb-db-postgresql
gambas2-gb-db-firebird
gambas2-gb-db-mysql
gambas2-gb-db-form
gambas2-gb-db-odbc
gambas2-gb-db-sqlite

gb-db is the common library for database access
gb-db-postgresql, ...firebird, ...mysql, ...odbc, ...sqlite libraries
are the specific 'drivers'
gb-db-form handles Gambas database-bound controls

The driver normally handles the specifics of talking to the particular
database so that your code all looks the same, regardless of which
database you're talking to. So whether it's for mysql, sqlite,
postgre...you just code:

Dim myConnection as NEW Connection
...
myConnection.Open
...
myConnection.Exec("select * from candidates")

The properties you set on the Connection, and specifically the Type
determine which database in particular you will be talking to.

My connect function looks something like:

PUBLIC FUNCTION connect2db() AS Boolean
WITH myConnection
.Type = "mysql"
.Host = "192.168.2.188"
.Port = 3306
.Login = "dbuser"
.Password = "db25$4klln"
.Name = "recruiting"
END WITH
TRY myConnection.Open
IF ERROR THEN
Message("Cannot Open recruiting database:" & Error.Text)
RETURN FALSE
END IF
RETURN TRUE
END

Under the covers, the specific driver for mysql may convert the
myConnection.Open into (completely made-up fictitious nonsense code
follows):

MySQLConnectionObject mysqlConn = CALL MySQL.openDatabase(Login:
{name:password}, Host: {host}, Port: {port})

and for postgresql it may look nothing at all like:

postgresql.connection = CALL postgresql.openDB(ConnectURL:
{name:***@host:port})

The only thing you need to be aware of (and only if you plan on making
your code portable from one db to another...actually not a bad idea) is
the small differences in SQL syntax that the different databases support
and the different ways in which different databases may interpret your
SQL.

For example for HSQLDB (this is a beautiful thing, written in java,
6.5Mb download for a complete db with graphical client!) you might say:

select concat(count(distinct name), '->B5') as ForCell from candidates
where upper(canReadAndWrite) = 'YES'

but for SQL Server (sorry, I'm forced to use 'doze for my work!) you
have to say:

select str(count (distinct name)) + '->B5' as ForCell from candidates
where upper(canReadAndWrite) = 'YES'

I've also been surprised by the same query returning different results
in different databases. ISTR something like SQL Server being
case-insensitive by default, and HSQLDB not... so running the same
"select count(distinct name) from candidates" query resulted in
different counts!

Hope this helps, and that you get this this time around (I put you on cc
so maybe you get 2 copies for the price of one) :-)

Regards,
Caveat


On Sun, 2010-09-12 at 21:33 +0200, tobias wrote:
> hi,
>
> > It's a kind of lbrary ... you select gb.db in thecomponents.
> >
> >
> > then
> >
> > private hcon as new connection
> >
> >
> > Publc sub Main()
> >
> > hCon.type = "sqlite3"
> > hCon.Host=user.home &/ "dbpath"
> > hcon.Name = "mydatabase"
> > hcon.open
> >
> > end
> >
>
> yeah, so this 'kind of library' it loaded by the gambas component (?)
> loader and there are the several sqlite3 functionalities for my program?
>
> regards,
> tobi
>
> ------------------------------------------------------------------------------
> 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-***@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/gambas-user