[Gambas-user] Mysql query question

[Ron]

Ron schreef:
> Benoit Minisini wrote:
>> On mardi 4 novembre 2008, Ron wrote:
>>  
>>> Hi,
>>>
>>> I'm replacing my buggy mysql query's for correct ones...
>>>
>>> All is well except for this one...
>>>
>>> DIM sTable as String = "remarks_tags"
>>>
>>> Main.hDB.Exec("SELECT text FROM " & sTable & " ORDER BY rand() LIMIT 
>>> 1")
>>> This works ok...
>>>
>>> Replaced by...
>>>
>>> Main.hDB.Exec("SELECT text FROM &1 ORDER BY rand() LIMIT 1", sTable)
>>>
>>> Which results in an SQL query syntax error, is this correct behavior?
>>>
>>> Any ideas?
>>>
>>> Regards,
>>> Ron_2nd
>>>
>>>     
>>
>> &1, &2... are there for quoting SQL values, not SQL table or field 
>> names.
>> Moreover, if you want to insert a table or field name in a SQL 
>> request, you should use the DB.Quote() function:
>>
>> Main.hDB.Exec("SELECT text FROM " & DB.Quote(sTable) & " ORDER BY 
>> rand() LIMIT 1")
>>
>>   
>
> Ok got it, works ok now.
>
> Thanks.
>

Does gambas provide an equivalent for the sql code below?

sSql = "CREATE TABLE capture_camera" & iCam & " ("
sSql &= "`id` int(11) NOT NULL auto_increment,"
sSql &= "`stamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update 
CURRENT_TIMESTAMP,"
sSql &= "`image` longblob,"
sSql &= "PRIMARY KEY( `id`),"
sSql &= "KEY `timestamp` (`stamp`)"
sSql &= ");"
Main.hDB.Exec(sSql)

This is what I have so far, but the replacement of the KEY and 
CURRENT_TIMESTAMP on update functionality is unclear to me.
It seems it's not here, and I have to keep using the method above.

DIM tableCaptures as Table

tableCaptures = Main.hDB.Tables.Add("capture_camera" & iCam)
tableCaptures.Fields.Add("id", db.Serial)
tableCaptures.Fields.Add("stamp", db.date, 32)
tableCaptures.Fields.Add("image", db.blob)
tableCaptures.PrimaryKey = ["id"]
tableCaptures.Update()

I'm using the mysql driver.

Regards,
Ron_2nd