SQL problem

  1. 2 weeks ago

    Juan C

    Oct 11 Pre-Release Testers, Xojo Pro

    Sorry for this long sql query but I have not been able to figure out what the error could be.
    It works perfectly on Navicat but throws a NilObjectException on Xojo.

    SELECT
    diccionario_dcp.nombredcp,
    diccionario_forma_farmaceutica_simplificadas.formafarmaceuticasimplificada,
    diccionario_vias_administracion.viaadministracion,
    composicion_pa.unidad_volumen_unidad_composicion,
    composicion_pa.orden_colacion,
    composicion_pa.dosis_pa,
    composicion_pa.unidad_dosis_pa,
    composicion_pa.dosis_composicion,
    composicion_pa.unidad_composicion,
    composicion_pa.cantidad_volumen_unidad_composicion,
    composicion_pa.unidad_volumen_unidad_composicion,
    composicion_pa.dosis_administracion,
    composicion_pa.unidad_administracion,
    composicion_pa.dosis_prescripcion,
    composicion_pa.unidad_prescripcion,
    composicion_pa.cantidad_volumen_unidad_administracion,
    composicion_pa.unidad_volumen_unidad_administracion
    FROM
    prescription,
    diccionario_dcp,
    formasfarmaceuticas,
    diccionario_forma_farmaceutica_simplificadas,
    diccionario_vias_administracion,
    viasadministracion,
    composicion_pa
    WHERE
    prescription.cod_dcp = diccionario_dcp.codigodcp
    AND prescription."prescription_Id" = formasfarmaceuticas."prescription_Id"
    AND composicion_pa."formasfarmaceuticas_Id" = formasfarmaceuticas."formasfarmaceuticas_Id"
    AND diccionario_forma_farmaceutica_simplificadas.codigoformafarmaceuticasimplificada = formasfarmaceuticas.cod_forfar_simplificada
    AND formasfarmaceuticas."formasfarmaceuticas_Id" = viasadministracion."formasfarmaceuticas_Id"
    AND diccionario_vias_administracion.codigoviaadministracion = viasadministracion.cod_via_admin
    AND prescription.cod_dcsa = 90332006
    AND prescription.cod_dcp = 322236009
    LIMIT 1

    I would really appreciate any help with this.

    Put all the text you posted at the beginning of this thread in a string constant i.e. define a constant and copy all the text in the value pane of the inspector.
    Use the constant string as the parameter for the SqlSelect.

  2. Kem T

    Oct 11 Pre-Release Testers, Xojo Pro New York

    Please post the code that runs this query and indicate where the NOE occurs.

    Also, please help us help you by using the "code" tags to format code for readability. It's in the message toolbar right next to the quote icon.

  3. Juan C

    Oct 11 Pre-Release Testers, Xojo Pro
    Dim sql As String
    sql = "SELECT "
    sql = sql + "diccionario_dcp.nombredcp,"
    sql = sql + " diccionario_forma_farmaceutica_simplificadas.formafarmaceuticasimplificada,"
    sql = sql + " diccionario_vias_administracion.viaadministracion,"
    sql = sql + " composicion_pa.unidad_volumen_unidad_composicion,"
    sql = sql + " composicion_pa.orden_colacion,"
    sql = sql + " composicion_pa.dosis_pa,"
    sql = sql + " composicion_pa.unidad_dosis_pa,"
    sql = sql + " composicion_pa.dosis_composicion,"
    sql = sql + " composicion_pa.unidad_composicion,"
    sql = sql + " composicion_pa.cantidad_volumen_unidad_composicion,"
    sql = sql + " composicion_pa.unidad_volumen_unidad_composicion,"
    sql = sql + " composicion_pa.dosis_administracion,"
    sql = sql + " composicion_pa.unidad_administracion,"
    sql = sql + " composicion_pa.dosis_prescripcion,"
    sql = sql + " composicion_pa.unidad_prescripcion,"
    sql = sql + " composicion_pa.cantidad_volumen_unidad_administracion,"
    sql = sql + " composicion_pa.unidad_volumen_unidad_administracion "
    sql = sql + " FROM"
    sql = sql + " prescription,"
    sql = sql + " diccionario_dcp,"
    sql = sql + " formasfarmaceuticas,"
    sql = sql + " diccionario_forma_farmaceutica_simplificadas,"
    sql = sql + " diccionario_vias_administracion,"
    sql = sql + " viasadministracion,"
    sql = sql + " composicion_pa "
    sql = sql + " WHERE"
    sql = sql + " prescription.cod_dcp = diccionario_dcp.codigodcp "
    sql = sql + " AND prescription'prescription_Id = formasfarmaceuticas.prescription_Id "
    sql = sql + " AND composicion_pa.formasfarmaceuticas_Id = formasfarmaceuticas.formasfarmaceuticas_Id "
    sql = sql + " AND diccionario_forma_farmaceutica_simplificadas.codigoformafarmaceuticasimplificada = formasfarmaceuticas.cod_forfar_simplificada "
    sql = sql + " AND formasfarmaceuticas.formasfarmaceuticas_Id = viasadministracion.formasfarmaceuticas_Id "
    sql = sql + " AND diccionario_vias_administracion.codigoviaadministracion = viasadministracion.cod_via_admin "
    sql = sql + " AND prescription.cod_dcsa = 90332006 "
    sql = sql + " AND prescription.cod_dcp = 322236009 "
    sql = sql + " LIMIT 1"
    
    Dim rs As RecordSet
    rs = Session.DB.SQLSelect(sql)

    It is a web project. The database connection is ok because other queries worked but this long one throws the NilObjectException

  4. Kem T

    Oct 11 Pre-Release Testers, Xojo Pro New York

    If you are getting the NOE in that second line, your Session or DB variable is nil.

  5. Juan C

    Oct 11 Pre-Release Testers, Xojo Pro
    unidad = rs.Field("composicion_pa.unidad_dosis_pa").StringValue

    here I get the NOE

  6. Kem T

    Oct 11 Pre-Release Testers, Xojo Pro New York

    Oh. Try this:

    unidad = rs.Field("unidad_dosis_pa").StringValue

    Table names are not included in the column names.

  7. Juan C

    Oct 11 Pre-Release Testers, Xojo Pro

    Same.
    The database is PostgreSQL
    Navicat uses a notation like
    prescription."prescription_Id" = formasfarmaceuticas."prescription_Id"
    but those quotations get syntax error in Xojo

  8. Maurizio R

    Oct 11 Pre-Release Testers, Xojo Pro Answer
    Edited 2 weeks ago by Maurizio R

    Put all the text you posted at the beginning of this thread in a string constant i.e. define a constant and copy all the text in the value pane of the inspector.
    Use the constant string as the parameter for the SqlSelect.

  9. Juan C

    Oct 11 Pre-Release Testers, Xojo Pro

    That worked. Thanks both Kem and Maurizio

  10. Jean-Yves P

    Oct 11 Pre-Release Testers, Xojo Pro Europe (France, Besancon)

    some fields ends with "_Id" and the "I" is uppercase
    you must quote these fields or postgres refuses them.
    in your original query, they are quoted
    in the xojo request, they are not.
    better rename these fields as postgres really hates all mixedcase fields.
    or quote all your requests in xojo
    or put the request in a constant like suggested.

or Sign Up to reply!