After upgrading to TimesTen 11.2.1, we've notice that some of our current sql statements are failing. These same instructions executes from the applications on TT6.0. This is once of the selects that is failing.
select NodeId , Idx from Santera.eswitchInterface where ( ( NodeId >= :NodeId ) and ( ( NodeId > :NodeId ) or ( Idx > :Idx ) ) ) order by NodeId , Idx (1, 0)(2, 0)
CharacterSet is set to TimesTen8.
Has something changed between TT6.0 and TT11.2 concerning this functionality?
I guess my first question would be why would you upgrade to the already very old 11.2.1 release instead of the established and mature current release stream of 11.2.2? Unless you have some compelling reason to use 11.2.1 then you really ought to be on 11.2.2(.6.0).
There are huge changes across the board between 6.0 (ancient) and 11.2.1/2. These changes affect most areas of the product. However, basic SQL functionality like this generally should not change (bug fixes excepted of course), though I am curious as to the strange '(1,0)(2,0)' at the end of the ORDER BY clause. That syntax does not look valid - what are you trying to achieve with that? Also, perhaps you could give us a clue as to how this statement is 'failing'? Perhaps provide the (full) error message? There is not much to go on otherwise...
I just noticed your mention of the ':' character in the title of the post. I suspect that your issue is not related to the ':' specifically but to the change in how multiple parameters with the same name are handled. Please see the description of the DSN/connection attribute 'DuplicateBindMode' in the TimesTen 11.2.1 Reference Guide. To quote it here:
This attribute determines whether applications use traditional TimesTen parameter binding for duplicate occurrences of a parameter in a SQL statement or Oracle-style parameter binding.
Traditionally, in TimesTen, multiple instances of the same parameter name in a SQL statement are considered to be multiple occurrences of the same parameter. When assigning parameter numbers to parameters, TimesTen assigns parameter numbers only to the first occurrence of each parameter name. The second and subsequent occurrences of a given name do not get their own parameter numbers. In this case, A TimesTen application binds a value for every unique parameter in a SQL statement. It cannot bind different values for different occurrences of the same parameter name nor can it leave any parameters or parameter occurrences unbound.In Oracle Database, multiple instances of the same parameter name in a SQL statement are considered to be different parameters. When assigning parameter numbers, Oracle Database assigns a number to each parameter occurrence without regard to name duplication. An Oracle Database application, at a minimum, binds a value for the first occurrence of each parameter name. For the subsequent occurrences of a given parameter, the application can either leave the parameter occurrence unbound or it can bind a different value for the occurrence.
The default value for this attribute is 0 (Oracle mode). It looks to me like you need to use 1 (TimesTen legacy mode). A better longer term fix is to change you SQL to be:
select NodeId , Idx from Santera.eswitchInterface where ( ( NodeId >= :NodeId1 ) and ( ( NodeId > :NodeId2 ) or ( Idx > :Idx ) ) ) order by NodeId , Idx
and change the application code to bind an input value for both ':NodeId1' and ':NodeId2'. This will work in either binding mode and will allow you to move to the default (Oracle) binding mode which will future proof your code against any such future time when we may decide to deprecate TimesTen binding mode.