Gossamer Forum
Home : Products : DBMan : Customization :

passing data from 1 db to another.

Quote Reply
passing data from 1 db to another.
Hello,

I have 2 data bases, db1 and db2. both have fields name ID, Userid, Name and city. they also have other fields
named other things. i also have 2 different dbman's running, one with db1 and the other with db2. what i would like to do
is when i have the dbman with db1 open to a record i have a link going to the other db(2) add routine. what i would like to
do is pass the ID, Userid, Name and city and populate the add record in the 2nd db. any ideals? thank you.
Quote Reply
Re: [knue] passing data from 1 db to another. In reply to
I'm not an expert with DBMan (very rarely used it), but I'm not sure how possible/easy that would be. I guess it really depends on how good your Perl programming is :)

The tricky issue I can see, if overlapping IDs (as they would need to be unique)

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] passing data from 1 db to another. In reply to
Hi Andy,
I did this about 14 years ago with the help of JP, we linked 5 db together passing data from one to the other but i can't fine the old post or remember how we did it. i'm thinking we moved the information by the URL but not sure.

ed
Quote Reply
Re: [knue] passing data from 1 db to another. In reply to
Ah ok - afraid I've very rarely used DBMan (pretty much everything, except) , so can't give you much more advice than that Whistle

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] passing data from 1 db to another. In reply to
i've hacked dbman extensively and what you want (if i understand you correctly) shouldn't be a problem at all. on the page where you're displaying the record in db1, have a link that is to $db_script_url?db=db2.cfg&uid=whoever&add_form=1&field1=$rec{'field1'}&field2=$rec{field2) etc

obviously change the field1, field2 etc to the actual names of your fields. the uid could be the $db_userid of the person logged in and clicking the link, or it could be the userid stored in the current record.

hope this helps!
Quote Reply
Re: [delicia] passing data from 1 db to another. In reply to
Hello Delicia,

Thank you for responding. I did what I thing you suggested, The information string is carried over to the other dbman but the information did not copy to the "add" page.

This is what I added.

<td colspan="2"><center><a href="http://website.com/cgi-bin/toby/db.cgi?db=default&uid=$in{'uid'}&add_form=1&Company=$rec{'Company'}&Contact=$rec{'Contact'}&sb=1" target="_blank"><img src="/../images/jersey.png" style="width:75px;"></a></center></td>

Any ideals?

Thank you,
Ed-
Quote Reply
Re: [knue] passing data from 1 db to another. In reply to
does it pull up the add form ok? if so, fill out the form manually and see what happens so we can be sure the basics are transferring ok. the only thing i see that doesn't look quite right is

uid=$in{'uid'}

but if you're on the page where the complete record is displayed when you click your link then it should be pulling the info from the current record. are the fieldnames the same in both databases? if not, it's ok, just be sure to change it as needed. example: db1 has fieldname Contact that you want to copy to fieldname Name in db2 -- you would just use & Name=$rec{'Contact'}
Quote Reply
Re: [delicia] passing data from 1 db to another. In reply to
Delicia,
From db1 when i'm the modify form this a record loaded, i click my link to go to db2, the add form opens but none of the information from db1 shows up in the add fields (exp: the Company Name) but if you look at the link at the top of the browser it shows all of the information that came over from db1, its just not putting it in the add form.

this is the string that is passed.

http://adcxyz.com/cgi-bin/toby/db.cgi?db=default&uid=admin.144047271667631&add_form=1&Company=Webco%20Printing%20&%20Graphics&sb=1

This part "&Company=Webco%20Printing%20&%20Graphics" is what i want to copy to the Company field in db2.

Ed-
Quote Reply
Re: [knue] passing data from 1 db to another. In reply to
Ok this is how you pass the data from one data base to another.

in the main db you want to put the link in the "sub html_record", if you put it in the "sub html_record_form" any data that is not saved
will not pass.

Next the URL string: My string looks like this,

