Say we have two databases outside; one is links another is only one table but i do everything the same for it as links has for itself; so i have a .def for my table and so on.

Now i want to pass to the home.htm a tag with data from this second db.

So here we go:
1. Add the vars in build_home(Build.cgi), the same as doing it for links itself,
2. give the links.pm some more vars; one for the
path to admin of the second db ...

Add this to build.cgi
# Verbraucher/Themen DB
$LINKDB_V_T = new Links::DBSQL "$LINKS{admin_themen_root_path}/defs/Links.def";

Add this to home_build in build.cgi
##--## Last New X von Verbraucher/Themen DB on home
# Let's get all the new links and order them nach build_sort_order_new_home in links.pm

$sth = $LINKDB_V_T->prepare (qq!SELECT * FROM Links order by $LINKS{build_sort_order_themen_home} LIMIT 5!);
$sth->execute();
($sth->rows > 5) and print "\tWarning: Max New links limit of 5 exceeded!\n";

Now i thought, the sub would get the links.def from my new DB, from there it gots the data for the db, then calling dbsl, connect to db and get the right data, passing to a tag later; but what should i say, this &(§/%(/§%&" Thing gets dfata from my links; so is it possible that dbsl has a static value somewhere which db is connected, and only the table is passed tru ???

Hmm, very mysterious.

Robert

Don't forget that the /defs/*.def file has some control over where the program looks for the data.

You can set up multiple sites that use the same database by using the same *.def files.... even with different build paths.

You can use different databases, such as if you run your own server and want to keep the 'basic' Links program files in one database, and add-ons in another.

You just change the database name in the appropriate .def file, and it will build in your Links path/tree, but take the data from a different area.

http://www.postcards.com
FAQ: http://www.postcards.com/FAQ/LinkSQL/

I thought this, too. But my try to get data from two dbs fails.
This is what i do:

Links A resides in: /home/linksA/admin
Links B resides in: /home/linksB/admin

in build.cgi: sub build_home_page: all for/in DB A

my $dbA= new Links::DBSQL "/home/linksA/admin/defs/Links.def";
$sth = $dbA->prepare(qq!SELECT * FROM Links order by $LINKS{home_sort_A} LIMIT 5!);
$sth->execute();

# Build the html
while ($link = $sth->fetchrow_hashref) {
$OUT{'link_A'} .= &site_html_link ($link);
}

my $dbB= new Links::DBSQL "/home/linksB/admin/defs/Links.def";
$sth = $dbB->prepare(qq!SELECT * FROM Links order by $LINKS{home_sort_A} LIMIT 5!);
$sth->execute();

# Build the html
while ($link = $sth->fetchrow_hashref) {
$OUT{'link_B'} .= &site_html_link ($link);
}
....

print HOME &site_html_home ( { category => $category, grand_total => $GRAND_TOTAL, TopA=>$OUT{'link_A'}, TopB=>$OUT{'link_B'} } );

With this i got two times data from DB A with the tags TopA and TopB in home.html.
Shurely i have two different db_name in the two links.def

Hi Pugdog, as you write in mail you have done this before. On this way?

Robert

PS: Something strange: In the same sub there is a call for catdb = new .... category.def;
when i use instead of dbA the catdb i got the same data from the links-table. UGH?

The problem, why you cant use two databases without some coding is this line:
Here it asks if there is db_driver set.
Cause both .def files has this value, there wouldn´t be done a new connection to a second database.
So i must find a way to ask for something else, maybe somewthing like:

if db_name ="definition in links.def from the orginal db
then defined $DBH and $_[0]->{db_driver} and return 1;
else go on.

sub connect {
# ---------------------------------------------------------------
# Connects to the SQL server.
#
# IN : DBI connect string and table name (optional)
# OUT: 1 on success, undef on connect error.
#
defined $DBH and $_[0]->{db_driver} and return 1;

Define the connection to two databases.

If you are trying to do this, use $DBH_A and $DBH_B when you have to split hairs, and default to $DBH_A = $DBH



http://www.postcards.com
FAQ: http://www.postcards.com/FAQ/LinkSQL/

Hi Pugdog, i dont understand what you mean.

sub connect {
# ---------------------------------------------------------------
# Connects to the SQL server.
#
# IN : DBI connect string and table name (optional)
# OUT: 1 on success, undef on connect error.
#
defined $DBH and $_[0]->{db_driver} and return 1;

The only way to make this running i see, is to ask which connection is open.
So if i could aks something like:

$test = $DBH or better the db_name
if $_[0]->(db_name) eq $test
then defined $DBH and $_[0]->{db_driver} and return 1;

else go on.

I have it running now, it´s not very elegant, but ...

sub connect {
# ---------------------------------------------------------------
# Connects to the SQL server.
#
# IN : DBI connect string and table name (optional)
# OUT: 1 on success, undef on connect error.
#

if ($_[0]->{db_name} eq "Name_of_the_first_db"){
defined $DBH and $_[0]->{db_driver} and return 1;
}

and i switch the order of building so the queries to the second db is the last one in the script.

The problem is still that "defined $DBH"

I could get rid of while outcommenting this line, so every query goes tru the whole connecting, but that could not the way.

I must ask in runtime for the value of the last connect, compare it to the new incoming and decide if connect or return.
But how to do that?

Robert

You could instead leave connect as is and do:

... first insert usings Links A

$Links:DBSQL::DBH->disconnect;
undef $Links::DBSQL::DBH;

... second insert using Links B

$Links:DBSQL::DBH->disconnect;
undef $Links::DBSQL::DBH;

in your code.

Hope that helps,

Alex

--
Gossamer Threads Inc.

Thank you Alex very much, i cant do it with your code, but it brings me to the right idea;

so i do now in build.cgi:

call DB A
...
call DB A

undef $Links::DBSQL::DBH;

call DB B

undef $Links::DBSQL::DBH;

call DB C

undef $Links::DBSQL::DBH;

call DB A
...
call DB A


Again, one simple advice and a solution just in minutes :-) instead of searching for hours and days :-(

Oops, your right, my code was broken, instead of:

$Links:DBSQL::DBH->disconnect;

you should do:

$Links::DBSQL::DBH->disconnect;

(note the extra : in there).

Cheers,

Alex

--
Gossamer Threads Inc.

So with my code (only to undef the variable) the connection stays open?
I have tried your code with douple :: before (cause of the old forum i always look closer to ::DBSQL :-),
but i got a lot of error-messages while disconnecting and so i only use the undef.

Is this a rule: Links::DBSQL::Var = xyz
to set a (main) var in another script?

So could i do Links::DBSQL::Function::Variable, too - but with this i must define Function in my main script?

But this makes no sense, cause i could call a function directly with function (var), hmm.

Robert

No, but if you don't call disconnect() first, you will see errors in your error_log like:

You should always call disconnect() before you destroy a database handle. This is very important on other databases like Oracle, MS SQL 7, but not so on mysql (you just get warnings).

You'll see at the top of DBSQL, the statement:

package Links::DBSQL;

This means all the variables are in the package Links::DBSQL. You can access any global (not declared with my()) by using $Links::DBSQL::VARIABLE = foo;

Cheers,

Alex




--
Gossamer Threads Inc.

Hmm, that´s strange, now im using only the undef statement and dont get any error-messages;
if i used your code first disconnecting, then undef i got close this message you write about ????

Robert

I have a similar problem...what I am attempting to do is setting up different databases with tables that will be used across different databases.

Example:

I have three tables in a database called something2 that will be used in other databases. These tables are called properly in the front-end scripts (e.g., user.cgi), however, in the back-end, I keep getting the following error:



NOTE: something is a replacement for my real database names.

I have put .def files for the three database files in a separate directory underneath defs. In these .def files, I have changed the $db_name to be something2. I also edited all references to these .def files in the DB_Utils.pl to the correct path where the .def files are located.

Why does this work fine in the front-end, but I get errors in attempting to access the tables via admin.cgi?

Any suggestions would be greatly appreciated.

Thanks in advance.

Regards,

Eliot Lee

Never mind...figured it out...just used the undef codes Alex provided and then wrapped them around a conditional if statement that checks the db parameter for an array of "databases" (tables) that I referenced in the Links module file.

Thanks for your assistance, Alex...Appreciate you posting these codes.

Regards,

Eliot Lee

Okay...I am having some more challenges with getting multiple databases to work...I have worked out most of the bugs, but when I try to add a record into a table located in other databases, I get the following error:



Line 99 of admin.cgi is:



So, I looked through sub add_record in the DBSQL.pm module, and I think the problem is pulling the correct ID via the _post_get_id subroutine.

Any thoughts or suggestions for fixing the above error would be appreciated.

BTW: I have searched through the MySQL site and my locally stored MySQL Manual and I can't find anything that would address this problem and since it deals with the core module of Links SQL, I thought others may provide some insight into this matter.

Now...the odd thing is that I can import files into the tables located in the other databases. But when I try to run functions, like ADD, via admin.cgi, I keep getting the above error.


Regards,

Eliot Lee

Sorry, i dont understand what you want to do.

I use one links as a user-database and another for content;
only with login in you could get to a restricted area and have all functions.

I melt my dbs only that kind that the user-database gets content from the second and show it at the home e.g.

If you only need some special tables, you dont need another links; i have done this for alternate-access,
means i could give any user different access-levels, where a small script reads this table and give the right links to do something; with a very special mod in your apache you have a very easy but relative secure access-system.

Robert

Thanks for the reply, Robert.

What I am attempting to do is set-up additional databases to hold the following data in different tables:

Database1: Links
Tables
* Build_Updates
* Category
* Links
* Validate

etc, etc....

Database2: Utilities
Tables
* Country
* Currency
* Degrees
* State
* Years

etc, etc....

Database3: Forum
Tables
* ForumCategory
* ForumTopics
* ForumPosts

etc., etc....

Database4: Career Connection
Tables

* Build_Resume_Update
* Build_Jobs_Update
* Jobs
* Jobs_Validate
* Resumes
* Resumes_Validate

etc, etc....

The problem I am having is adding data both via the admin script and separate public CGI scripts to tables in Database2, Database3, and Database4.

I have gotten a bunch of error message, but the one I posted before seems to be the most puzzling and complex.

Regards,

Eliot Lee

Ok. It seems for me that you don´t want to have one big links as i use here,
but different tables indexed over the id to hold data init. Right?
OR do you have more than one values for fields in links?


For this i could maybe help you: (i know its not the best solution, but it works for me)

I have the field access in links
and a new table access_alternate

with this i copied all the functions for alt-cats to alt-access

but, i remark that´s not the thing you need cause users will give the data and not you
(the old problem with more than one cat)

Hmm, please give me an detailed example:

Links with Title, Desc, URL, Contactname and mail

then Utilities?
One value for every field or more?

The forum thing i have solved with wwwthreads, while holding the data twice.

To use extern scripts and the admin, i have solved this for fields like mailsend, detailhits and so on. This is no problem. eg: modify the jump.cgi for detailhits and do additional table for detail_track like hits_track and add a new field in update and dont forget to find all the points where the update-table is used (build with stats, jump or rate eg.) For these things i use new .def-files and have no probs with them.

Sorry.

Robert