[Gambas-user] Database Results

[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