SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
[SA 9.1.276 Pro] - macro variables with text pre/suffix

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
[SA 9.1.276 Pro] - macro variables with text pre/suffix
Author Message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post [SA 9.1.276 Pro] - macro variables with text pre/suffix Reply with quote
Text that is placed next to eg. $COLUMN$ macro will be appended to the expanded value, thus making things like
Code:

DECLARE @$COLUMNS(vertical,types)$

work in SQL Server. The same doesn't work in MySQL which requires each variable to have its own DECLARE keyword, thus it would need the keyword DECLARE itself with an additional space to be attached to the macro. If I recall correctly, that could be achieved by using double quotes like this:
Code:

"DECLARE lv_"$COLUMNS(vertical,types)$;

But this no longer works. Could you check it, please?
Sat Jul 08, 2017 4:49 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7841

Post Reply with quote
Unfortunately I get an exception when executing that kind of code snippet in my environment. I'm sending it to the support team for further investigation.
Sun Jul 09, 2017 7:30 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7841

Post Reply with quote
I believe that regression has been fixed in 9.2. I have found a ticket with similar description.
Fri Jul 14, 2017 10:52 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
This has improved significantly in [SA 9.2.346 Pro], though there's one small side-effect. I've created the following snippet:
Code:

"DECLARE lv_"$COLUMNS(vertical,types)$";"

SELECT
   s.$COLUMNS$
INTO
   lv_$COLUMNS$
FROM $OBJECT(ins_qualname, table, view)$ AS s
WHERE   1 = 1
   AND   s| ;


The code it generates for a selected example table is:
Code:

DECLARE lv_id INT(11);,
DECLARE lv_name VARCHAR(128);,
DECLARE lv_description VARCHAR(128);,
DECLARE lv_area VARCHAR(4096);,
DECLARE lv_attributes VARCHAR(4000);,
DECLARE lv_calendarid INT(11);

SELECT
    s.id
    ,s.name
    ,s.description
    ,s.area
    ,s.attributes
    ,s.calendarid
INTO
    lv_id
    ,lv_name
    ,lv_description
    ,lv_area
    ,lv_attributes
    ,lv_calendarid
FROM
    traccar.geofences AS s
WHERE   1 = 1
    AND s;


I'm almost there. The result has surplus comas at the end of each DECLARE statement. I believe its the inherent/inherited property of the $COLUMN()$ macro to paste a comma after each (before the last) item it has retrieved, except when it has " AND" or " OR" attached as a suffix. Is there a way to prevent those comas being put there?
Sat Jul 15, 2017 4:45 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7841

Post Reply with quote
Quote:
I'm almost there. The result has surplus comas at the end of each DECLARE statement. I believe its the inherent/inherited property of the $COLUMN()$ macro to paste a comma after each (before the last) item it has retrieved, except when it has " AND" or " OR" attached as a suffix. Is there a way to prevent those comas being put there?


You're correct, the commas after column names or their optional suffixes is a feature of $COLUMN()$ macro, they are always added, unless the suffix includes " AND" or " OR". The only solution I know and it's kind of ugly is masking extra commas with a line comments

Code:
"DECLARE lv_"$COLUMNS(vertical,types)$"; --"


or tricking it into thinking that AND is used
Code:
"DECLARE lv_"$COLUMNS(vertical,types)$"; -- and more " no more

Tue Jul 18, 2017 12:26 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
That's some clever hacking, thank you very much.
Tue Jul 18, 2017 4:08 am View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.