Hi,
I have defined a macro {SQL_TEMP_CHAR} which evaluates to ## for MS SQL Server and evaluates to TT for PostgreSQL (only because Postgres tables cannot begin with ##. The two definitions are always applied for both providers. This is for use with temp table names where I will add some other characters to name the tables.
How can I determine what SQL_TEMP_CHAR will evaluate to for the current connection? They work when used as part of the SQL but I wanted to get the current value for use in a UniLoader TableName depending on which DB I'm connected to.
UniConnection1.MacroByName('SQL_TEMP_CHAR').Value always evaluates to the first definition (##) regardless if I'm currently connected to a postgres DB.
I need the value TT when connected to pg and ## when connected to sql server.
Thank you, Regards, Paul.
Macro value for current provider
Hi Paul,
just a thought: assuming the macro values are not "hardcoded" at design-time, then during runtime, you'd need to set the macro's actual values somewhere, right?
In that case wouldn't it be possible to make those assignments connection provider-dependent and just use those set variable(s)?
Like this:
a) upon connection set a specific (global) variable to the appropriate value for the database, like
b) later in the code just use the gSQL_TEMP_CHAR variable instead of trying to use "MacroByName('SQL_TEMP_CHAR')".
This is just a thought experiment for me as I haven't yet used macros myself and I'm interested to hear from others how well they work.
Regards,
Tobias
just a thought: assuming the macro values are not "hardcoded" at design-time, then during runtime, you'd need to set the macro's actual values somewhere, right?
In that case wouldn't it be possible to make those assignments connection provider-dependent and just use those set variable(s)?
Like this:
a) upon connection set a specific (global) variable to the appropriate value for the database, like
Code: Select all
if myConnection.ProviderName = 'SQL Server'
then gSQL_TEMP_CHAR := '##'
else gSQL_TEMP_CHAR := 'TT';
myConnection.Macros.Add('SQL_TEMP_CHAR', gSQL_TEMP_CHAR, '');
This is just a thought experiment for me as I haven't yet used macros myself and I'm interested to hear from others how well they work.
Regards,
Tobias
Hi,
Yes that is a possibility though not quite as slick as it could be.
I currently add macros for all providers that I use and normally wouldn't access them outside of the sql statements. It's just that I needed this for the UniLoader.TableName.
The macros work very well in practice and make it very easy to cater for different DB sql dialects.
Regards, Paul.
Yes that is a possibility though not quite as slick as it could be.
I currently add macros for all providers that I use and normally wouldn't access them outside of the sql statements. It's just that I needed this for the UniLoader.TableName.
The macros work very well in practice and make it very easy to cater for different DB sql dialects.
Regards, Paul.
Hello
There are two ways to define macros:
1.
2.
You can find more detailed information about it in the UniDAC help: Using UniDAC -> Unified SQL
There are two ways to define macros:
1.
Code: Select all
if UniConnection.ProviderName = 'Oracle' then
UniConnection.MacroByName('tablename').Value := 'dept'
else
if UniConnection.ProviderName = 'MySql' then
UniConnection.MacroByName('tablename').Value := 'test.dept';Code: Select all
UniConnection.Macros.Add('tablename', 'test.dept', 'MySQL');
UniConnection.Macros.Add('tablename', 'dept', 'Oracle');Hi Bork,
If you are using the first way in your example, you also need to have all the values (e.g. 'dept' or 'test.dept') defined as constants so that you can use them elsewhere in the application.
If you use your second way (which is what I do) then you still need to define all your values as constants to use them elsewhere.
What I thought would be a useful is if you could write something like this:
MyStringVariable := Query1.GetValueForMacro('tablename');
When you're connected to a DB, you could then get back the current definition for use in other components (in my case, to assign the current value to UniLoader.TableName ('dept' when Oracle or 'test.dept' when MySQL).
What I do now in the one case where I need this is:
UniQuery1.SQL.Text := 'SELECT * FROM {'SQL_TEMP_CHAR'};
and then use the last 2 characters of UniQuery1.FinalSQL to get the value (the query is never executed).
This only works because I know it's 2 characters long.
Do you see what I mean?
Regards, Paul.
sayEither way, you have to remember to get those values have second way in your example, you need to
If you are using the first way in your example, you also need to have all the values (e.g. 'dept' or 'test.dept') defined as constants so that you can use them elsewhere in the application.
If you use your second way (which is what I do) then you still need to define all your values as constants to use them elsewhere.
What I thought would be a useful is if you could write something like this:
MyStringVariable := Query1.GetValueForMacro('tablename');
When you're connected to a DB, you could then get back the current definition for use in other components (in my case, to assign the current value to UniLoader.TableName ('dept' when Oracle or 'test.dept' when MySQL).
What I do now in the one case where I need this is:
UniQuery1.SQL.Text := 'SELECT * FROM {'SQL_TEMP_CHAR'};
and then use the last 2 characters of UniQuery1.FinalSQL to get the value (the query is never executed).
This only works because I know it's 2 characters long.
Do you see what I mean?
Regards, Paul.
sayEither way, you have to remember to get those values have second way in your example, you need to