[Gambas-user] Problem with Gambas and MySql

[Caveat]

Hi Willy

Me again!

Either use LIMIT row_count on your delete statement as per 
http://dev.mysql.com/doc/refman/5.0/en/delete.html

Or use an auto-incrementing primary key that your VB clients don't need 
to worry about as per 
http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html

Kind regards,
Caveat

On 04/10/13 22:53, Willy Raets wrote:
> 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?
>
>
>
>
> ------------------------------------------------------------------------------
> October Webinars: Code for Performance
> Free Intel webinars can help you accelerate application performance.
> Explore tips for MPI, OpenMP, advanced profiling, and more. Get the most from
> the latest Intel processors and coprocessors. See abstracts and register >
> http://pubads.g.doubleclick.net/gampad/clk?id=60134791&iu=/4140/ostg.clktrk
>
>
> _______________________________________________
> Gambas-user mailing list
> Gambas-user at lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/gambas-user