[Gambas-user] Problem with Gambas and MySql

[nando]

For MySQL to operate properly, you MUST have a column which is a primary key.
A "good" table will always have one column set as Primary Key.
If you don't have one, make one as INT(9) AutoIncrement, Not NULL
Name the column 'recid' and use 'recid' to delete a particular row


---------- Original Message -----------
From: Willy Raets <willy at ...2734...>
To: gambas-user at lists.sourceforge.net
Sent: Fri, 04 Oct 2013 22:53:41 +0200
Subject: [Gambas-user] Problem with Gambas and MySql

> 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
------- End of Original Message -------