[Gambas-user] Problem with Gambas and MySql

[Willy Raets]

On Sat, 2013-10-05 at 23:11 +0200, Caveat wrote:
> 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)
> 

Yes, this (the limit option) is what I was missing. All I need to be
able is delete 1 of the several identical records.
So I only need to execute a SQL SELECT and use Limit 1.

> 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... ?

With your solution, there is no more problem. This is exactly what I
needed. Didn't use the SELECT because it removed all the record that
where identical, but wasn't aware of the fact you could use limit to
limit the number of records to remove.

So I tried it with opening a SELECT for the proper records for edit in
Gambas and then using .MoveNext until first record that meets criteria
and delete it, to next update and leave.
That is where the primary key problem arose.
But the 'SELECT ... limit 1' will do the job.

Thanks,

-- 
Kind regards,

Willy (aka gbWilly)

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