railo 3.1.2.021 issue

[andreacfm]

Steve,

I have upgraded my railo local machine to latest BER 021 and I have a
strange issue.
Using mysql ( no tested on any other ).
Issue is related to the runsql activity.

Example:

<cfquery datasource="dsn" name="q" psq="false">
SHOW TABLES LIKE 'utente'
</cfquery>

This run just fine but if I ask to dm to getTableData('utente') i get
an error at the moment that dm run the same query via runsql.

<cfset dm =
createObject('com.andreacfm.datax.dataMgr.dataMgr').init(datasource =
'dsn')>
<cfset dm.getTableData('utente')>

error is :

Railo 3.1.2.021 Error (Database)
Message You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near 'utente''' at line 1
SQL SHOW TABLES LIKE ''utente''
DatabaseName MySQL
DatabaseVersion 5.1.48
DriverName MySQL-AB JDBC Driver
DriverVersion mysql-connector-java-5.0.6 ( $Date: 2007-03-09 22:13:57
+0100 (Fri, 09 Mar 2007) $, $Revision: 6341 $ )
Datasource portalevendesiaffittasi
Stacktrace The Error Occurred in
/Users/andrea/dev/www/va/libs/com/andreacfm/datax/dataMgr/
DataMgr_railo_query.cfm: line 15

13: <cfquery attributeCollection="#sRailoQuery#"><cfloop index="i"
from="1" to="#ArrayLen(aSQL)#" step="1"><cfif
IsSimpleValue(aSQL[i])><cfset temp = aSQL[i]>#Trim(temp)#<cfelseif
IsStruct(aSQL[i])><cfset aSQL[i] =
queryparam(argumentCollection=aSQL[i])><cfswitch
expression="#aSQL[i].cfsqltype#"><cfcase
value="CF_SQL_BIT">#getBooleanSqlValue(aSQL[i].value)#</cfcase><cfcase
value="CF_SQL_DATE,CF_SQL_DATETIME">#CreateODBCDateTime(aSQL[i].value)#</
cfcase><cfdefaultcase><!--- <cfif
ListFindNoCase(variables.dectypes,aSQL[i].cfsqltype)>#Val(aSQL[i].value)#<cfelse>
---><cfqueryparam value="#aSQL[i].value#"
cfsqltype="#aSQL[i].cfsqltype#" maxlength="#aSQL[i].maxlength#"
scale="#aSQL[i].scale#" null="#aSQL[i].null#" list="#aSQL[i].list#"
separator="#aSQL[i].separator#"><!--- </cfif> ---></cfdefaultcase></
cfswitch></cfif> </cfloop></cfquery>
14: <cfelse>
15: <cfquery
attributeCollection="#sRailoQuery#">#Trim(arguments.sql)#</cfquery>
16: </cfif>

called from/Users/andrea/dev/www/va/libs/com/andreacfm/datax/dataMgr/
_DataMgr.cfc: line 2496

2494: <cfif Len(arguments.sql)>
2495: <cfif variables.CFServer EQ "Railo">
2496: <cfinclude template="DataMgr_railo_query.cfm">
2497: <cfelse>
2498: <cfif StructKeyExists(variables,"username") AND
StructKeyExists(variables,"password")>

called from/Users/andrea/dev/www/va/libs/com/andreacfm/datax/dataMgr/
DataMgr_MYSQL.cfc: line 140

