[Gambas-user] Bug executing SQLs containing Ampersands ?

[Dani Santos]

This might be resolved cause I'm currently using 1.9.20 but, 'por si las
moscas' (anyway) ...

I'm working against a mysql 4.0.24 database. One of the tables of my
database is created with the following code:

---------------------------------------------------------------------
PRIVATE FUNCTION createTableTemasPreguntas() AS String
  DIM sql AS String


  sql = "CREATE TABLE `preguntas` ("
  sql = sql & "`id` INT UNSIGNED NOT NULL ,"
  sql = sql & "`idApartado` INT UNSIGNED NOT NULL ,"
  sql = sql & "`idUsuario` INT UNSIGNED NOT NULL ,"
  sql = sql & "`fecha` DATE NOT NULL ,"
  sql = sql & "`pregunta` TEXT NOT NULL ,"
  sql = sql & "`observaciones` TEXT NULL ,"
  sql = sql & "`resAleatorias` TINYINT NOT NULL ,"
  sql = sql & "`comprobada` TINYINT NOT NULL ,"
  sql = sql & "`fechaUltimaModificacion` DATE NOT NULL ,"
  sql = sql & "PRIMARY KEY( `id` ) ,"
  sql = sql & "INDEX( `idApartado` )"
  sql = sql & ")TYPE = innodb;"
  RETURN sql
END
---------------------------------------------------------------------



I modify records to this database with the following procedure (I add
records with a similar one):

---------------------------------------------------------------------
PUBLIC SUB Tests_Modificar(IdPregunta AS Long, IdApartado AS Long, Fecha
AS Date, Pregunta AS String, Observaciones AS String,
respuestasAleatorias AS Boolean, comprobada AS Boolean, exportable AS
Boolean)
' 
'   Creado el 03/03/2003 por Daniel. 
'   Modificaciones: 
'       - 16/09/2003    Se añade el parámetro respuestasAleatorias y se
añade en la SQL. 
' 
    DIM strSQL AS String
    DIM intResAleatorias AS Integer
    DIM intComprobada AS Integer
    DIM intExportable AS Integer

    ' Valor que toma esta variable. Únicamente para construir la SQL. 
    intResAleatorias = 0
    IF respuestasAleatorias THEN intResAleatorias = 1

    intComprobada = 0
    IF comprobada THEN intComprobada = 1

    intExportable = 0
    IF exportable THEN intExportable = 1

    strSQL = " UPDATE preguntas SET "
    strSQL = strSQL & " idApartado = " & IdApartado & ", "
    strSQL = strSQL & " fecha = '" & Format(Fecha, "yyyy/mm/dd") & "', "
    strSQL = strSQL & " pregunta = '" & Trim(Pregunta) & "', "
    strSQL = strSQL & " observaciones = '" & Trim(Observaciones) & "', "
    strSQL = strSQL & " resAleatorias = " & intResAleatorias & ", "
    strSQL = strSQL & " comprobada = " & intComprobada & ", "
    strSQL = strSQL & " exportable = " & intExportable & ", "
    strSQL = strSQL & " fechaUltimaModificacion = CURDATE() "
    strSQL = strSQL & "WHERE   id = " & Str(idPregunta)

    m_db.Exec(strSQL)

END SUB
---------------------------------------------------------------------

I've found out that if the 'Pregunta' variable contains an ampersand
(&), I must add an ampersand at the beginning of the variable or it will
not appear at the database when the SQL is executed. This is not
neccesary with the 'Observaciones' variable.

On the other hand, if 'Pregunta' variable DOES NOT contains and
ampersand (&) and the 'Observaciones' variable DO contains it, the
ampersand at the 'Observaciones' variable will not appear at the
database when the SQL is executed.

This happens either adding or updating records.

I've provissionaly solved it with the following code before creating the
SQL statements:

---------------------------------------------------------------------
    ' Posible bug de Gambas? Quitar cdo se resuelva:
    IF InStr(Pregunta, "&") <> 0 OR InStr(Observaciones, "&") <> 0 THEN
      Pregunta = "&" & Pregunta
    ENDIF
---------------------------------------------------------------------



Regards.

-- 
Dani Santos <jdsantos1978 at ...626...>