Gossamer Forum
Home : Gossamer Threads Inc. : Custom Modification Jobs :

dbman mod: get records and query results from other database

Quote Reply
dbman mod: get records and query results from other database
I have tried to code this myself, but can't get it to work and am not willing to invest any more of my time, so I thought I'd invest some money instead :-)

There's actually two mods I need, both of which I already started thinking through, and both of which involve changing from one database setup to another for searching and printing out results (printing = on screen). Authentication is not an issue, as all database setups I have share the same authentication routines.

1) I need dbman to be capable to retrieve individual fields from a database other than the one one is logged in, and I need this to work in html_record.

Example: User name is stored in user.db, user's publications are stored in publ.db. In every search result from publ, I would like to retrieve the user's first name from user.db. Essentially, this involves calling sub query for user.db from within sub html_record for publ.db, but somehow this just doesn't work.

2) I want to write the results of database queries to static pages.
I have a database of static pages, pages.db, which contains the filename, say file.html. The field to be printed as "content" to file.html is generally empty. With non-query-generated pages, "content" is retrieved from file.html itself, where it is contained in "<div class="content"></div>" tags.

For query-generated pages, the field "content" in pages.db stores the query string, like
http://someserver.com/cgi-bin/dbman/db.cgi?db=bibl&uid=default&ID=*&view_records=1

Now, whenever another of my databases, say publ.db, is modified, dbman should do the following:
- open pages.db and check whether the string "db=publ" is contained in the field "content".
- for each page where that's the case: rewrite the file using the values from the query-string stored in "content".

All I need to do is to get the actual content, i.e. the query.
This involves doing the query and coding the results, using html_record from that *other* database. So here again, a change of database setup values is required. The actual writing to file I have already taken care of.

(I should perhaps add that my dbman files have been quite heavily edited already!)

Hopefully someone will find this an interesting task ...







kellner
Quote Reply
Re: [kellner] dbman mod: get records and query results from other database In reply to
Hela Kellner!

I have code for switching db's from within html_search_succes working, and I think this works in html_record too.
I use this for getting user-data from another db, while searching for records in another.

I can't help you with 2) right now I'm afraid.

Richard.
Quote Reply
Re: [looping] dbman mod: get records and query results from other database In reply to
Hi,

I've already found someone who does the coding for me, but just out of interest, could you post the code you use to switch databases (I know there's a mod by JPDeni for that, but it doesn't work for me)?

Many thanks in advance,
kellner
Quote Reply
Re: [kellner] dbman mod: get records and query results from other database In reply to
I actually use the JP Deni's code but it works for me.
Here is a piece of it from the -html.pl file:

Code:
## Medewerkergegevens halen:

$user = $in{'userid'};

&switch_to_medewerker;
undef %in;

$in{'medewerker'} = $user;

my ($status2,@hits2) = &query("view");

