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.
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.
[code]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)[/code]
It is a web project. The database connection is ok because other queries worked but this long one throws the NilObjectException
If you are getting the NOE in that second line, your Session or DB variable is nil.
unidad = rs.Field("composicion_pa.unidad_dosis_pa").StringValue
here I get the NOE
Oh. Try this:
unidad = rs.Field("unidad_dosis_pa").StringValue
Table names are not included in the column names.
Same.
The database is PostgreSQL
Navicat uses a notation like
prescription.“prescription_Id” = formasfarmaceuticas.“prescription_Id”
but those quotations get syntax error in Xojo
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.
That worked. Thanks both Kem and Maurizio
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.