<td colspan="2"><center><a href="http://website.com/cgi-bin/toby/db.cgi?db=default&uid=$in{'uid'}&add_form=1&Company=$rec{'Company'}&Contact=$rec{'Contact'}&sb=1" target="_blank"><img src="/../images/jersey.png" style="width:75px;"></a></center></td>

"toby" is the name of the dir where the 2nd db is that i want to pass the information to.
"db=default" is the cfg file in toby that points to the 2nd db.
"&uid=$in{'uid'}"is your login information (I use the same pass file and auth as I do in the db1. I'll show how I did that when we get to the cfg file.)

"&add_form=1" this is the form where the data will be placed.

Any field from the 1st. db that you want to pass should look like this. &Company=$rec{'Company'} you can add as many of them as you like.

That is it for the db1.

in DB2 (toby) we need to make some changes in the cfg file.

"main" is the dir where the main db (db1) is placed.
"tobydb.db" is in this dir.

As you can see I point db2 "auth and pass" to db1 so you can move from one db to the other without having to login again.


# File and URL's
# --------------------------------------------------------
# URL of the directory dbman resides in. No Trailing Slash Please.
$db_dir_url = "http://website.com/cgi-bin/toby";
# URL of dbman.
$db_script_url = $db_dir_url . "/db.cgi";
# Full Path and File name of the database file.
$db_file_name = $db_script_path . "./main/tobydb.db";
# Full path and file name of the counter file.
$db_id_file_name = $db_script_path . "/default.count";
# Full path and file name of the authorization directory.
$auth_dir = $db_script_path . "./main/auth";
# Full path and file name of the password file.
$auth_pw_file = $db_script_path . "./main/default.pass";
# Full path and file name of the log file.
$auth_log_file = $db_script_path . "/default.log";
# Full path and file name of the html routines.
require $db_script_path . "/html.pl";


That is it for the cfg file.

Now for the string to get the data into the rec.

http://website.com/cgi-bin/toby/db.cgi?db=default&uid=admin.144077486113967&add_form=1&ID2=4374&Userid=edknue&Company=Webco%20Printing&Contact=Ed%20Knue&sb=1

dbman will covert to the data from db1 to db2 and it will look like this.
At this point you should be at the "add rec form".
This form has to have a field for each of the fields in your link. you can not use the "auto generate".

This is what part of mine looks like.

you will notices that the fields in my form has "$in{'Company'}" the "$in" is what the string is looking for to place the information in the field.
you can all so have other fields if you want to add additional information to the same rec. here we use "$rec{'Address'}" the "$rec" is what we use to write to the db.


sub html_record_form {
# --------------------------------------------------------
# The form fields that will be displayed each time a record is
# edited (including searching). You don't want to put the
# <FORM> and </FORM tags, merely the <INPUT> tags for each field.
# The values to be displayed are in %rec and should be incorporated
# into your form. You can use &build_select_field, &build_checkbox_field
# and &build_radio_field to generate the respective input boxes. Text and
# Textarea inputs can be inserted as is. If you turn on form auto
# generation, the program will build the forms for you (all though they may
# not be as nice). See the README for more info.

my (%rec) = @_;
($db_auto_generate and print &build_html_record_form(%rec) and return);
my $font = 'Font face="Verdana, Arial, Helvetica" Size=2 Color=#003399';

print qq|
<center><table style="width: 600px; height: 20px;" border="1" cellpadding="3">
<table border="0">
<tr>
<td>Invoice:</td>
<td><input name="ID2" value="$in{'ID2'}"></td>
</tr>
<tr>
<td>Userid:</td>
<td><input name="Userid" value="$in{'Userid'}"></td>
</tr>
<tr>
<td>Company:</td>
<td><input NAME="Company" VALUE="$in{'Company'}" size="40"></td>
</tr>
<tr>
<td>Contact:</td>
<td><input name="Contact" value="$in{'Contact'}" size="40"></td>
</tr>
<tr>
<td>Address:</td>
<td><input name="Address" value="$rec{'Address'}" size="40"></td>
</tr>
<tr>
<td>CSZ:</td>
<td><input name="CSZ" value="$rec{'CSZ'}" size="40"></td>
</tr>
<tr>
<td>Phone:</td>
<td><input name="Phone" value="$rec{'Phone'}"></td>
</tr>
<tr>
<td>Email:</td>
<td><input name="Email" value="$rec{'Email'}" size="40"></td>
</tr>
</table>
</table>
</center>
|;
}


That's it. i hope this helps others who need to pass information from one db to another.

Ed-

ps. Delicia, Thank you for your help.
Quote Reply
Re: [knue] passing data from 1 db to another. In reply to
oops. i forgot about the part in second db but i think i have an easier way. instead of changing the input value from $rec to $in, i have a conditional statement at top of record_form in db2 AFTER %rec = @_;

if ($in{'add_form'}) {
$rec{'Company'} = $in{'Company'};
etc
}

or you could do this to test if there are any values coming in
if ($in{'Company'}) {
$rec{'Company'} = $in{'Company'};
etc
}

if you put one of the above before the autogenerate line, i think you can even use autogenerate. (can't remember if i've done that.)

this is all that was missing from original attempt because you were already getting to the form. all my databases are in same folder so that's why i can use the script_link_url.

anyway, glad you got it working. but be sure you can modify the record using your coding! i know mine works setting $rec = $in

Last edited by:

delicia: Aug 29, 2015, 8:43 AM
Quote Reply
Re: [delicia] passing data from 1 db to another. In reply to
Delicia, Have you ever come across this problem when passing data? In the company name that you are passing there is a "&" in the name. When you receive the data on the other end the name stops at the "&". I know it because perl uses it but is there a way around it so the hole name is passed?

Thanks,
Ed-
Quote Reply
Re: [knue] passing data from 1 db to another. In reply to
i haven't encountered the situation but suggest you try this:

my ($company) = $rec{'Company'}; # or whatever the field name is
$company =~ s/&/&amp;/g; # this converts & to &amp;

then in the line that passes the value, pass $company instead of $rec{'Company'}

let me know if that works!
Quote Reply
Re: [delicia] passing data from 1 db to another. In reply to
the reason the rest of company name isn't being passed is because & indicates new info like another field=whatever. so now i'm thinking you need to convert the & to something else that would never be in a company name, maybe a string of zzzzz or something. then when you get to the other database convert it back to &. is it dealing with spaces in the company name ok?

if the suggestion above doesn't work, you could just pass the record id to db2. then in db2, open db1 and find the record and get the info you want. the relational db tips might help you.
Quote Reply
Re: [delicia] passing data from 1 db to another. In reply to
Or, if it has GT::CGI on it, you could do:L

Code:
GT::CGI->escape($string)

...and this would then convert & into a correct URL encoded value, along with stuff like " and other characters that would give you issues :)

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: [delicia] passing data from 1 db to another. In reply to
delicia, I'm still trying to send data from db to another db through a url. Passing the data works just fine. This part deals with the company name that has a "&" in the name. Working off your last few post this is what i came up with so far.


I made the change on the sending side to look like this:


my ($company) = $rec{'Company'};
$company =~ s/&/zzzzz/g; # this converts & to zzzzz



This will show up on the receiving side in the link as "&Company=company zzzz company".


In the receiving form i have this code for converting it back:


$rec{'Company'} = my ($company); # or whatever the field name is
$company =~ s/zzzzz/&/g; # this converts zzzzz to &


In the form body I tried this: it showed just a blank field.


<td>Company:</td>
<td><input name="Company" value="$in{'Company'}" size="40"></td>


And I tried it with this code as well with the same results as above.


<td>Company:</td>
<td>$company</td>


And a few other combination with no luck.


Any ideals.


Ed-




Quote Reply
Re: [knue] passing data from 1 db to another. In reply to
not sure but i think you need to change

$company =~ s/zzzzz/&/g; # this converts zzzzz to &

to


$company =~ s/zzzzz/\&/g; # this converts zzzzz to &

see backslash in front of &

actually i thought you needed \ in front of & any time you have a substitution expression; can you tell if the company name is coming in correctly? if you turn on debug you should be able to see it.

Last edited by:

delicia: Dec 7, 2015, 1:59 PM
Quote Reply
Re: [delicia] passing data from 1 db to another. In reply to
Hi,

You shouldn't need to escape it if its in the substitution:

Code:
#!perl

use strict;

my $test = "zzzzz";

$test =~ s/zzzzz/&/g;

print "FOO: $test \n";


Quote:
C:\Users\Andy>perl test.pl
FOO: &

Doing it like this:

Code:
$test =~ s/zzzzz/\&/g;

...will still work (exactly the same output), but its not necessary :)