if ($status2 eq "ok") {
my ($numhits2) = ($#hits2+1) / ($#db_cols+1);

for (0 .. $numhits2 - 1) {
%rec_medewerkers = &array_to_hash($_, @hits2);
$unit = $rec_medewerkers{'unit'};

print qq|<DIV CLASS="DomenicaLopendeTekst"><B>Urenopgave voor $rec_medewerkers{'naam_medewerker'} ( $user, Unit $unit )</B></DIV>|;
}

$deeltijdfactor = $rec_medewerkers{'deeltijdfactor'};
$interne_factor = $rec_medewerkers{'interne_factor'};

}

And in db.cgi it says:
Code:
sub switch_to_medewerker {
#-----------------------------------------------------
$cols = 'medewerker,naam_medewerker,volgnr_mw,voornaam,verlofuren_vorig_jaar,verlofuren_dit_jaar,meeruren_vorig_jaar,correctie_meeruren,eerste_maand_declarabel,status,type,unit,datum_in_dienst,datum_uit_dienst,functie,deeltijdfactor,interne_factor';
@db_cols = split /,/,$cols;
$db_file_name = "C:/Path_to_file" . "/Medewerkers.csv";
$db_key_pos = 0;
}

Richard.
Quote Reply
Re: [looping] dbman mod: get records and query results from other database In reply to
Kellner here is the code I have:

Code:
$db_name_list = '../data/data.db'; #name of other db file
open (OTHER, "<$db_name_list") or &cgierr("error!"); #open file or return error if not able
if ($db_use_flock) { flock(OTHER, 1); } #temporarily lock other file
while (<OTHER>) { #begin looping thru other file line by line
next if /^$/; #skip blank lines
next if /^#/; #skip comment lines(?)
chomp; #remove line breaks
@data = &split_decode($_); #use sub to convert break commands and delimiters
if ($data[9] eq "$rec{'UserID'}") { #field position (starts with zero) to match
$UCompany = $data[1]; #field position of data from line w/matching
$UBranch= $data[2]; #field position of data from line w/matching
} #may possibly want to add 'close (OTHER)'
} #at end of this script - double check close syntax
This is what I use to grab some information out of one db and pull it into another.
Quote Reply
Re: [looping] dbman mod: get records and query results from other database In reply to
It's been a while, but I just wanted to thank you for your post, looping.

I had been working on this for a while, already commissioned someone else to code the job (well, luckily, I must say in this case, the person did not actually do it), but your message turned out to be the eye-opener - thanks!
kellner
Quote Reply
Re: [kellner] dbman mod: get records and query results from other database In reply to
Hi Kellner!

Glad that it helped you out!
I am using this again in another db, but I dont like to have the column-names defined in two places (in "db.cfg" and &amp;switch_to_etc). It's a pain to have to edit both when column names change...)

Still wrestling with that though. Maybe you have a suggestion for this?
(I would like to have all the column names defined in the .cfg file and not anywhere else)

Grtx,

Richard
Quote Reply
Re: [looping] dbman mod: get records and query results from other database In reply to
Well, I'm using the following sub to get the database column names:

sub get_db_cols {
my $configfile = shift;
my ($fields, @cols);
open(CFG, "<$configfile") || &amp;cgierr("Cannot open $configfile.\n$!");
while (my $line = <CFG>){
if ($line =~ /^'(\w+)'/) {
push (@cols, $1);}
}
close(CFG);
return (@cols);

}

It's rather primitive, and it assumes that database fieldnames are at the beginning of a line, surrounded by single quotes, *and* that nothing else is. But it does succeed in my case in getting the column names directly from the cfg-file. You call this sub with:

my @fieldnames = &amp;get_db_cols("database.cfg");

I have now, on the basis of your code, written two subs. One that searches another database and returns results formatted according to the html_record of that database, and another that simply returns values in another database for *one* particular record and stores it in %rec2 (that's what JPDeni's code already did anyway). If anyone's interested, I can post it in the customization forum, where - sorry folks - this post would really belong as well.Wink






kellner
Quote Reply
Re: [kellner] dbman mod: get records and query results from other database In reply to
There is an easier (more reliable) way :)

Code:
sub get_cols {
my @cols = ();
for (keys %db_def) { push @cols, $_; }
return @cols;
}

@cols = get_cols();


Last edited by:

PaulW: Dec 18, 2001, 4:04 AM
Quote Reply
Re: [PaulW] dbman mod: get records and query results from other database In reply to
Oh Paul, you've posted this before, but it won't work, as we are here trying to get the column names of *another* database, and not the current one, so %db_def won't hold the keys and values we need.

I suppose, however, this would also work (untested):

sub get_db_cols{
my $configfile = shift;
my %old_db_def = %db_def;
require ("$configfile");
my @cols = ();
for (keys %db_def) { push @cols, $_; }
%db_def = %old_db_def;
return (@cols);
}

It would be more reliable than my first version, as you wouldn't have to be so sticky about the format of the cfg-file, but I wonder whether it would also be faster.




kellner
Quote Reply
Re: [kellner] dbman mod: get records and query results from other database In reply to
I thought I had dejavu :)

Im not 100% sure what happens when you require a second file with a duplicate hash.....I wonder whether the original %db_def would be used or whether %db_def would now become the new included one.

In my post above I did start writing it using require $db; but then for some reason edited to the way you see above.
Quote Reply
Re: [PaulW] dbman mod: get records and query results from other database In reply to
Well, I just tried require-ing the other cfg-file. %db_def turns out like we want it to be, populated with the required cfg-file's keys and values, and *only* with those. @db_cols, however, ends up containing two instances for each value. (Note, though, that I've reworked dbman quite a bit, so I'm importing all variables from the cfg-file in html.pl via "use vars qw (...)"; maybe this affects scoping here.)

If you do this, you would end up with just what we want:

undef (@db_cols);
require("database.cfg");
foreach (@db_cols) { print qq|$_<br>|;}

This has the consequence, perhaps not wanted in all cases, that *all* variables defined in the cfg-file are now reset to those of database.cfg. I guess to set them back to the original database you'd just require "originaldatabase.cfg" after you've done whatever it is you want to do. I'll try running some benchmarks to find out what's quicker.







kellner
Quote Reply
Re: [kellner] dbman mod: get records and query results from other database In reply to
Oo!
I will def. try this as soon as i have this project finished (not going to mess up a working script ...)
I looks like it is *exactly* what I want, so already:
BIG thanks to you Kellner!

Richard.
Quote Reply
Re: [looping] dbman mod: get records and query results from other database In reply to
Just to add this: I ran some benchmark tests on the sub get_db_cols which I posted before and on the other possibility using "require", with the - perhaps not surprising - result that get_db_cols is *way* slower.


kellner