[Gambas-user] Problem with Gambas and MySql

[Caveat]

MySQL itself doesn't mind about having no primary key:

mysql> desc test_table;
+-----------+-------------+------+-----+---------+-------+
| Field        | Type        | Null  | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| Actual ID | varchar(20) | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+

mysql> select * from test_table where `Actual ID` = 'My actual ID';
+--------------+
| Actual ID    |
+--------------+
| My actual ID |
| My actual ID |
| My actual ID |
| My actual ID |
| My actual ID |
| My actual ID |
+--------------+
6 rows in set (0.00 sec)

mysql> delete from test_table where `Actual ID` = 'My actual ID' limit 2;
Query OK, 2 rows affected (0.13 sec)

mysql> select * from test_table where `Actual ID` = 'My actual ID';
+--------------+
| Actual ID    |
+--------------+
| My actual ID |
| My actual ID |
| My actual ID |
| My actual ID |
+--------------+
4 rows in set (0.00 sec)

If the records are *identical*, it *can't* matter which ones you 
actually delete!

Even in Gambas, I don't run into any problems specifically to do with 
having no Primary Key:

   Try myDB.Open
   If Error Then
     Message("Cannot Open database:" & Error.Text)
     Return False
   End If
   myDB.Exec("delete from test_table")
   For n = 1 To 6
     myDB.Exec("insert into test_table values ('My actual ID')")
   Next
   res = myDB.Exec("select * from test_table")
   counter = 0
   res.MoveFirst
   While res.Available
     counter += 1
     Print Str(counter) & ": " & res["Actual ID"]
     res.MoveNext
   Wend
   myDB.Exec("delete from test_table where `Actual ID` = 'My actual ID' 
limit 2")
   res = myDB.Exec("select * from test_table")
   counter = 0
   res.MoveFirst
   While res.Available
     counter += 1
     Print Str(counter) & ": " & res["Actual ID"]
     res.MoveNext
   Wend

The above code works perfectly, giving the expected results:

1: My actual ID
2: My actual ID
3: My actual ID
4: My actual ID
5: My actual ID
6: My actual ID
1: My actual ID
2: My actual ID
3: My actual ID
4: My actual ID

Willy, can you explain again EXACTLY what your problem is?  What error 
do you get, where, and when... ?

Kind regards,
Caveat



On 05/10/13 14:08, Willy Raets wrote:
> On Sat, 2013-10-05 at 10:08 +0200, Fernando Martins wrote:
>> On 10/05/2013 01:25 AM, Willy Raets wrote:
>>> On Fri, 2013-10-04 at 22:53 +0200, Willy Raets wrote:
>>>
>>> Nando, Fernando and Caveat,
>>>
>>> Thanks for your responses.
>>>
>>>> Content in 'ICzakgewicht' can be like this:
>>>> | ID | Gewicht |
>>>> 345 100
>>>> 345 100
>>>> 345 50
>>>> 347 200
>>>> 347 200
>>>> 347 200
>>>> 347 200
>>> @ Fernando and Caveat
>>>
>>> Above you can see that more than one record can be exactly the same.
>>> At any time only one of them needs to be removed.
>>> With Gambas this can be done, looking for the first record meeting the
>>> criteria, delete it, next update and leave the table.
>>>
>>> With SQL DELETE all records that meet the criteria will be deleted
>>> instead of only one.
>>>
>>> That is why this is not an option.
>>>
>>>
>> "duplicate rows are and always were a mistake in SQL", C.J.Date
>>
>> http://books.google.nl/books?id=y_eVBB5qdwMC&pg=PA159&lpg=PA159&dq=c+j+date+duplicate+records&source=bl&ots=DjN-LDuU2B&sig=5-vlJ8itEkC7h6aFMt2PxHkT-ug&hl=en&sa=X&ei=1cdPUtjSCcHH0QWvooGQCw&redir_esc=y#v=onepage&q=c%20j%20date%20duplicate%20records&f=false
>>
>> In my experience, most often there is a design problem with the database
>> allowing for duplicates. With duplicates, you step out of the set theory
>> behind relational dbs and enter into trouble. I admit I have done it
>> myself, but best be avoided.
>>
> Then adding a primary key should solve that problem.
>