[Gambas-user] Calculated fields

[Stephen]

I'm sure there are better answers than this one, however I'll lend a 
hand whenever I can.
If you're using the data controls then someone else will have to lend a 
hand, but here's what I do (it entails no guesswork on the part of the 
programmer).
  Open a database connection and keep it open, assign the select query 
to a string then use the .exec method of the connection to pull the 
resultset back. Iterate through the result set and populate the form.
   The following is a code snippet from a form with 2 buttons on it, 
"ConnectToDB" and "ExecuteQuery" (creative huh?) There's no error 
checking on the database open (this is quick & dirty), exercise error 
checking on the db open and such, but you probably already know that.

' Gambas class file
PUBLIC mDBConnection AS NEW Connection


PUBLIC SUB ConnectDB_Click()
   IF NOT EstablishDatabaseConnection() THEN
       Message ("Unable to open Database","OK")
       ExecuteQuery.Enabled = False
   ELSE
       ExecuteQuery.Enabled = True
   END IF

END


PUBLIC SUB ExecuteQuery_Click()
   DIM SQLString AS String
   DIM RecordSet AS Result

   SQLString = "SELECT Quantity, "&
                                    "Price, " &
                                   "(Quantity * Price) AS SubTotal, " &
                                   "Description " &
                        "FROM Components " &
                        "ORDER BY Quantity, Price, SubTotal;"
   RecordSet = mDBConnection(SQLString)

   If Not IsEmpty(RecordSet) Then
      ' Whatever you do here is up to you, you have the Result, the 
values are there.
      ' Access them like this;
      '    [Object].Text = RecordSet["Quantity"]
      '    [Object].Text = RecordSet["Price"]
      '    [Object].Text = RecordSet["SubTotal"]
      '    [Object].Text = RecordSet["Description"]
   End IF
END

PRIVATE FUNCTION  EstablishDatabaseConnection() AS Boolean
   DIM Success AS Boolean

   Success = False

   WITH mDBConnection
            .Type = "mysql"
            .Host = "192.168.1.55"  ' Insert the IP of your server here
            .User = "dbuser" ' Insert your user here
            .Password = "dbpassword" ' Insert your password here
            .Name = "dbName" ' Insert the name of the db here
            TRY .Open

            IF NOT Error THEN
                Success = TRUE
            END IF

   END WITH

   RETURN (Success)

END

'***********************************************************
' I never code multiple RETURNs in a function, or exit points
' in a SUB, there can be only one.
************************************************************
PUBLIC FUNCTION IsEmpty(pRecordSet AS Result) AS Boolean
   DIM Empty AS Boolean
   Empty = False ' I'm cynical. In this case False means the RecordSet 
is NOT empty (a good thing)

   ' This could also be written
   ' IF Not IsNull(pRecordSet) AND pRecordSet.Count = 0 THEN Empty = True
   IF Not IsNull(pRecordset) THEN
       IF pRecordset.Count = 0 then
           Empty = TRUE
       END IF
   END IF

   RETURN (Empty)

END

On 05/25/2015 03:57 AM, William Chapman wrote:
> Hi Everyone!
>
> My first post to the forum and my first project!
>
> Being new to Gambas, I have got stuck on trying to use Gambas to create a
> front-end for a MySQL database created with MySQL Workbench. I have trawled
> the Internet without much success for an answer.
>
> The application that I am working on requires calculated fields. Using
> MySQL
> workbench the calculated field has been produced by using a MySQL query.
>
> For example:
>
> select quantity, price, (quantity * price) as subtotal
> from components
> order by quantity, price, subtotal desc
>
> Following a Youtube video showing how to "import" data held in a MySQL
> database into a Gambas front-end, the form is constructed using the
> DataSource and the DataBrowser options from the Toolbox.
>
> While the fields of "price" and "quantity" are shown, being obtained from
> the
> table "components", I have not been able to figure out how to source the
> calculated field i.e. quantity * price and its value (as obtained via the
> SQL
> query) in order to place it into the Gambas front end.
>
> e.g:-
>
> amount              price            subtotal
> 10                        50                   500
>
> Any possible suggestions or reference to an available coding example would
> be
> greatly appreciated.
>
> Regards
> William
> Cape Town
> South Africa
> ------------------------------------------------------------------------------
> One dashboard for servers and applications across Physical-Virtual-Cloud
> Widest out-of-the-box monitoring support with 50+ applications
> Performance metrics, stats and reports that give you Actionable Insights
> Deep dive visibility with transaction tracing using APM Insight.
> http://ad.doubleclick.net/ddm/clk/290420510;117567292;y
> _______________________________________________
> Gambas-user mailing list
> Gambas-user at lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/gambas-user
>


-- 
Kindest Regards
Stephen A. Bungay, Prop.
Smarts On Site Information Systems