138:
139: <cfscript>
140: var qTable = runSQL("SHOW TABLES LIKE
'#arguments.tablename#'");
141: var qFields = 0;
142: var qPrimaryKeys = 0;

called from/Users/andrea/dev/www/va/libs/com/andreacfm/datax/dataMgr/
_DataMgr.cfc: line 2078

2076:
2077: <cftry>
2078: <cfset arrTableStruct =
getDBTableStruct(arguments.tablename)>
2079:
2080: <cfloop index="ii" from="1" to="#ArrayLen(arrTableStruct)#"
step="1">

called from/Users/andrea/dev/www/va/libs/com/andreacfm/datax/dataMgr/
_DataMgr.cfc: line 2075

2073:
2074: <cfset var ii = 0>
2075: <cfset var arrTableStruct = 0>
2076:
2077: <cftry>

called from/Users/andrea/dev/www/va/libs/com/andreacfm/datax/dataMgr/
DataMgr_MYSQL.cfc: line 330

328:
329: <cfif NOT
StructKeyExists(variables.tables,arguments.tablename)>
330: <cfset loadTable(arguments.tablename)>
331: </cfif>
332:

called from/Users/andrea/dev/www/va/libs/com/andreacfm/datax/dataMgr/
_DataMgr.cfc: line 1694

1692:
1693: <cfif StructKeyExists(arguments,"tablename") AND
Len(arguments.tablename)>
1694: <cfset checkTable(arguments.tablename)><!--- Check whether
table is loaded --->
1695: <cfset sResult = StructNew()>
1696: <cfif
ListFindNoCase(StructKeyList(variables.tables),arguments.tablename)>

called from/Users/andrea/dev/www/va/test.cfm: line 2

looks like quotes are not correctly skipped anymore:

Any suggestion?

Andrea

[Steve Bryant]

Andrea,

It may be that I set the psq value incorrectly in
"DataMgr_railo_query.cfm". Would you mind looking into that? I know
you have experience with that attribute (after all, my search on it
just now returned a blog entry you wrote).

I thought I had it working with the same code on Railo earlier though.
I'm not sure why that would have been. Maybe I set that value in the
admin and failed to notice.

Let me know how it works out.

Thanks,

Steve

> --
> You received this message because you are subscribed to the Google Groups "DataMgr" group.
> To post to this group, send email to dat...@googlegroups.com.
> To unsubscribe from this group, send email to datamgr+u...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/datamgr?hl=en.
>
>

[salvatore fusto]

The same with H2! i had to downgrade to 020
regards

[salvatore fusto]

i changed the value of variable psd in dataMgr_query_railo.cfm from
false to true and now all work fine.
Confirm required.
salvatore

[salvatore fusto]

i tested obtaining:
if i set psd to true, dataMgr works on railo 3.1.2.021 and DOES NOT
work on 020;
if i set psd to false, the original value, dataMgr works on railo
3.1.2.020 and DOES NOT work on 021;
So, how can we solve? i'm not sure this is a dataMgr problem!
Salvatore

[salvatore fusto]

i just tested with 3.2 RC with the same error: i have seen, either in
your code with mysql or my code with h2, (i bet it is the same on your
example), that the wrong sql statement returned has a double quote in
WHERE/LIKE, but i have written my query with a single quotes:
in getCreateSql() i wrote:
<cfset qTables = runSQL("SELECT Table_Name FROM
INFORMATION_SCHEMA.TABLES WHERE table_type = 'TABLE'")> (single quotes
in table_type='TABLE') but the stack error returns this line eith a
double quote: may be an issue in PreserveSingleQuote() function?
regards
Salvatore

[salvatore fusto]

Steve,about preserveSingleQuote(), can you explain the following code:

<cffunction name="DMPreserveSingleQuotes" access="private"
returntype="string" output="false" hint="">
<cfargument name="sql" type="any" required="yes">

<cfset var result = "">
------------------------------------------------------------------------------------------------
<cfif variables.CFServer EQ "Railo">
<cfset result = PreserveSingleQuotes(arguments.sql)>
<cfelse>
<cfset result = PreserveSingleQuotes(arguments.sql)>
</cfif>
-------------------------------------------------------------------------------------------------
<cfreturn result>
</cffunction>

why the test on variables.cfServer="railo"? in either case, the action
is the same, if i read well!
regards
Salvatore

[andreacfm]

UPdating to 3.2 RC with datamgr 2.5 beta 1 solved the issue for me
with no need to change the psq in dm that is setted to false.

Andrea

[andreacfm]

Sorry I correct myself.
I need to set the psg attributes to true to make it work with 3.2.

Is that making any sense Steve?

Andrea

On Sep 27, 4:12 pm, salvatore fusto <ing.fu...@gmail.com> wrote:

[Steve Bryant]

Salvatore,

Yep. Sorry about the confusion on that. That is an anomoly of having
had a conditional in there earlier that actually had an effect. I left
the CFIF in just as a reminder to myself that it had been different at
one time. I certainly meant to remove it though. I have done so
locally now.

Steve

[Steve Bryant]

Andrea,

It does. I have corrected that now in my local copy of DataMgr 2.5.

Thanks for catching that,

Steve

[salvatore fusto]

The problem is that the stable version of railo is 3.1.2.001 that
requires psq = false, so deploying now we have to set psq to false;
when a stable 3.2 release will be available, we will have to set psq
to true in that dataMgr's template.
Salvatore

On 28 Set, 03:56, Steve Bryant <sebto...@gmail.com> wrote:
> Andrea,
>
> It does. I have corrected that now in my local copy of DataMgr 2.5.
>
> Thanks for catching that,
>
> Steve
>

[andreacfm]

Yes,

put definitly psq = true makes more sense

Andrea