Skip to main content
M
maxime_bonin
Participant
December 1, 2009
Question

Oracle Alter Session not working in CF9

  • December 1, 2009
  • 1 reply
  • 818 views

Hello,

I'm trying to understand differences between CF5 and CF9 when I retrieve numbers and dates from an Oracle Database.

The code I ran on CF5 and CF9 servers :

<cfoutput>

<cftransaction>
<cfquery datasource="intranet">
alter session SET NLS_TERRITORY =  FRANCE
</cfquery>

<cfquery name="qry" datasource="intranet">
select 
     TO_NUMBER(12345/10) as nbr,
     sysdate as dt,
     TO_CHAR(1234.56,'L99G999D99') as cur
from dual
</cfquery>

</cftransaction>

#qry.nbr#<br>
#qry.dt#<br>
#qry.cur#


</cfoutput>

I've got those outputs :

Result in CF5Result in CF9
1234,5
01/12/09
¿1.234,56		1234.5
2009-12-01 19:16:04.0
¿1.234,56

The first two rows in CF5 display data in French format. That's not the same for CF9, the data are in American format.

Then I changed the NLS_TERRITORY parameter 

alter session SET NLS_TERRITORY =  AMERICA
Result in CF5Result in CF9
1234.5
01-DEC-09
$1,234.56		1234.5
2009-12-01 19:20:39.0
$1,234.56
The two first row haven't changed in CF9, it seems that the "alter session" has no effect on number and date format in query results. Is that a bug or am I misunderstanding something ?
Regards,
Maxime
This topic has been closed for replies.
1 reply
ilssac
ilssac
Inspiring
December 1, 2009
I can't advise much on your issue other then to explain that ColdFusion is the least likely culprit.
All ColdFusion does is bundle up the SQL text and send it to the database through the driver.  It then gets the record set from the database, again through the driver.
It does not interpret the SQL in any manner, it does not modify the record set in any manner.
Since the database is the same in both the CF5 and CF9 examples, I presume.  The likely canidate is different database drivers between what shipped in CF 5 and what shipped in CF 9.
I'm not working with CF 9 yet, but I presume it is no different then CF 8 and earlier that I am familiar with.  It is fairly easy to work with 3 different drivers between ColdFusion and Oracle.
1) The oracle JDBC database driver which comes with Enterprise ColdFusion, this is the one I presume you are using.
2) oracle JDBC thin client.  Get and install the Oracle thin client driver.  There are plenty of documentation on how to do this on the web.
3) oracle JDBC-ODBC bridge.  Install the oracle client on the server which will include an oracle ODBC driver AND|OR use the Microsoft Oracle ODBC driver and then use the "ODBC" ColdFusion JDBC-ODBC DSN connection to configure a connection to it.
It would be interesting to see if you get similar or different results based on which driver you use.
M
maxime_boninAuthor
Participant
December 2, 2009
Thank you for your reply.
I tried what you've suggested on the CF9 server.
The results are :
NLS_TERRITORY =1) oracle JDBC database2) oracle JDBC thin client3) oracle JDBC-ODBC bridge
AMERICA1234.5
2009-12-02 13:24:30.0
$1,234.56		1234.5
{ts '2009-12-02 00:00:00'}
$1,234.56		1234.5
2009-12-02 13:24:30.0
$1,234.56
FRANCE1234.5
2009-12-02 13:29:53.0
¿1.234,56		1234.5
{ts '2009-12-02 00:00:00'}
¿1.234,56		1234.5
2009-12-02 13:29:53.0
¿1.234,56

The behavior is pretty much the same in the three differents ways to call the database. The only difference is on the date display using the Oracle JDBC thin client, which is another format from those I've already got.
Regards,
Maxime

        
                                                

        

    


            

                                        

            
                        

                                                                                                            
            







        

                    

    
    





    


                                        

                                                        

            
        
    






                                    
Terms & ConditionsAccessibility statement

            
        



        
            
    
        
    
        



        
        
        


        


        
        
        
        
                                                        

                            
    

                    

  

    

      
        
      
    

    

      
        Using the community
      
      
        Terms of use
      
      
        Privacy
      
      
        Cookie preferences
      
      
        Do not sell or share my personal information
      
      
        ad choicesAdChoices