QoQ and leading space(s) suppression

[Frustated]

I am facing a peculiar problem with QoQ on CFMX 6.1

My script performs the following:

<cfquery name="Q1" datasource="DATASOURCE1">
SELECT *
FROM tblPAYMENTS
</cfquery>

Three rows are returned in the result set. One of the rows is as follows:

FIELD_1 = 'A'
FIELD_2 = ' 06 M3'

If I do a CFDUMP of query Q1, FIELD_2 does contain string listed above,
including the leading space

[Dan Bracuk]

Don't allow the leading spaces in the first place.

[Frustated]

Don,

I do not own the data I am retrieving, and the spaces are part of the data
structure, meaning they belong there.

I was wondering why QoQ cannot preserve the original data.

Thank you.

-Mauricio

[efecto747]

Just replace spaces in the original select with something else so you can put
the spaces back in when you're finished qith your QofQ.

here's an example:

<cfquery name="Q1" datasource="DATASOURCE1">
SELECT FIELD_1, REPLACE(FIELD_2,' ','^^^') AS FIELD_2
FROM tblPAYMENTS
</cfquery>

Your data will look like this:

FIELD_1 = 'A'
FIELD_2 = '^^^06^^^M3'

Then run the QofQ:

<cfquery name="Q2" dbtype="query">
SELECT FIELD_2
FROM Q1
WHERE FIELD_1 = 'A'
</cfquery>

once the QofQ is run you can replace the '^^^' strings with spaces to get the
original text:

<cfoutput>
#Replace(Q2.FIELD_2, '^^^', ' ', 'ALL')#
</cfoutput>

cheers.

[Adam Cameron]

> Next, I perform a QoQ like the following:

>
> <cfquery name="Q2" dbtype="query">
> SELECT FIELD_2
> FROM Q1
> WHERE FIELD_1 = 'A'
> </cfquery>
>
> Now, when I do a CFDUMP of Q2, FIELD_2 value is '06 M3' (The leading spaces
> get suppressed or trimmed).
>
> How do I get around that problem?

Other people seem to be offering suggestions as to how to work around this:
good.

Have you raised it as a bug in CF?

--
Adam

[Adam Cameron]

> Have you raised it as a bug in CF?

Actually, I was being pre-emptive there.

I cannot replicate what you say is happening on CFMX7.0. I'm using a
MS-SQL Server DB, with this driver:
"macromedia.jdbc.sqlserver.SQLServerDriver is 3.3".

I have created this table:

CREATE TABLE [Table1] (
[id_col] [int] IDENTITY (1, 1) NOT NULL ,
[space_col] [varchar] (50) NULL
) ON [PRIMARY]

I have populated it thus:

1 [no leading space]
2 [ leading space]
3 [trailing space ]
4 [ both ]

(I have put the square brackets in here simply to demonstrate where the
spacing is: the square brackets are NOT in the actual data).

I then run this process:
<cfquery name="q3" datasource="junk">
select *
from Table1
</cfquery>
<cfquery name="q4" dbtype="query">
select *
from q3
</cfquery>
<hr />
<cfoutput query="q3">
[#id_col#][#space_col#]<br />
</cfoutput>
<hr />
<cfoutput query="q4">
[#id_col#][#space_col#]<br />
</cfoutput>

And the output - from both queries - includes all the spacing I would
expect to see:

[1][no leading space]
[2][ leading space]
[3][trailing space ]
[4][ both ]
[1][no leading space]
[2][ leading space]
[3][trailing space ]
[4][ both ]

What happens if you replicate the above situation? What DB / driver / CF
version are you using?

--
Adam

[Frustated]

Thank you, Adam.

Code is attached:



<cfquery name = "SALES" datasource="DATASOURCE">
SELECT CHAR_CODE, CUST_ID
FROM SALES
WHERE CUST_ID = 1
</cfquery>

Result Set:

ROW CHAR_CODE CUST_ID


---------- ------------------------------------------------ -------------
000001 06 1
000002 06 1


<cfquery name = "qGetSales" dbtype="query">
SELECT *
FROM SALES
WHERE CUST_ID = 1
</cfquery>

Result Set:

ROW CHAR_CODE CUST_ID


---------- ------------------------------------------------ -------------
000001 06 1
000002 06 1