Parse error unidac

[albourgz]

c++ builder XE10 pro, unidac 6.4.14, TUniQuery.
This sql is correctly parsed:

Code: Select all

SELECT {fn TRUNCATE(DESTROY_DATE,'')} as closedate, sealbag, sb_type
FROM vsectransdetok 
WHERE DEPT=:1 AND SB_TYPE=:2
AND {fn TRUNCATE(DESTROY_DATE,'')}={fn TRUNCATE({CURRENTDATE},'')}
GROUP BY {fn TRUNCATE(DESTROY_DATE,'')}, sealbag, sb_type
ORDER BY {fn TRUNCATE(DESTROY_DATE,'')}, sealbag, sb_type

This one is not (I get "Empty Macro or function name" at design-time when saving the query, even not when opening it)

Code: Select all

SELECT o.SEALBAG,  si.name as gache, pr.name as product, o.tsCreate, o.ObjectKey, o.infoText, o.infoDate, {fn truncate(sm1.ondt,'')} as dtgache, {fn truncate(sm2.ondt,'')} as dtdestroy, o.cnt, o.job
FROM objectsToClass o join sites si on o.info1=si.id join products pr on o.product=pr.id 
left outer join stockmoves sm1 on o.stockId1=sm1.ID left outer join stockmoves sm2 on o.stockId2=sm2.ID
WHERE  o.ObjectType=:1


AND o.TSCREATE BETWEEN :2 and :3
GROUP by o.sealbag, si.name, pr.name, o.tsCreate, o.ObjectKey, o.infoText, o.infoDate, {fn truncate(sm1.ondt,'')}, {fn truncate(sm2.ondt,'')}, o.cnt, o.job
order by o.sealbag, si.name, pr.name, o.tsCreate, o.ObjectKey, o.infoText, o.infoDate, {fn truncate(sm1.ondt,'')}, {fn truncate(sm2.ondt,'')}, o.cnt, o.job

Why wy why? Only one macro is used, truncate, and the syntax used is the same in both statements.

[MaximG]

Please specify what kind of database do you use? For further issue investigation, please send us the script to create database objects, used in your queries.

[albourgz]

it is oracle 12c but the TUniSession was disconnected.
To reproduce the problem, put a TUniQuery with as select:

Code: Select all

SELECT count(o.SEALBAG),  {fn truncate(sm1.ondt,'')} as dtgache
FROM objectsToClass o 
left outer join stockmoves sm1 on o.stockId1=sm1.ID left 
GROUP BY { fn truncate(sm1.ondt,'')}
order by { fn truncate(sm1.ondt,'')}

Notice the space between { and fn that is in order by and Group By and NOT in select.
Once this happens, The only way to fix it is to put another TUniQuery with {fn expressions exactly the same (case and spaces) in select and in order by/group by.

[MaximG]

We still can't reproduce the error according to your description. The following construction is syntactically correct:

Code: Select all

{fn truncate(sm1.ondt,")}

, no additional spaces in front of the fn operator. In this case, this query returns correct data in our test database. Maybe, we will be able to reproduce the error using your test data. So, please send us the script for creating the DB table and the query for this table using UniSQL, executing which you get the error.

[albourgz]

Do you mean that a space between { and fn is something that is syntaxically incorrect?
To reproduce, new vcl project, drop TUniConnection on the form. Specify Oracle as provider, don't even connect it.
Drop TUniQuery on the form, paste as sql:

Code: Select all

SELECT o.SEALBAG,  si.name as gache, pr.name as product, o.tsCreate, o.ObjectKey, o.infoText, o.infoDate, {fn truncate(sm1.ondt,'')} as dtgache, {fn truncate(sm2.ondt,'')} as dtdestroy, o.cnt, o.job
FROM objectsToClass o join sites si on o.info1=si.id join products pr on o.product=pr.id 
left outer join stockmoves sm1 on o.stockId1=sm1.ID left outer join stockmoves sm2 on o.stockId2=sm2.ID
WHERE  o.ObjectType=:1


AND o.TSCREATE BETWEEN :2 and :3
GROUP by o.sealbag, si.name, pr.name, o.tsCreate, o.ObjectKey, o.infoText, o.infoDate, { fn truncate(sm1.ondt,'')}, {fn truncate(sm2.ondt,'')}, o.cnt, o.job
order by o.sealbag, si.name, pr.name, o.tsCreate, o.ObjectKey, o.infoText, o.infoDate, {fn truncate(sm1.ondt,'')}, {fn truncate(sm2.ondt,'')}, o.cnt, o.job

And press OK button.

[MaximG]

We will fix the behavior of UniDAC, while using spaces in expressions UniSQL. As a temporary solution do not use spaces in front of the construction fn :

Code: Select all

 ...GROUP by o.sealbag, si.name, pr.name o.tsCreate, o.ObjectKey, o.infoText, o.infoDate, {fn truncate(sm1.ondt,")}, …