Gossamer Forum
Home : Products : DBMan : Customization :

Pulling data form 1 db to another.

(Page 2 of 2)
> >
Quote Reply
Re: [delicia] Pulling data form 1 db to another. In reply to
Thanks delicia, I don't see the attachment.
Quote Reply
Re: [delicia] Pulling data form 1 db to another. In reply to
Hello delicia, I got this error:

Software error:Can't declare subroutine entry in "my" at db.cgi line 491, near ");" BEGIN not safe after errors--compilation aborted at db.cgi line 2275.



sub modify_mult_record {
# --------------------------------------------------------
# This routine will update multiple records at once. It expects
# to find in %in a series of records to update. They will be of the
# form field_name-key.
#
my ($key, %modify_list, %modify_rec, $rec_to_modify, @data, $key,
$errstr, $succstr, $output, %errors);
### new line 20151219 ##########################################################
my (&modify_list2); This is line 491
#########################################################
# First let's pick which records to modify and then separate them and store
Quote Reply
Re: [knue] Pulling data form 1 db to another. In reply to
I'm not 100% sure, but I think:

Code:
my (&modify_list2);

should be:

Code:
my %modify_list2;

and:

Code:
&modify_list2 = &modify_list;

..should be:

Code:
%modify_list2 = &modify_list;

Cheers

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [Andy] Pulling data form 1 db to another. In reply to
Hello andy,

I now get this error:

Error Message : fatal error: Undefined subroutine &main::modify_list called at db.cgi line 507.

sub modify_mult_record {
# --------------------------------------------------------
# This routine will update multiple records at once. It expects
# to find in %in a series of records to update. They will be of the
# form field_name-key.
#
my ($key, %modify_list, %modify_rec, $rec_to_modify, @data, $key,
$errstr, $succstr, $output, %errors);
### new line 20151219 ##########################################################
my %modify_list2; This is line 491
#########################################################
# First let's pick which records to modify and then separate them and store
# them in their own hashes.
$rec_to_modify = 0;
foreach $key (keys %in) { # Build a hash of keys to modify.
if ($in{$key} eq "modify") {
$modify_list{$key} = 1;
$rec_to_modify = 1;
}
($key =~ /^(.*)-(.+)$/) and (${$modify_rec{$2}}{$1} = $in{$key});
}
# Choke if we don't have anything to do.
$rec_to_modify or (&html_modify_failure("no records specified.") and return);

### new line 20151219
%modify_list2 = &modify_list; This is line 507
####

thank you,

Ed
Quote Reply
Re: [knue] Pulling data form 1 db to another. In reply to
Ah ok - delicia will need to help you on that bit :) I assumed that was an already existing function

Cheers

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [Andy] Pulling data form 1 db to another. In reply to
ok. line 491 should be % instead of &

my (%modify_list2); # creating a new variable for this sub

then my original line 507 should be ok:

%modify_list2 = %modify_list; # all this is doing is saving a copy of %modify_list to use with db2
Quote Reply
Re: [delicia] Pulling data form 1 db to another. In reply to
Hello delicia, I made the changes and they work! Now when I make multiple changes in DB1 the same changes are made in DB2.
I can't thank you enough for all of your help without it I would be lost.

So then I started thinking (this is where I usually get my self in trouble :-) if i apply this in the opposite direction I could make it work for changes going from DB2 to DB1. As far as I can tell it works. This is what I did:

This code will modify multiple records in master (db2) and make the same changes in hercules (db1).
But will only write the record to hercules if the record is already exist.

I remove "sub after_add_record {", and changed any ref too "after_modify_record{".

Then these line below in red. If i'm reading this correctly this is what tells the script to write to DB1 only if the record already exist. If it dose than the changes are made to both DB, if not then it makes the changes to DB2.

******************************************************************************************
Config File
******************************************************************************************

# Full path to db2 databace.
$db_after_add_db = $db_script_path . "/../db/hercules.db"; # This is the name of the 2nd DB.
# Call for modify to hercules.
$db_after_modify_record = 1;



******************************************************************************************
db.cgi
******************************************************************************************

sub modify_mult_record {
# --------------------------------------------------------
# This routine will update multiple records at once. It expects
# to find in %in a series of records to update. They will be of the
# form field_name-key.
#
my ($key, %modify_list, %modify_rec, $rec_to_modify, @data, $key,
$errstr, $succstr, $output, %errors);
### new line 20151219 ##########################################################
my (%modify_list2);
#########################################################
# First let's pick which records to modify and then separate them and store
# them in their own hashes.
$rec_to_modify = 0;
foreach $key (keys %in) { # Build a hash of keys to modify.
if ($in{$key} eq "modify") {
$modify_list{$key} = 1;
$rec_to_modify = 1;
}
($key =~ /^(.*)-(.+)$/) and (${$modify_rec{$2}}{$1} = $in{$key});
}
# Choke if we don't have anything to do.
$rec_to_modify or (&html_modify_failure("no records specified.") and return);

### new line 20151219
%modify_list2 = %modify_list;
####
open (DB, "<$db_file_name") or &cgierr("error in modify_records. unable to open db file: $db_file_name.\nReason: $!");
if ($db_use_flock) { flock(DB, 1); }
LINE: while (<DB>) {
(/^#/) and ($output .= $_ and next LINE);
(/^\s*$/) and next LINE;
chomp;
@data = &split_decode($_);
$key = $data[$db_key_pos];

# Now we check if this record is something we want to modify. If so, then
# we make sure the new record is ok, if so we replace it.
if ($modify_list{$key}) {
$status = &validate_record(%{$modify_rec{$key}});
if ($status eq "ok") {
$output .= &join_encode(%{$modify_rec{$key}});
$modify_list{$key} = 0;
}
else {
$errors{$key} = $status;
$output .= "$_\n";
}
}
else {
$output .= "$_\n";
}
}
close DB;

# Reprint out the database.
open (DB, ">$db_file_name") or &cgierr("error in modify_records. unable to open db file: $db_file_name.\nReason: $!");
if ($db_use_flock) {
flock(DB, 2) or &cgierr("unable to get exclusive lock on $db_file_name.\nReason: $!");
}
print DB $output;
close DB; # automatically removes file lock

# Let's display an error message if we were unable to modify a record
# for some reason.
foreach $key (keys %modify_list) {
if ($modify_list{$key}) {
($errors{$key}) ?
($errstr .= "$key: $errors{$key}") :
($errstr .= "$key: not found");
}
else {
$succstr .= qq~<a href="$db_script_link_url&view_records=1&$db_key=$key&ww=1">$key</a>,~;
}
}
chop($succstr); # Remove trailing delimeter
########## new lines 20151219 #############################################################
if ($db_after_modify_record) {
my ($db2_file_name) = "$db_after_add_db"; ## i don't understand this but if it works for adding
## it should work here
my ($output2);
open (DB, "<$db2_file_name") or &cgierr("error in modify_records. unable to open db file: $db2_file_name.\nReason: $!");
if ($db_use_flock) { flock(DB, 1); }
LINE: while (<DB>) {
(/^#/) and ($output2 .= $_ and next LINE);
(/^\s*$/) and next LINE;
chomp;
@data = &split_decode($_);
$key = $data[$db_key_pos]; ##### 20151219 change this if key pos not same in db and db2!!!

# Now we check if this record is something we want to modify. If so, then
# we make sure the new record is ok, if so we replace it.
if ($modify_list2{$key}) {
# not validating records in db2
$output2 .= &join_encode(%{$modify_rec{$key}});
$modify_list2{$key} = 1; # I changed this from 0 to 1, thinking that this will only write to hercules if the record is already exist.
}
else {
$output2 .= "$_\n";
}
}
close DB;

# Reprint out the database.
open (DB, ">$db2_file_name") or &cgierr("error in modify_records. unable to open db file: $db2_file_name.\nReason: $!");
if ($db_use_flock) {
flock(DB, 2) or &cgierr("unable to get exclusive lock on $db2_file_name.\nReason: $!");
}
print DB $output2;
close DB; # automatically removes file lock

}

&html_modify_mult_results($succstr, $errstr);
}

******************************************************************************************
html.pl file
******************************************************************************************

sub after_modify_record {

# first get the new record
%rec = &get_record($in{$db_key});

my ($db2_file_name) = "$db_after_add_db";


#make any changes you want to the rec2, especially if record layout is not identical

#next, slurp db2
open (DBX, "<$db2_file_name") or &cgierr("error in modify_records. unable to open db file: $db2_file_name.\nReason: $!");
if ($db_use_flock) { flock(DBX, 1); }
@lines = <DBX>; # Slurp the database into @lines..
close DBX;
# now look for matching record
$found = 1; # Make sure the record is in here! If it is then make the changes, if not don't add the record.
# I changed this from 0 to 1, thinking that this will only write to hercules if the record is already exist.

LINE: foreach $line (@lines) {
if ($line =~ /^$/) { next LINE; } # Skip and Remove blank lines
if ($line =~ /^#/) { $output .= $line; next LINE; } # Comment Line
chomp ($line);
@data = &split_decode($line);
# put your key position below
if ($data[$db2_key_pos] eq $in{$db_key}) {

%rec2 = %rec;
$output .= &join_encode(%rec2);
$found = 1;
}
else {
$output .= $line . "\n"; # else print regular line.
}
}

if (!$found) {
# if the record was not found, you might want to just add it here
$output .= &join_encode(%rec2);
}

open (DBX, ">$db2_file_name") or &cgierr("error in modify_records. unable to open db file: $db2_file_name.\nReason: $!");
if ($db_use_flock) {
flock(DBX, 2) or &cgierr("unable to get exclusive lock on $db2_file_name.\nReason: $!");
}
print DBX $output;
close DBX; # automatically removes file lock

}

END of SCRIPT.


What i'm working on now, only the fields that are displayed in the "master" form copies over to DB1. I thought that I needed to make the other fields (that are in the DB and config) make them hidden in the master form and they would move over at the same time, but it dose not work. This is how I place them:

The ones in red are the ones I tried it with first.

my (%rec) = @_;
$rec{$db_key} =~ s/<?.B>//g;
($db_auto_generate and print &build_html_record_form(%rec) and return);
my $font = 'Font face="Tahoma" Size=2 Color=#003399';
print qq|

<center>
<input type=hidden NAME="ID2" VALUE="$rec{'ID2'}" MAXLENGTH="5">
<input type=hidden NAME="Date" VALUE="$rec{'Date'}" MAXLENGTH="12">
<input type=hidden NAME="AD_Size" VALUE="$rec{'AD_Size'}" MAXLENGTH="12">
<input type=hidden NAME="Art" VALUE="$rec{'Art'}" MAXLENGTH="12">
<table width="1000" border="0" cellspacing="0" cellpadding="3" bgcolor="#E0E0D6">
<tr>
<td colspan=3 bgcolor="#8A148A"></td><td colspan=2 bgcolor="#8A148A"><font color="#ffffff">Master Data Base</font></td><td colspan=3 bgcolor="#8A148A"></td>
</tr>
<tr>
<td>|; print &build_select_field ("Promotion", "$rec{'Promotion'}"); print qq|</td>
<td>|; print &build_select_field ("Pdate", "$rec{'Pdate'}"); print qq|</td>
<td>|; print &build_select_field ("Dino_Pro", "$rec{'Dino_Pro'}"); print qq|</td>
<td colspan="2">Week:<input type="text" name="Week" VALUE="$rec{'Week'}" size="3"></td>
<td></td>


Thank you again,

Ed-
Quote Reply
Re: [knue] Pulling data form 1 db to another. In reply to
sorry but i'm confused!!! if you make change in db2, do you want record added to db1 if it doesn't already exist?

$modify_list2{$key} = 1; # I changed this from 0 to 1, thinking that this will only write to hercules if the record is already exist.

the above line is used to generate your success or error string so you should leave it at 0.

$found = 1; # Make sure the record is in here! If it is then make the changes, if not don't add the record.
# I changed this from 0 to 1, thinking that this will only write to hercules if the record is already exist.

your change defeats the purpose of $found and should be changed back to 0. if you don't want to add the record to second db, just comment the following lines, which add the record to the other db:

if (!$found) {
# if the record was not found, you might want to just add it here
$output .= &join_encode(%rec2);
}


re the fields on the form: if you have a form that doesn't have all the fields, it will probably erase the contents of the field in the db in which you're working. then when you copy the record to the other db, those fields don't have any content and will be empty. any time you have a form, it should have ALL the fields for the db you're working on. so make them hidden if you don't want to see them or change them. the routine that copies from one db to the other is not looking at the form; it's simply copying the record as it exists.
Quote Reply
Re: [delicia] Pulling data form 1 db to another. In reply to
Question: "sorry but i'm confused!!! if you make change in db2, do you want record added to db1 if it doesn't already exist? ".



Answer : No. if the record I change in db2 is not in db1 I do not want to add it to db1, just make the change in db2. But if the record is in db1 I then want to make the change in both db's.


Ok so I change both lines back to "0" and comment out these lines:


if (!$found) {
# if the record was not found, you might want to just add it here
$output .= &join_encode(%rec2);
}

From what I can tell it's working.

I'm adding the rest of the fields in the form as "hidden", I'll let you know the out come of that.


Again thank you,


Ed-


Quote Reply
Re: [delicia] Pulling data form 1 db to another. In reply to
My DB has all the information in it. My form has the fields that I want to display and the hidden feels I don't want to display. When the record get copy to the other DB only the fields that are displayed get copy the hidden fields get erased. I attached my "html_record_form" could you please take a look and tell me what I missed?

Thank you,
Ed
Post deleted by delicia In reply to
Quote Reply
Re: [knue] Pulling data form 1 db to another. In reply to
i think i found the problem! in sub after_modify...

$output2 .= &join_encode(%{$modify_rec{$key}});

should be

$output2 .= &join_encode(%{$modify_rec2{$key}});
Quote Reply
Re: [delicia] Pulling data form 1 db to another. In reply to
I don't have this line "$output2 .= &join_encode(%{$modify_rec{$key}}); ".

is it one of the green line?


sub after_modify_record {
#---------------------------------------------
# update db2

# first get the new record
%rec = &get_record($in{$db_key});
my (%rec2) = %rec;
my ($db2_file_name) = "$db_after_modify_db";


#make any changes you want to the rec2, especially if record layout is not identical

#next, slurp db2
open (DBX, "<$db2_file_name") or &cgierr("error in modify_records. unable to open db file: $db2_file_name.\nReason: $!");
if ($db_use_flock) { flock(DBX, 1); }
@lines = <DBX>; # Slurp the database into @lines..
close DBX;
# now look for matching record
$found = 0; # Make sure the record is in here!
LINE: foreach $line (@lines) {
if ($line =~ /^$/) { next LINE; } # Skip and Remove blank lines
if ($line =~ /^#/) { $output .= $line; next LINE; } # Comment Line
chomp ($line);
@data = &split_decode($line);
# put your key position below
if ($data[$db2_key_pos] eq $in{$db_key}) {

%rec2 = %rec;
$output .= &join_encode(%rec2);
$found = 1;
}
else {
$output .= $line . "\n"; # else print regular line.
}
}

# if (!$found) {
# if the record was not found, you might want to just add it here
# $output .= &join_encode(%rec2);
# }

open (DBX, ">$db2_file_name") or &cgierr("error in modify_records. unable to open db file: $db2_file_name.\nReason: $!");
if ($db_use_flock) {
flock(DBX, 2) or &cgierr("unable to get exclusive lock on $db2_file_name.\nReason: $!");
}
print DBX $output;
close DBX; # automatically removes file lock

}
Quote Reply
Re: [delicia] Pulling data form 1 db to another. In reply to
I do have this line in my db.cgi "sub modify_mult_record {"
Quote Reply
Re: [knue] Pulling data form 1 db to another. In reply to
sorry it's in sub modify_mult_record
Quote Reply
Re: [delicia] Pulling data form 1 db to another. In reply to
Sorry no luck, none of the fields copy over. the record is blank.

Last edited by:

knue: Dec 22, 2015, 1:51 PM
Quote Reply
Re: [knue] Pulling data form 1 db to another. In reply to
ok, i'm getting confused because i handled modify mult in a different way than modify single record. i'm getting confused because we started copying db1 change to db2 and now are copying db2 change to db1 IF the record is in db1, right? is single record working ok in both directions? please let me know and show me code for both single and mult.
Quote Reply
Re: [delicia] Pulling data form 1 db to another. In reply to
You are correct. Single records work in both directions. The muti modify works going from db1 to db2. Muti modify works going from db2 to db1 with the exception of the hidden fields they are blank.

Last edited by:

knue: Dec 22, 2015, 2:13 PM
Quote Reply
Re: [knue] Pulling data form 1 db to another. In reply to
i don't see anything wrong but i'm not a perl expert. in the following code i don't know what

(${$modify_rec{$2}}{$1} = $in{$key});

is doing. i don't know if that's something we need to be saving as

(${$modify_rec2{$2}}{$1} = $in{$key});

to use later.

Code:
foreach $key (keys %in) { # Build a hash of keys to modify.
if ($in{$key} eq "modify") {
$modify_list{$key} = 1;
$rec_to_modify = 1;
}
($key =~ /^(.*)-(.+)$/) and (${$modify_rec{$2}}{$1} = $in{$key});
}

i'll ask andy to look at it.
Quote Reply
Re: [delicia] Pulling data form 1 db to another. In reply to
Hey delicia, you are a perl expert! There was nothing wrong with your code, it was on my end. I was putting the hidden code in the

"sub html_record_form {"

but I forgot that I also have a:

"sub html_record_form_mult {"

I made this for a custom look instead of the auto generated form. After putting the hidden code and adding the "-$rec{$db_key}" it worked!


<input type=hidden NAME="AD_Size-$rec{$db_key}" VALUE="$rec{'AD_Size'}" MAXLENGTH="12">

Now I'll start testing everything and make sure I didn't miss something.

Ed-
> >