Angelic

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!

Last edited by:

Andy: Dec 8, 2015, 3:32 AM
Quote Reply
Re: [Andy] passing data from 1 db to another. In reply to
ignore all the colors below!!! andy is the perl expert and he said you don't need the backslashes.

ok, i on the sending side:

$in{'company'} = $rec{'Company'};
$in{'company'} =~ s/&/zzzzz/g; # this converts & to zzzzz



then on the receiving side:
# using if statement so that if $in{'company'} doesn't exist because you're doing
# something directly in this db, instead of copying from other
# you don't erase the existing value of
$rec{'company'}
if ($in{'company'}) {
$rec{'Company'} = $in{'company'}; #
$rec{'company'} =~ s/zzzzz/&/g; # this converts zzzzz to &
}

in the form, put:

<td>Company:</td>
<td><input name="Company" value="$rec{'Company'}" size="40"></td>
Quote Reply
Re: [delicia] passing data from 1 db to another. In reply to
When I use this code, nothing transfers over for the company name.
Quote Reply
Re: [knue] passing data from 1 db to another. In reply to
be sure call to copy includes $rec{'company'}

can't remember where we are in this!

call could have something like
... company=$rec{'company'} ...

then in the form or routine that copies to other db, have the following

if ($in{'company'}) {
$in{'company'} = s/zzzzz/&/;# or whatever i said before!
$rec{'company'}= $in{'company'};
)

