literal value in the select fieldlist returns not the string, but a java String object

Hi,

I'm using pgsql/jdbc in an evaluation of "bluedragon", a coldfusion app server clone. Following is an email to the bluedragon folks, but the behavior is probably really JDBC driver related... can anyone confirm that?

Briefly, the issue is that having a static, literal value in the select fieldlist returns not the string, but a java String object; not sure if this is the driver, or if bluedragon for some reason fails to do a toString() on it for some reason...

Here's the email to bluedragon support:

=========================================
Single quoted literals are usually used to concatenate some constant with the contents of a field. This works fine.

But I sometimes use a constant by itself, to create a column with just a string literal... for example, the word "Edit", to use for creating a clickable command hyperlink.

This does *not* work (as I expected it would), because instead of returning a text string, it's returning a java String object.

To see this:

<cfquery datasource="postgres" name="mike">
    select 'test text' as staticText, firstname, lastname
    from employees
</cfquery>
<cfdump var = "#mike#" >

Notice that the firstname and lastname columns are "normal", but the staticText column contains "object of java.lang.String".

There's an easy workaround... appending an empty string gives the desired result:

<cfquery datasource="postgres" name="mike">
    select 'test text' || '' as staticText, firstname, lastname
    from employees
</cfquery>
<cfdump var = "#mike#" >
=========================================



bluedragon-support <bluedragon-support@newatlanta.com> wrote:

Hi Mike,

You’ll have to forgive my ignorance, but I’ve never seen syntax like this before.  So, I created a table in Postgresql database named Employees and I dumped the same values into the table that you might already be familiar with.  I used cfsnippets which comes with CFMX and created a duplicate database in postgresql.  

 

So, what is an appropriate Select statement that I should try? 

 

<cfquery datasouce=”postgres” name=”Mike”>

Select ‘Dave’ from Employees

</cfquery>

 

<cfdump var=”#Mike#”>

 

Please edit my cfquery in such a way that I can see the problem.  Thanks,

-Lori

Bluedragon Support

 

---

From: Mike Morris [mailto:mcmorris95125@yahoo.com]
Sent: Friday, July 28, 2006 3:47 AM
To: bluedragon-support
Subject: RE: Thank You For Downloading BlueDragon

 

Hi,

Still evaluating BD... writing a simple SQL generator CFC as a test, using Postgres as back end.

I can't get a single-quoted literal passed to Postgres properly. I've RTFM'd and Googled at NewAtlanta, Postgres and JDBC, to no avail.

The issue is:

•     select 'myString' as literal, * from testtable

runs fine from pgAdmin, e.g., but from inside a CFQuery, I get various errors ... most of which contain doubled up instances of the single quote chars. I've tried PreserveSingleQuotes() on the SQL statement, escaping with "\" or doubling the quote, etc...

Any tips?

TIA,

MikeM



bluedragon-support <bluedragon-support@newatlanta.com> wrote:

Hi Mike!  Thank you for the info.  I’ll pass this along.  Wow, I really appreciate your work. 

 

-Lori

BlueDragon Support

---

Do you Yahoo!?
Get on board. You're invited to try the new Yahoo! Mail Beta.