SQL for reserved keywords from a MS SQL db, via JDBC

Q&A
1

This is my SQL

SELECT
Requester.FIRST_NAME,
WorkOrder.Title,
WorkOrder.DESCRIPTION AS TheDescription,
WorkOrder.CREATEDTIME
FROM
WorkOrder
INNER JOIN AAAUser AS Requester ON REQUESTERID = Requester.USER_ID
WHERE
WorkOrderID = 147570


which is bringing back values for FIRST_NAME, Title and CREATEDTIME. However the DESCRIPTION field is coming back as "net.sourceforge.jtds.jdbc.ClobImpl@51b9cb35".

I've tried [WorkOrder].[DESCRIPTION], WorkOrder.[DESCRIPTION] and all other combinations with [], " and ' characters, and nothing is cracking it.


Anybody else any ideas?

BTW - The use of a keyword as a ciolumn heading in the database, whilst stupid, is also out of my hands, and this has to be worked around.

2

Hi

I found the answer with the help of the MS SQL experts here... but I'll post anyway.

SELECT
Requester.FIRST_NAME,
WorkOrder.Title,
CONVERT(VARCHAR(500),WorkOrder.DESCRIPTION) AS HDDesc,
WorkOrder.CREATEDTIME
FROM
WorkOrder
INNER JOIN AAAUser AS Requester ON REQUESTERID = Requester.USER_ID
WHERE
WorkOrderID = ${hDNumber}

The column had some other value set that required the CONVERT() value - but once applied it started to work.

3

Hi Lawrence,

What about creating a view on the 'stupid' table?

CREATE VIEW WOView AS SELECT Title, DESCRIPTION as WODesc, CREATEDTIME FROM WorkOrder;

The SQL above becomes something like:

SELECT Requester.FIRST_NAME,
 WOView.Title,
 WOView.WODesc AS TheDescription,
 WOView.CREATEDTIME
 FROM  WOView
 INNER JOIN AAAUser AS Requester ON REQUESTERID = Requester.USER_ID
 WHERE
 WOView.WorkOrderID = 147570

Disclaimer: this has not been tested...

Jérôme