Re: revisited ODBC Error 37000 on prepared statements

To solve the described problem in a clean way, I modified the following in
DBParametrizedStatement:

Added Instance var "paramInputOutputTypeMap".

Added instance methods:
----------------------------------------------------------------------------
--------------------------------------------
paramInputOutputTypeAt: anInteger default: defaultTypeInteger
paramInputOutputTypeMap isNil ifTrue: [^defaultTypeInteger].
^(paramInputOutputTypeMap at: anInteger)
ifNil: [defaultTypeInteger]
ifNotNil: [:type | type]
----------------------------------------------------------------------------
--------------------------------------------
paramInputOutputTypeAt: colInteger put: typeInteger
paramInputOutputTypeMap ifNil: [paramInputOutputTypeMap :=
IdentityDictionary new].
paramInputOutputTypeMap at: colInteger put: typeInteger
----------------------------------------------------------------------------
--------------------------------------------
paramInputOutputTypeMap
^paramInputOutputTypeMap
----------------------------------------------------------------------------
--------------------------------------------
paramInputOutputTypeMap: aDictionary
paramInputOutputTypeMap := aDictionary
----------------------------------------------------------------------------
--------------------------------------------

Changed setParams instance method, row 16 to:
...
inputOutputType: (self paramInputOutputTypeAt: each default:
SQL_PARAM_INPUT)
...

That's it.

Note that the default type is now SQL_PARAM_INPUT instead of
SQL_PARAM_INPUT_OUTPUT as this allows me to run >90 percent of my stored
procedures and queries without setting any types at all.

Only in the rare case of a real output parameter in the SP I need to add
e.g. :
myPreparedStatement paramInputOutputTypeAt: 4 put: SQL_PARAM_OUTPUT.

I would be happy if this would be added to the base image some day as the
current standard implementation of prepared statements is unusable with the
SQLServer 2000 ODBC driver (and likely with others as well).

Ciao

...Jochen

Jochen,

Thanks for the report and the suggested fix. This has been recorded as
defect #1260 "Prepared statements give Error 3700 using SQL Server 2000" and
your fix will appear in the next patch level,

Best Regards,

Andy Bower
Dolphin Support
http://www.object-arts.com
---
Are you trying too hard?
http://www.object-arts.com/Relax.htm
---

"Jochen Riekhof" <[hidden email]> wrote in message
news:[hidden email]...
> To solve the described problem in a clean way, I modified the following in
> DBParametrizedStatement:
>
> Added Instance var "paramInputOutputTypeMap".
>
> Added instance methods:
> --------------------------------------------------------------------------
--
> --------------------------------------------
> paramInputOutputTypeAt: anInteger default: defaultTypeInteger
> paramInputOutputTypeMap isNil ifTrue: [^defaultTypeInteger].
> ^(paramInputOutputTypeMap at: anInteger)
> ifNil: [defaultTypeInteger]
> ifNotNil: [:type | type]
> --------------------------------------------------------------------------
--
> --------------------------------------------
> paramInputOutputTypeAt: colInteger put: typeInteger
> paramInputOutputTypeMap ifNil: [paramInputOutputTypeMap :=
> IdentityDictionary new].
> paramInputOutputTypeMap at: colInteger put: typeInteger
> --------------------------------------------------------------------------
--
> --------------------------------------------
> paramInputOutputTypeMap
> ^paramInputOutputTypeMap
> --------------------------------------------------------------------------
--
> --------------------------------------------
> paramInputOutputTypeMap: aDictionary
> paramInputOutputTypeMap := aDictionary
> --------------------------------------------------------------------------
-- the

> Thanks for the report and the suggested fix. This has been recorded as
> defect #1260 "Prepared statements give Error 3700 using SQL Server 2000"
and
> your fix will appear in the next patch level,

Cool!!!!

I hope that setting default io type to INPUT does not break any old code.
Anyway, since the bug did not become apparent so far it seems to me that
many ODBC drivers don't care much about this parameter anyway :-).

Ciao

...Jochen

Just remembered one other issue with DBConnection - while the methods
"tables" and "procedures" are present, it lacks a "views" method. Here is my
implementation:

views
"Answer a collection of all user views on the database"
^self
tables: nil
owners: nil
tables: nil
types: '''VIEW'''


Would be great to have this one added in the base image, too.

Ciao

...Jochen