How to display the database name in a report 1

[JustineB]

I need to display the database name on a report. This is not a field in the "Special Fields" and I was wondering whether anyone has acheived this.
I am using 8.5 connecting via ODBC to a SQL Server Database (this will also be needed for Oracle and Sybase)
I am under the impression that it is impossible, but if anyone has written a UFL for this, please can I "borrow" your code?
Many thanks in advance.

Justine

 

[FoxG]

Hi Justine,

If nobody has a better idea you can simply cheat to get round this!

File..
Summary Info...

Just put the following into the Report summary comment box
DB=YourDB_name;

Then just create a simple formula
Formula DbName:-
BeforeReadingRecords;
local numbervar p1;
local numbervar p2;
p1 := instr(Reportcomments,"DB=&quot+3;
if p1 < 4 then &quot;&quot;
else (p2 := instr(p1,Reportcomments,&quot;;&quot;
mid(Reportcomments,p1,p2-p1))


This will not cater for changes to the database name but will always tell you which database you should be using!!

Hope this helps,

Geoff

PS. They could be other similar things that you could use this approach for!

 

[JustineB]

Thanks for the reply!
I really need soething to link to the ODBC datasource to find the name of the database being used as it will change from person to person, but your idea will be useful for other things!

 

[FoxG]

Hi Justine,

There is a similar thing you can do, but that involves tweaking the record selection criteria.

What you can do is add a dummy first line to the formula...

local numbervar DB:={sample.code};

You obviously need to select a field from your own table.
Then the formula for determining the DB name is ...

beforereadingrecords;
local numbervar p1;
local numbervar p2;
p1 := instr(RecordSelection,&quot;DB:={&quot;
if p1 < 1 then &quot;DB not defined&quot;
else
(p1 := p1 + 5;
p2 := instr(p1,RecordSelection,&quot;.&quot;
mid(RecordSelection,p1,p2-p1-1))

The +5 skips to the start of the DB name within the Field

PS. I actually enjoy these sort of things
I must have a warped mind!

Geoff

 

[JustineB]

Hi Geoff - thanks for the reply.

It looks just what I nedd, but I have a slight problem. When I try and add the dummy line to the Record Selection formula - I get the message:
&quot;The Result of the Selection Formula must be a boolean&quot;
What am I doing wrong?

Many thanks!
Justine

ps - I too enjoy things like this, I don't think you're warped!

 

[FoxG]

Hi Justine,

You need to have an end result from the record selection.

If you are not currently using one then just add a second line...

True

Geoff

 

[JustineB]

Hello - sorry to bother you again!

I have the formula and the records selection in place, but the formula is only returning the table name. Is this because I am connecting via ODBC?

Once again, thanks for all your help!

Justine.

 

[FoxG]

Ooops...

Didn't notice that!

Back to the drawing board (or my first suggestion)

Geoff

 

[vinorosso]

Insert a subreport in the report header based on:

select distinct table_catalog
from information_schema.columns Larry Storm
LTD dba

 

[Ngolem]

If this is a general report linking to several different databases you might:

1. Make it a parameter that is passed to the report (if it is known to the user)
2. hardcode a lookup array within the report and have the user add his/her name as a parameter within the report. Using this name you could get the info you require.

Stringvar array userinfo;

eg. userinfo[1] = &quot;usernameuserdatabase.....&quot;

username is 1st 8 chars
userdatabase is next 12 chars

something like that...note 254 char max /array item

it requires maintenance though...and may be a drawback for you