7 Replies Latest reply on Jun 27, 2017 10:23 AM by ipdvo51633454

    ColdFusion 2016 loosing variable values

    ipdvo51633454

      ColdFusion 2016 v2016.0.04.302561 (all updates released to this date installed)

      Tomcat v8.5.11.0

      OS Windows Server 2012 R2 v6.3

      Adobe Driver Version: 5.1.4

      IIS v8.5.9600.16384

       

      Hi,

       

      We are face a pretty strange and intermitent problem: ColdFusion is loosing variable values from SQL Server queries and variables created in the middle of the script.

       

      When we try to print de variable value with cfoutput or cfdump the return value is blank.

       

      It happens from time to time and sometimes it goes away without any action taken from our server staff and sometimes it stay like that until the ColdFusion service is stoped and started again.

       

       

       

      2016.0.04.302561

        • 1. Re: ColdFusion 2016 loosing variable values
          BKBK Adobe Community Professional & MVP

          Please show us the code and a printscreen of the result.

          • 2. Re: ColdFusion 2016 loosing variable values
            ipdvo51633454 Level 1

            Expected result:

            ok.png

             

            Issue:

            nok.png

             

             

            Code:

            Index.cfm

            The session is set on this page that validates if the user is logged in or not:

             

            <cfset session.qryCliente = new model.cliente().buscaCliente(dsn=this.dsn,id_job=url.job) />

             

            If the user is not logged in it will be redirected to login.cfm after setting the session above

             

            cliente.cfc

            <cffunction name="buscaCliente" displayname="buscaCliente" description="Busca todos os dados necessários para costumizar o sistema conforme cliente" access="remote" returntype="query">
               <cfargument name="dsn" type="string" required="yes">

                    <cfargument name="projeto" type="string" required="no">

                    <cfargument name="id" type="numeric" required="no">

                    <cfargument name="id_job" type="numeric" required="no">

                    <cfargument name="cnpj" type="string" required="no">

                    <cfargument name="segmento" type="string" required="no">

                    <cfargument name="cliente" type="string" required="no">

                    <cfargument name="job" type="string" required="no">

                    <cfargument name="nome_banco" type="string" required="no">

                    <cfargument name="limitClientes" type="numeric" required="no">

                   

                    <cfif IsDefined('arguments.cnpj')>

                         <cfset my.cnpj = ReplaceList(arguments.cnpj,'.,/,-',',,,') />

                    </cfif>

                   

                    <cfquery datasource="#arguments.dsn#" name="busca_dados">           

                        select <cfif IsDefined('arguments.projeto')> top 1</cfif>

                            c.id_cliente,

                            ax.campo as segmento,

                            ax.id_auxiliar as id_segmento,

                            c.nome_empresa,

                            c.cnpj,

                            c.id_erp,

                            convert(char(10),c.dt_criacao,103) as dt_criacao_empresa,

                            u.nome as usuario,

                            c.possui_job,

                            j.id_job,

                            j2.id_job as id_job_cliente,

                            j.nome_job,

                            j.desc_job,

                            convert(char(10),j.dt_criacao,103) as dt_criacao_job,

                            j.dominio,

                            j.pasta_projeto,

                            j.nome_banco,

                            j.ativo,

                            j.banco_dsn as dsn,

                            j.cor_estilo_login,

                            j.cor_estilo_headerlogo,

                            j.cor_estilo_header,

                            j.cor_estilo_menu,

                            j.cor_estilo_login_front,

                            j.cor_estilo_header_front,

                            j.cor_estilo_header_font,

                            j.cor_estilo_conteudo_title,

                            j.cor_estilo_conteudo,

                            j.modelo_icon_front,

                            j.cor_modelo_icon_front,

                            j.cor_icon_front,

                            j.cor_skin_menu,

                            j.id_produto_ipdv,

                            j.id_tipo_checkin,

                            j.trava_checkin_raio,

                            j.key_sistema

                       

                        from tb_clientes c

                        inner join tb_auxiliar ax on ax.id_auxiliar = c.id_canal_comercial and ax.grupo = 'canal_comercial'

                        inner join tb_usuarios u on u.id_usuario = c.id_usuario_criador

                        left join tb_jobs j on j.id_cliente = c.id_cliente

                        <cfif find('cliente',dsn,1) gt 0>

                       left join #session.qryCliente.nome_banco#.dbo.tb_jobs j2 on j2.id_job_prateleira = j.id_job

                        <cfelse>

                       left join tb_jobs j2 on j2.id_job = j.id_job

                        </cfif>

                        where 1 = 1

                        <cfif IsDefined('arguments.projeto')>

                       

                       and (j.dominio in(<cfqueryparam list="yes" cfsqltype="cf_sql_varchar" value="#arguments.projeto#" />)
                             or j.pasta_projeto = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.projeto#" />

                                  )

                        </cfif>

                        <cfif IsDefined('arguments.id')>

                       and c.id_cliente = #arguments.id#

                        </cfif>

                        <cfif IsDefined('arguments.nome_banco')>

                       and lower(j.nome_banco) = '#LCase(arguments.nome_banco)#'

                        </cfif>

                        <cfif IsDefined('arguments.id_job')>

                       and j.id_job = #arguments.id_job#

                        </cfif>

                        <cfif IsDefined('arguments.cnpj')>

                       and c.cnpj = <cfqueryparam cfsqltype="cf_sql_varchar" value="#my.cnpj#" />

                        </cfif>

                        <cfif IsDefined('arguments.segmento')>

                       and ax.campo like <cfqueryparam cfsqltype="cf_sql_varchar" value="%#arguments.segmento#%" />

                        </cfif>

                        <cfif IsDefined('arguments.cliente')>

                       and lower(c.nome_empresa) like <cfqueryparam cfsqltype="cf_sql_varchar" value="%#LCase(arguments.cliente)#%" />

                        </cfif>

                        <cfif IsDefined('arguments.job')>

                       and lower(j.nome_job) = <cfqueryparam cfsqltype="cf_sql_varchar" value="#LCase(arguments.job)#" />

                        </cfif>

                        <cfif IsDefined('arguments.limitClientes') and !IsDefined('arguments.id_job') and !IsDefined('arguments.id')>

                       and c.id_cliente in(

                                        select top #arguments.limitClientes# id_cliente

                                        from tb_clientes order by id_cliente desc

                                    )

                        </cfif>

                       

                        order by c.id_cliente desc,j.id_job desc

                    </cfquery>

                   

                    <cfreturn busca_dados>

                </cffunction>

             

            Login.cfm

            If I dump the session with the query result, when the issue is impacting our servers the resultset comes with no records where normally should be one record.

            <cfdump var="#session.qryCliente#" />

            • 3. Re: ColdFusion 2016 loosing variable values
              Steve Sommers Level 4

              I would look closely and compare the where clauses of your SQL statements.

              • 4. Re: ColdFusion 2016 loosing variable values
                BKBK Adobe Community Professional & MVP

                Two remarks:

                 

                1) busca_dados seems to be a function-local variable. So, you should add the following line after the last cfargument tag:

                 

                <cfset var busca_dados = "">

                 

                2) Your code seems to have recursion. The definition of session.qryCliente, that is,

                 

                <cfset session.qryCliente = new model.cliente().buscaCliente(dsn=this.dsn,id_job=url.job) />

                 

                calls the function buscaCliente(). However, the variable session.qryCliente occurs within the function. That looks like it could be a problem.

                • 5. Re: ColdFusion 2016 loosing variable values
                  ipdvo51633454 Level 1

                  Couldn´t log in at forums yesterday to a server erro at the log in.

                   

                  Anyway, we found the problem.

                   

                  ColdFusion is mixing thing up when you use the  "use database" command with SQL Server.

                   

                  Script example:

                  <cfquery datasource="datasource1" name="test1">

                      select * from tableOne

                  </cfquery>

                   

                  <cfdump var="#test1#">

                   

                   

                  <cfquery datasource="datasource1" name="test2">

                    use anotherDatabase

                    select * from anotherTable

                  </cfquery>

                   

                  <cfdump var="#test2#">

                   

                  <cfquery datasource="datasource1" name="test1">

                      select * from tableOne

                  </cfquery>

                   

                  <cfdump var="#test1#">

                   

                  What will happen:

                  Step 1: ColdFusion will run the first query and return it´s result normally.

                  Step 2: ColdFusion will change to the database that the SQL command is telling it to turn and will run the second query and return it´s result normally.

                  Step 3: If the tableOne exists on the anotherDatabase database ColdFusion will run the query at anotherDatabase.tableOne instead of running where the datasource is really pointed at. And after running this script it will still be pointing to the anotherDatabase database for every other connection until you save the datasource1 again.

                   

                  Pretty anoying bug!!

                   

                   

                  EDIT:

                   

                  The use database command was being used in another application that used the same datasource this application was relying on. This command was not being used anywhere on the code where the problem was detected.

                  • 6. Re: ColdFusion 2016 loosing variable values
                    Steve Sommers Level 4

                    I've found it dangerous to "use database" with CF datasources. The database connections are shared and the connection could be left pointing to a different database than what CF is assuming. I'm not sure this has ever changed since we encountered strange issue with use database but one thing for certain is that you'll need to wrap your query calls within cftransaction block to for the same connection.

                     

                    <cftransaction>

                    <cfquery>use database</cfquery>

                    <cfquery>...</cfquery>

                    <cfquery>...</cfquery>

                    </cftransaction>

                     

                    But again, I would recommend against using "use database" and instead explicitly name the database in the query:

                     

                    SELECT x, y, z FROM [database].[dbo].[table]

                     

                    This assumes MSSQL.

                    • 7. Re: ColdFusion 2016 loosing variable values
                      ipdvo51633454 Level 1

                      We already made the changes in order to work with database.dbo.table.

                       

                      The problem we encoutered should no impact on the datasource itself, which it did.

                       

                      If you simulate the problem as I showed you will see that it will change the datasource for all application users and will remain pointed to  the new database in use until you save the datasource again in CFAdmin.

                       

                      This shouldn´t be the expected behaviour.