Cfset array oracle sql

dev support
1

Hi

i’m stuck with cfset array.

select obid, username from table1

Output:
obid Username
23232 max
34523 sandy
44444 helen
…

<cfset getobj= "#test.obid#"> 

<cfquery name="othertest" datasource="htc">
select c.obid, c.username, a.loginname 
from table1 c, table2 a
where c.obid= a.obid
and a.obid = (#preserveSingleQuotes(getobj)#)  
</cfquery>

the goal is to get:

and a.obid = (23232, 34523, 44444)

but this method does not work… any suggestion ?

2

Try in instead of =

3

was a typo (sorry) it should be IN, but it still don’t show the requested results

4

Is test.obid really a list? What is its output?

I miss something like:

<cfset getobj= ValueList(query.obid,";")>

Don’t know Oracle, but in MySQL I usually make use of list attribute of cfqueryparam:

<cfquery name="othertest" datasource="htc">
select c.obid, c.username, a.loginname 
from table1 c, table2 a
where c.obid= a.obid
and a.obid IN (<cfqueryPARAM value = "#getobj#" CFSQLType = "CF_SQL_INTEGER" list="true" separator=";">)
</cfquery>

To tacle down strange SQL results, I sometimes copy the SELECT statement from cfquery to a raw cfoutput code (getting rid of the cfqueryparam), then I look at the page result and copy/paste the output directly into my SQL-Client (in my case heidisql) to check the results. That made me have some facepalms already :smiley:

Example:

<cfquery name="othertest" datasource="htc">
select c.obid, c.username, a.loginname 
from table1 c, table2 a
where c.obid= a.obid
and a.obid in (#preserveSingleQuotes(getobj)#)  
</cfquery>

<cfoutput>
select c.obid, c.username, a.loginname 
from table1 c, table2 a
where c.obid= a.obid
and a.obid in (#preserveSingleQuotes(getobj)#)  
</cfoutput>

The last cfoutput part I simply copy from the browser result, and paste it to another SQL-Client to check the results of that exact SQL statement.

5

Thx Andreas, for the feedback, this is the solution

<cfset getobj= ValueList(query.obid,";")>
and a.obid IN (<cfqueryPARAM value = "#getobj#" CFSQLType = "CF_SQL_INTEGER" list="true" separator=";">)
1 Like