Create view problem using TUniQuery

Discussion of open issues, suggestions and bugs regarding UniDAC (Universal Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
ertank
Posts: 160
Joined: Wed 13 Jan 2016 16:00

Create view problem using TUniQuery

Post by ertank » Wed 07 Aug 2019 11:23

Hello,

I am using Delphi 10.3.2, UniDAC 8.0.1, SQL Server 2012 Express 64Bit, targeting Win32 platform.

I have following script:

Code: Select all

CREATE VIEW [CRM_DayAlert] AS
SELECT     dbo.Customer.CustomerID, dbo.Customer.Username, dbo.CustomerContact.EntryDate, dbo.CustomerContact.EntryUser, 
                      dbo.CustomerContact.ContactName, dbo.CustomerContact.CustomerPosition, dbo.CustomerContact.Email, dbo.CustomerContact.Phone, 
                      dbo.CustomerContact.Extension, dbo.CustomerContact.MobilePhone, dbo.CustomerContact.Address, dbo.CustomerContact.Bithday, 
                      dbo.CustomerContact.Weddingday, dbo.CustomerContact.PartnerName, dbo.CustomerContact.PartnerBirthday, dbo.CustomerContact.ChildrenBirthDay, 
                      dbo.CustomerContact.VisitPeriod, dbo.CustomerContact.VisitDate, dbo.CustomerContact.PhoneCallPeriod, dbo.CustomerContact.PhoneCallDate, 
                      DATEADD(day, { fn MOD(CAST(dbo.CustomerContact.PhoneCallPeriod AS INT), DATEDIFF(day, dbo.CustomerContact.PhoneCallDate, GETDATE())) }, 
                      GETDATE()) AS PhoneCallPeriodDate, DATEADD(day, { fn MOD(CAST(dbo.CustomerContact.VisitPeriod AS INT), DATEDIFF(day, 
                      dbo.CustomerContact.VisitDate, GETDATE())) }, GETDATE()) AS VisitPeriodDate, CAST({ fn MOD(CAST(dbo.CustomerContact.VisitPeriod AS INT), 
                      DATEDIFF(day, dbo.CustomerContact.VisitDate, GETDATE())) } AS NVARCHAR) + N' gün sonra' AS VisitPeriodDateName, 
                      CAST({ fn MOD(CAST(dbo.CustomerContact.PhoneCallPeriod AS INT), DATEDIFF(day, dbo.CustomerContact.PhoneCallDate, GETDATE())) } AS NVARCHAR)
                       + N' gün sonra' AS PhoneCallPeriodDateName, dbo.Customer.VipCustomer, dbo.Customer.CustomerLock, dbo.CustomerPool.CustomerPool, 
                      dbo.CustomerCategory.CATEGORY, dbo.Customer.Customer
FROM         dbo.Customer INNER JOIN
                      dbo.CustomerContact ON dbo.Customer.CustomerID = dbo.CustomerContact.CustomerID INNER JOIN
                      dbo.CustomerPool ON dbo.Customer.CustomerPoolID = dbo.CustomerPool.CustomerPoolID INNER JOIN
                      dbo.CustomerCategory ON dbo.Customer.CustomerCategoryID = dbo.CustomerCategory.CAT_ID
That script executes fine in SQL Server Management studio, but fails when I run it using TUniQuery. Error I receive is:

Code: Select all

Empty macro or function name.
Same error raised when I try to copy paste that script in TUniQuery SQL editor.

Is there a way to run that script with TUniQuery?

Thanks & regards,
Ertan

Stellar
Devart Team
Posts: 359
Joined: Tue 03 Oct 2017 11:00

Re: Create view problem using TUniQuery

Post by Stellar » Wed 07 Aug 2019 14:16

If you assign False to the global variable EnableUniSQL in UniDAC, the driver won't look for macros in an SQL statement. You can find out more about macros in our docs:
https://www.devart.com/unidac/docs/unisql.htm

For example:

Code: Select all

uses
  Uni;

...

EnableUniSQL := False;

ertank
Posts: 160
Joined: Wed 13 Jan 2016 16:00

Re: Create view problem using TUniQuery

Post by ertank » Wed 07 Aug 2019 14:41

Stellar wrote:
Wed 07 Aug 2019 14:16
If you assign False to the global variable EnableUniSQL in UniDAC, the driver won't look for macros in an SQL statement.
Is this an instant affect global variable? I mean, I have a code where I create database, tables, views. At that point I do not need macros. After all these, I will need macro support again.

Will it be enough to set that variable to True after I'm done all database creation stuff?

Stellar
Devart Team
Posts: 359
Joined: Tue 03 Oct 2017 11:00

Re: Create view problem using TUniQuery

Post by Stellar » Wed 07 Aug 2019 15:27

Yes, you can disable macros parsing for only SQL statements that contain ODBC function calls. For example:

Code: Select all

EnableUniSQL := False;
try
  ...
finally
  EnableUniSQL := True;
end;

Post Reply