[Gambas-user] Problems with DB access

[neil lewis]

Hello everyone,

I've just started experimenting with using Gambas to access a MySQL 
database, so please excuse me if this has already been covered. I've not 
been able to find any mention of it in the lists.

As a first experiment, I built a form which connects to a remote 
database on my lan. I have no problems querying the database and 
displaying the results, so long as I use a query in the form

        roResult = hConnection.Exec(query)

However, this only gives me read only access. For read/write access, I 
have trying to use a query in the form

         rwResult = hConnection.Edit(table, query)

But so far, with no success.

The help info only shows an example which it says doesn't work and asks 
for a working example to be submitted. This I will be happy to do, as 
soon as I have one!

The relevant portion of my code is below. I'd very much appreciate some 
suggestions on how to resolve this.

Thanks in anticipation!

Neil Lewis.

---------------------------------------------------------------------------------------------------------------------
'Global variable decalarations

server AS String                      'server URL
database AS String                    'name of database on server
client_table AS String                'name of clients table
order_table AS String                 'name of orders table
hConnection AS Connection             'connection handle for database
roResult AS Result                     'result array of read-only query

PUBLIC SUB _new()
 
  hConnection = NEW Connection
  server = "fileserver"
  database = "gibsons"
  client_table = "clients"
  order_table = "orders"
 
  WITH hConnection
  .Type = "mysql"
  .Host = server
  .Login = ""
  .Password = ""
  .Name = database
  END WITH

 
  hConnection.Open

  CATCH
    Message.Error(Error.Text)
 
END

PUBLIC SUB ButtonQuery_Click()

  DIM query AS String

  IF TextBoxSearch.Text = "" THEN RETURN
 
  query = "select id, firstname, lastname, building, street, area, city, 
postcode from " & client_table & " where lastname = \"" & 
TextBoxSearch.Text & "\""
  roResult = hConnection.Exec(query)
 
  IF roResult.Available = FALSE THEN
    Message("No matching records found")
    RETURN
  ENDIF
 
  ShowRecord
   
END

PUBLIC SUB ShowRecord()
 
  DIM new_address AS String
 
  LCDNumberIndex.Value = roResult.index + 1
  LCDNumberCount.Value = roResult.Count
  TextBoxFirstName.Text = roResult ["firstname"]
  TextBoxLastName.Text = roResult ["lastname"]
  TextAreaAddress.Text = roResult["building"] & "\n" &  
roResult["street"] & "\n" & roResult["area"] & "\n" & roResult["city"]
  TextBoxPostcode.Text = roResult ["postcode"]
 
END

PUBLIC SUB ButtonUpdate_Click()

  DIM rwResult AS Result
  DIM query AS String
  DIM table AS String
  DIM field AS String
   
  table = "clients"
  field = "address"
  query = "select address where id = " & roResult["id"]
     
  hConnection.Begin()
 
    rwResult = hConnection.Edit(table, query)
    IF NOT (rwResult.Available) THEN
      Message.Warning("No result available")
      RETURN
    ENDIF
    rwResult!address = TextAreaAddress.Text
    rwResult.Update
   
  hConnection.Commit()

END

PUBLIC SUB ButtonBack_Click()

  IF NOT (roResult.MovePrevious()) THEN RETURN
 
  ShowRecord

END

PUBLIC SUB Next_Click()

  IF NOT (roResult.MoveNext()) THEN RETURN
 
  ShowRecord

END

PUBLIC SUB ButtonStop_Click()

  ME.Close

END