SQLDataSource: How to generate for a integer field the sql expression field = 12345?

[mpretorian]

Hi,

I'm using smartgwt-power-3.0p.2012-07-17, oracle-11g and several sql datasources.
One datasource has a field:
<field name="amount" type="integer" nativeName="AMOUNT" operator="equals" />

and the generated fetch query looks like:

SELECT * FROM (SELECT /*+ FIRST_ROWS(75) */ a.*, rownum myrownum FROM (
	SELECT COLUMN1,AMOUNT		
	FROM TABLE1
	WHERE AMOUNT = '12345'
	ORDER BY 
	COLUMN1 DESC
) a where rownum <=76) WHERE myrownum >= 1

In database the AMOUNT column has the type NUMBER(16).

Our DBA said that the condition AMOUNT = '12345' should be changed to AMOUNT = 12345 in order to give Oracle the possibility to use the existing index on AMOUNT column.
How can I do that?
I thought that by specifying the integer type the SQLDataSource will generate and expression like AMOUNT = 12345 and not AMOUNT = '12345'.

Regards,
Mihnea

[Isomorphic]

We're looking at this - it's really an Oracle bug that it doesn't treat these as equivalent, but it looks like we can safely remove the quotes when the value is numeric and it's not a string-oriented operator (like startsWith).

[mpretorian]

Do you have any news about this issue?

[Isomorphic]

We've fixed this in 3.1d. We currently don't plan to apply it to 3.0p as it's fairly fundamental (not a "patch") and only works around an Oracle bug. Can you use it in 3.1d?

[mpretorian]

Thank you for the fix.
We currently use 3.0p and have no plan yet to change to 3.1d.
Would be good if you could apply this change also to 3.0p.

[Isomorphic]

That's unfortunate, but again, we don't plan to apply it to 3.0p - it's not appropriate as a patch. 3.1 GA will be out soon enough and we'd recommend testing against 3.1d now.

[mpretorian]

When is planned to be released 3.1 GA?

[mpretorian]

I tested with smartgwtpower-3.1d-20121012 and works well with the field defined like:
<field name="amount" type="integer" nativeName="AMOUNT" operator="equals" />
but if the field is defined like:
<field name="amount" type="long" nativeName="AMOUNT" operator="equals" />
is doesn't work, it still generates something like AMOUNT = '1234'.
Is it a bug?

[Isomorphic]

type="long" is not a valid field type. It's being interpreted as a text field for that reason. The valid field types are in the QuickStart Guide and JavaDoc.