[Gambas-user] Mysql query question

[Benoit Minisini]

On mercredi 5 novembre 2008, Ron wrote:
> 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
>

No, gb.db does not provide an interface to that feature, as it is a mysql 
syntax only.

Regards,

-- 
Benoit Minisini