[Gambas-user] Problem with Gambas and MySql

[Willy Raets]

Hi All,

In a bigger project where creating, editing and deleting records works
as a charm, only one table is giving me trouble and just won't delete a
record with reason 'No primary key'.

Now the table in question 'ICzakgewichten' was migrated from an Access
database to MySql and in Access it was no problem to delete a record
from a Windows VB client.

I'm in the process, next to the existing Windows VB-clients, writing
Linux Gambas-clients. Hence the move of data from Access to MySql. The
VB-clients get connected to the tables in MySql and will co-exist with
the Gambas written clients for a while, until migration complete on all
client PCs. 

The table has a one to many releation (being at the many side) with a
'mother' table 'IC' that holds (amongst others) following relevant
fields:
| ID | .... | NumItems | ....
ID is a primary key

The table ICzakgewichten looks like this:
| ID | Gewicht |

Note: gewicht (Dutch) means weight.

Content in 'ICzakgewicht' can be like this:
| ID | Gewicht |
345 100
345 100
345 50
347 200
347 200
347 200
347 200

In IC the records would look like:
| ID | .... | NumItems | ....
345 ..... 3
347 ..... 4
NumItems holds the number of corresponding records in ICzakgewicht.

Now along the road the NumItems decrements until it reaches 0.
Corresponding records in ICzakgewicht need to be deleted as well.

And this is where stuff won't work, as I am not allowed to delete the
records because of no primary key in tabe 'ICzakgewicht'.

I could add a primary key, but that would mean adapting the VB-clients
to the new table format and I don't really want to go there as they are
on their way out.

I have created a small example project and exported 'ICzakgewichten'
from MySql.

To make example work you need:
1. A working MySql server
2. A empty database named 'test'
3. A user account and password to the database

In database test import the added 'ICzakgewichten.sql'
It was exported from MySql Server version: 5.5.31

In the Gambas added example, change user and password (and all else that
might be needed) to meet your needs.

You will find the code in following procedure:

Public Procedure Connect()
   
   MyConn.Close()
   MyConn.Type = "MySQL"
   MyConn.Host = "127.0.0.1"
   MyConn.Login = "willy"    '<- change to your situation
   MyConn.Port = "3306"
   MyConn.Name = "test"
   MyConn.Password = "9876"  '<- change to your situation
   MyConn.Open()
   If Error Then
      Message("Can't open database: " & Error.Text)
      Return
   Endif
   
End

Next run the application, select an ID in the combobox and add a
corresponding weight (in example no check if weight exist in table, in
real project there is, so pick one that exists!).
Next click button 'Delete' to get the error.

My question, how to prevent this error from happening, preferably
without having to add a primary key to the table?


-- 
Kind regards,

Willy (aka gbWilly)

http://gambasshowcase.org/
http://howtogambas.org
http://gambos.org



-------------- next part --------------
A non-text attachment was scrubbed...
Name: ICzakgewichten.sql
Type: text/x-sql
Size: 3285 bytes
Desc: not available
URL: <http://lists.gambas-basic.org/pipermail/user/attachments/20131004/104db067/attachment.bin>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: DatabaseEditProblem-0.0.1.tar.gz
Type: application/x-compressed-tar
Size: 6473 bytes
Desc: not available
URL: <http://lists.gambas-basic.org/pipermail/user/attachments/20131004/104db067/attachment-0001.bin>