• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

ColdFusion 2016 loosing variable values

Community Beginner ,
Jun 21, 2017 Jun 21, 2017

Copy link to clipboard

Copied

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

Views

874

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 1 Correct answer

Community Beginner , Jun 27, 2017 Jun 27, 2017

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 tabl

...

Votes

Translate

Translate
Community Expert ,
Jun 22, 2017 Jun 22, 2017

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Jun 26, 2017 Jun 26, 2017

Copy link to clipboard

Copied

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#" />

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
Jun 26, 2017 Jun 26, 2017

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Jun 26, 2017 Jun 26, 2017

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Jun 27, 2017 Jun 27, 2017

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
Jun 27, 2017 Jun 27, 2017

Copy link to clipboard

Copied

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].

This assumes MSSQL.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Jun 27, 2017 Jun 27, 2017

Copy link to clipboard

Copied

LATEST

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation