Restricted data type attribute violation
Arturo Henry Torres Zenteno
When we try to access an MS Access database and query as follows:
set sql = “select nhc, fnacimiento, apellido1, apellido2, nombre,
telefono, sexo from pacientes where apellido1 = ?"
set parametro = pRequest.apellido1
#Dim rs as EnsLib.SQL.GatewayResultSet
set tSC = ..Adapter.ExecuteQuery(.rs,sql,parametro)
and shows the following error:
____†___SetParameter________"___†___DescribeParameters________“___<Ens>ErrGeneralx_
SQLState: (IM001) NativeError: [0] Message: [Microsoft][ODBC Driver
Manager] Driver does not support this function ________
___<Ens>ErrGeneral…_ SQLState: (07006) NativeError: [19] Message:
[Microsoft][ODBC Microsoft Access Driver]Restricted data type
attribute violation ________
but if we omit the "?" as follows:
set sql = “select nhc, fnacimiento, apellido1, apellido2, nombre,
telefono, sexo from pacientes where nhc = "_ pRequest.apellido1
#Dim rs as EnsLib.SQL.GatewayResultSet
set tSC = ..Adapter.ExecuteQuery(.rs,sql)
It works correctly.
Why does it fail with the first way?. This is very peculiar, because
in other computers both methods work correctly and with the same
characteristics of machine. My OS is MS Windows Vista and driver ODBC
v6.
Any ideas?
Regards,
Arturo.
Dale du Preez
This is partly due to the way we have implemented the SQL gateway code
and partly due to the MS Access ODBC driver.
The difference between your two queries is that the second call includes
your parameter as a static value in the SQL statement, and the first
query has the parameter external to the query. From an implementation
perspective, the two are different, because we can simply pass the SQL
query to the foreign database in the case that works. In the second
case, we need to use the ODBC framework to send the value of the
parameter separately from the main SQL statement. (I am glossing over
some details -- please let me know if you'd like more!) For our SQL
adapters, when such a binding takes place, our default behaviour is to
call a function in the ODBC driver to describe the parameters/columns
that we need to send to the database so that we can correctly send the
data in the correct format using the correct type. The MS Access drivers
don't implement this function, so you get the error you reported below.
The SQL adapters allow you to bypass this call by passing in an array to
indicate what ODBC types the parameters should have. The exact structure
of the array depends on your version of Ensemble, so I will have to
refer you to our documentation for 2010.1 at present:
http://docs.intersystems.com/ens20101/csp/docbook/DocBook.UI.Page.cls?KEY=ESQL_adapter_methods_creating#ESQL_parameter_overview
That should help explain some of how to work around this. In general, MS
Access accepts VARCHAR types for most updates as it doesn't provide an
ODBC interface for identifying more complex types.
I hope that helps,
Dale
Arturo Henry Torres Zenteno
--
You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare Community" group.
To post to this group, send email to Ensemble-in...@googlegroups.com
To unsubscribe from this group, send email to Ensemble-in-Healt...@googlegroups.com
For more options, visit this group at http://groups.google.com/group/Ensemble-in-Healthcare?hl=en