Last edited by:

delicia: Dec 11, 2015, 6:05 PM
Quote Reply
Re: [delicia] passing data from 1 db to another. In reply to
Here is a walk through that will help you find my problem (coding problem :-) )
see your pm first.


After log on go to menu “add tap to new event”.

Enter 99 in week then search.
scroll down to find company “COMEY & Shepherd”.
Find the button at the bottom of the record that says “add tap to new event” it's in-between “amount” and “ jersey number 4”, click the button.

As you see the company information did not show up in the company field, but if you look at the blue header
it'll say “COMEY zzzzz Shepherd” so the information did come over the URL and covered the “&” to “zzzzz”, it just did not show up in the company field.

If you type the company name “COMEY & Shepherd” in the company field and then click “add tap” it will take you to the next screen where you will see a credit card icon at the bottom.

Click that icon and it will take you over to the credit card page which is Toby and the same thing occurs here no company name shows up, if you look in the URL you will see that the information copies over without the company information.

I’m add the sub routines for adsdb and toby which have the URL link and code used to convert the “zzzzz and &”.

Last edited by:

knue: Dec 12, 2015, 10:19 AM
Quote Reply
Re: [knue] passing data from 1 db to another. In reply to
i didn't notice case earlier but everywhere you have company, it should probably be Company. field names are case sensitive.

Example:

if ($in{'company'}) {
$rec{'Company'} = $in{'company'}; #
$rec{'company'} =~ s/zzzzz/&/g; # this converts zzzzz to &
}

should be

if ($in{'Company'}) {
$rec{'Company'} = $in{'Company'}; #
$rec{'Company'} =~ s/zzzzz/&/g; # this converts zzzzz to &
}

you can save & in a record; you just can't use it in the cgi string where you're passing it to other db.
Quote Reply
Re: [delicia] passing data from 1 db to another. In reply to
delicia, That did it. passing company name with a "&" through a URL is working!!

Thank you.
w00t
Quote Reply
Re: [knue] passing data from 1 db to another. In reply to
Great!!!