Gossamer Forum
Home : Products : Gossamer Links : Version 1.x :

Links v1.11 --> Problem with Multiple Databases

Quote Reply
Links v1.11 --> Problem with Multiple Databases
I originally posted this problem in the following Thread:

http://www.gossamer-threads.com/...w=collapsed&sb=5

-----------------------------------------------------------
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:



DBSQL (11346): Fatal Error: Unable to execute query: INSERT INTO Year (ID, Name) VALUES (?, '1917') . Reason: Column 'ID' cannot be null at /dir/admin.cgi line 99


Line 99 of admin.cgi is:



my $id = $db->add_record (&cgi_to_hash($in), $in);


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
Quote Reply
Re: Links v1.11 --> Problem with Multiple Databases In reply to
Ok:

First, I'm running multiple databases. My postcards database is separate from the Links database (the sent-cards vs the cards-to-send) and on two sites, the postcards database is not even in the same "Database". I have 3 different databases running through DBSQL.pm on one site, and I've never had a confusion problem.

Two problems I've had, is that DBSQL.pm sometimes assumes that all fields are going to be there, and the log is filled with useless warnings about "uninitialized value used in Match" or "uninitialed value used in interator" etc. Doesn't seem to stop execution, but I was able to get rid of them by adding in some ugly hacks (initializing all fields in a passed hash) and/or first testing to see if the value exists.

my $id = $db->add_record (&cgi_to_hash($in), $in);

What seems to be happening, is something I hit way back in the first versions, where the ID field is not properly set.

Most likely, there is no field "ID" so that it can't be passed.

If you broke this down into:

$in = &cgi_to_hash($in);
$in->{'ID'} = '';

So that the field ID actually existed, but had a null value?

Or, delete $in->{'ID'}, so that it didn't exist at all.

I'm not sure which would work.

BTW.... this may be silly, but column Year.ID is autoincrement? and your Year.def file is up to date?



PUGDOGŪ
PUGDOGŪ Enterprises, Inc.
FAQ: http://postcards.com/FAQ


Quote Reply
Re: Links v1.11 --> Problem with Multiple Databases In reply to
Thanks for the reply, pugdog! Smile

In Reply To:
Most likely, there is no field "ID" so that it can't be passed.
Welp...there IS an ID field in the tables that I am attempting to add data...

In Reply To:
So that the field ID actually existed, but had a null value?
Or, delete $in->{'ID'}, so that it didn't exist at all.
I don't know if this would solve the problem, but you did give me an idea for hacking the DBSQL.pm module that may make it work.

In Reply To:
BTW.... this may be silly, but column Year.ID is autoincrement? and your Year.def file is up to date?
Actually...yes...it is autoincremented and also primary. And the Year.def file matches the configurations I've set-up in the Year table.

Anyway...thanks for your insights...while I don't think deleting out the $in{'ID'} will do the trick...it does give me some ideas to play with.

Thanks!





Regards,

Eliot Lee
Quote Reply
Re: Links v1.11 --> Problem with Multiple Databases In reply to
Let me know what works.

The way the ? operator works, is if the field is left EMPTY, it inserts the value passed in, or a NULL. For an auto-increment field, a NULL value grabs the next available ID. It's a place holder function.

But how it all works depends on how you are tracing out the code. I _know_ I hit these problems when I started to tie into the DBSQL.pm, but figured out what I was doing "wrong" and don't seem to hit those problems any more.

These errors do go away when you find the right combination :)

PUGDOGŪ
PUGDOGŪ Enterprises, Inc.
FAQ: http://postcards.com/FAQ


Quote Reply
Re: Links v1.11 --> Problem with Multiple Databases In reply to
In Reply To:
The way the ? operator works, is if the field is left EMPTY, it inserts the value passed in, or a NULL. For an auto-increment field, a NULL value grabs the next available ID. It's a place holder function.
Thanks...I do understand that...but the challenge to resolve is why this does not pose a problem in the original "Links" database while this is a problem in other databases...

I will post the solution if and when I find it.

Thanks again.

Regards,

Eliot Lee
Quote Reply
Re: Links v1.11 --> Problem with Multiple Databases In reply to
Welp...for some reason, when I created the MySQL tables, the ID field was not set to AUTO-INCREMENT, which was the source of this particular problem. I thought I had set each of the ID fields in the tables to AUTO-INCREMENT.

Regards,

Eliot Lee
Quote Reply
Re: Links v1.11 --> Problem with Multiple Databases In reply to
Wellll :) Sometimes it really is the silly problems.



PUGDOGŪ
PUGDOGŪ Enterprises, Inc.
FAQ: http://postcards.com/FAQ