Check if field exists in table

[Raptorke]

Is there a way to check within preprocessort &IF if a field exists in a table.
We need this to be able to compile 1 source into 3 different environments with 3 databases (overall the same but here and there some slight differences).

Thanx

Roel

 

[bendaluz2]

Wouldnt it be easier just to add the field to the database it doesnt exist in?
Then your program would compile without any problems.

Originally posted by Raptorke
Is there a way to check within preprocessort &IF if a field exists in a table.
We need this to be able to compile 1 source into 3 different environments with 3 databases (overall the same but here and there some slight differences).

Thanx

Roel

 

[Norman Biggar]

Not sure as to what level you need to work to, but you could use code similar to that below IF your PROPATH is different when performing the different compiles (easily manipulated) and you knew where the differences between the systems were.

&IF INDEX(PROPATH,"LiveEnv" ) NE 0 &THEN
MESSAGE "Yes".
&ELSEIF INDEX(PROPATH,"TestEnv" ) NE 0 &THEN
MESSAGE "Maybe"
&ELSE
MESSAGE "No".
&ENDIF.

Progress supplies a list of functions you can use in Pre-Processors
ABS LEFT-TRIM R-INDEX ASC LENGTH RANDOM DATE LIBRARY REPLACE DAY LOG RIGHT-TRIM
DECIMAL LOOKUP ROUND ENCODE MATCHES SQRT
ENTRY MAXIMUM STRING ETIME MEMBER SUBSTITUTE
EXP MINIMUM SUBSTRING FILL MODULO TIME
INDEX MONTH TODAY INTEGER NUM-ENTRIES TRIM
KEYWORD OPSYS TRUNCATE KEYWORDALL PROPATH WEEKDAY
LC PROVERSION YEAR

 

[Raptorke]

No that's not what I'm looking for.

We have a company with 3 sites. Every site uses the same software, BUT sometimes there are differences. We can't update the databases just like that because this takes weeks of planning and training (for the new fields that will be added). We now solve this problem by placing the code that won't compile (because the fields don't exist) in comment en so we have a source PER site...But I want to make use of a function that tests if a field exists in a table so the compiler is allways able to compile the code...and we only have 1 source for 3 sites...


Thanx!

Roel

 

[michaelcarroll]

I don't think that there's a way that you can test for a DB connection but you can still use pre-processors to get a similar effect. For example, you could define a pre-processor in an include file that is included in ALL procedures.

To perform a compile for a specific database, you would need to have multiple versions of the include file and modify the PROPATH. It's not ideal, but it's as probably as close as you can get.

e.g. in your include file you could define:

&GLOBAL-DEFINE DB1

and in your code, you would say:

&IF DEFINED(DB1) &THEN
<do stuff for DB1>
&ENDIF

&IF DEFINED(DB2) &THEN
<do stuff for DB2>
&ENDIF

What you should be able to do, is have three different versions of the include file placed in three different directories, each defining a different pre-processor, but all with the same file-name. You can then use three different session start-ups which set the PROPATH to point to the appropriate include file.

One of the easiest ways to do this is to use the -param start-up parameter, and use the start-up procedure to modify the PROPATH depending on the value of the SESSIONARAMETER.

 

[Norman Biggar]

If you're going to have different entries in your PROPATH, you might as well make direct use of this.

If you change your code to e.g.

ASSIGN Table.Field1 = cField1
Table.Field2 = cField2
&IF INDEX(PROPATH,"Env1" ) NE 0 &THEN
Table.Field3 = cField3
&ENDIF
Table.Field4 = cField4.

there will be no attempt to compile the line Table.Field3 = cField3
unless the word "Env1" exists in your PROPATH.

 

[bendaluz2]

If you are planning this to consolidate the source into 1 set rather than 3. Would it not make sense to standardise the databases at the same time? Then, you can control what code runs on each site by use of a system flag of some kind, either an environment variable or flag in a control table. This would mean that all site specific code is determined at run time rather than compile time - which means you can turn functionality on and off without re-compiling.

e.g. site 1 suddenly has a new requirement that site 2 already uses. Rather than having to go through all your code, change the pre-processor statements to include the bits you now need for site 1, recompile and deploy to the customer...etc..etc..
You would simply connect to their database and change a flag.

Believe me, I know from experience, my way is much less painful in the long run.

 

[Raptorke]

You got a point there. We allready use this system of functionality with a table Parameters where we control all our functionality.

But this is not going to solve my problem with fields that exist in one databse but still need to be updated in another db... The source just wont compile then from your point of view.

 

[vinod_home]

I guess you need to comment out code based on the sites using the &IF &ENDIF and not based on fields. If you would know what exactly are the fields you want to comment you could always split the code into different versions on different propaths for different sites. Which I guess would be an easier.

 

[Raptorke]

Originally posted by vinod_home
I guess you need to comment out code based on the sites using the &IF &ENDIF and not based on fields. If you would know what exactly are the fields you want to comment you could always split the code into different versions on different propaths for different sites. Which I guess would be an easier.

That's exactly how we do it now, one source copied 3 times and modified PER site... But I'm looking for a function that checks if a field exists in a table.
example:

ASSIGN Customer.CustNum = 500
&IF FIELDEXISTS(Customer.Phone) &THEN
Customer.Phone = 02314342434
&ENDIF
.

In this case we would only have 1 source for 3 different sites... Sooner or later these missing fields will be added to the databases but other fields will keep coming and we can't update the 3 databases at the same time...this takes months to complete...

 

[toby.Harman]

1. Force them ALL to the same version of database.

2. Implement a CRC check on the database tables. You would have to get a master database which has all the schema changes applied in the right way. I'm not sure this will help compilations but it may help with management!

3. Get the databases into sync again!

Good luck!

 

[vinod_home]

then you can try this. in a program that you call first when you start the system scope define pre-processors for the fields that you need and use those in these programs. You can define those pre-processors by finding the fields you want and from the tables you want from the _file and _field tables.

HTH

Originally posted by Raptorke
That's exactly how we do it now, one source copied 3 times and modified PER site... But I'm looking for a function that checks if a field exists in a table.
example:

ASSIGN Customer.CustNum = 500
&IF FIELDEXISTS(Customer.Phone) &THEN
Customer.Phone = 02314342434
&ENDIF
.

In this case we would only have 1 source for 3 different sites... Sooner or later these missing fields will be added to the databases but other fields will keep coming and we can't update the 3 databases at the same time...this takes months to complete...

 

[bendaluz2]

I still dont see why you dont just add all the missing fields to the db's that dont have them. It isnt a crime to have unused fields in a database.

Seems to me that a little extra work now to do this would save you a vast amount of work going forward.

Originally posted by Raptorke
You got a point there. We allready use this system of functionality with a table Parameters where we control all our functionality.

But this is not going to solve my problem with fields that exist in one databse but still need to be updated in another db... The source just wont compile then from your point of view.