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
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
...Copy link to clipboard
Copied
Please show us the code and a printscreen of the result.
Copy link to clipboard
Copied
Expected result:
Issue:
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#" />
Copy link to clipboard
Copied
I would look closely and compare the where clauses of your SQL statements.
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.
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.
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].
Copy link to clipboard
Copied
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.