Devart Forums

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.

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

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.

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

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

Hello

You are right, it is useful functionality. We will add a function that will return macros values for current data provider (connection) and we will notify you as soon as it is done.

Hi Bork,

Thank you. I think it will come in useful.

I have to say that UniDAC is a really good product and the support & maintenance you provide are first class.

Excellent work guys!

Regards, Paul.