Gossamer Forum
(Advanced Search)
for
Home : Products : Gossamer Links : Version 1.x :

DBSQL.pm -- add_record problem

Aug 19, 1999, 7:28 PM
pugdog
Veteran / Moderator (6956 posts)
Aug 19, 1999, 7:28 PM
Post #1 of 9
Views: 4145
Shortcut
Quote Reply
DBSQL.pm -- add_record problem
Alex,

I've hit this twice now, and can't come up with an answer.

When I add a record using add_record to a table that is _NOT_ Links.def I get an error that the first term is missing:

Due to lots of print statements, I know all the terms are being passed INTO the routine.
To avoid problems, I follwed the FAQ guide for this test...

Quote:
Fatal Error: Unable to execute query: INSERT INTO Search_Log (Term, Count,Last_Look) VALUES (?, '1','935126868') . Reason:
Column 'Term' cannot be null at /www/postcards/cgi-bin/LinkSQL/search.cgi line 297

I got this when I first tried to play with the routines (which is why I asked the questions on how to use DBSQL.pm) and I'm still getting it.

It looks like add_record is ver links.def specific. modify works just fine... if the 'Term' exists, I can increment 'Count' and change the 'Last_Look'.

Robert

[This message has been edited by pugdog (edited August 19, 1999).]
Aug 20, 1999, 8:30 AM
Alex
Administrator (9387 posts)
Aug 20, 1999, 8:30 AM
Post #2 of 9
Views: 4007
Shortcut
Quote Reply
Re: DBSQL.pm -- add_record problem In reply to
Your table does not seem to have a primary key which is throwing off DBSQL.pm. Try recreating your table and defining a field called ID (doesn't have to be ID) and making it a primary key.

Cheers,

Alex
Aug 20, 1999, 5:38 PM
pugdog
Veteran / Moderator (6956 posts)
Aug 20, 1999, 5:38 PM
Post #3 of 9
Views: 4002
Shortcut
Quote Reply
Re: DBSQL.pm -- add_record problem In reply to
That's not it... at least from where I see. I have a primary key, and the def file looks like it's set right --

Quote:
# Auto Generated Config File.
# Created on: Fri Aug 20 23:31:35 1999
#

package Links: BSQL::Search_Log;

$db_name = 'links';
$db_host = 'localhost';
$db_driver = 'mysql';
$db_user = '';
$db_pass = '';
$db_key = 'Term';
$db_table = 'Search_Log';

%db_def = (
Term => ['1', 'CHAR', '30', '30', '1', '', '', '0'],
Count => ['2', 'INT', '10', '20', '1', '0', '^\d*$', '0'],
Last_Look => ['3', 'BIGINT', '20', '20', '0', 'NOW', '', '0']
);

%db_select_fields = (

);
%db_checkbox_fields = (

);
%db_radio_fields = (

);

1;

My table def in the database looksl ike:

Quote:
# Host: localhost Database : links
# --------------------------------------------------------

#
# Table structure for table 'Search_Log'
#

CREATE TABLE Search_Log (
Term char(25) NOT NULL,
Count int(11) DEFAULT '0' NOT NULL,
Last_Look bigint(21) DEFAULT '0' NOT NULL,
p_key int(11) DEFAULT '0' NOT NULL,
PRIMARY KEY (Term)
);


In the add_record routine, there is a '?' in one of the database calls, where "Term" would have been placed:

Quote:
# Perform the query.
my $table = $self->{'db_table'};
my $key = $self->{'db_key'};
my $query = qq!
INSERT INTO $table ($key, $insert_name)
VALUES (?, $insert_value)
!;

Would this have anything to do with it?

The primary key is 'unique' and that is what 'Term' is. It should be allowed once, and only once. I don't need an ID#, since 'Term' should be doing that.

I just can't figure it out... updates work, if the record already exists.



[This message has been edited by pugdog (edited August 20, 1999).]
Aug 20, 1999, 8:20 PM
pugdog
Veteran / Moderator (6956 posts)
Aug 20, 1999, 8:20 PM
Post #4 of 9
Views: 4029
Shortcut
Quote Reply
Re: DBSQL.pm -- add_record problem In reply to
I added a field, and all it did was shift all the values right, and still give a "?" as the first term and error.

[This message has been edited by pugdog (edited August 21, 1999).]
Aug 21, 1999, 8:30 AM
Alex
Administrator (9387 posts)
Aug 21, 1999, 8:30 AM
Post #5 of 9
Views: 4013
Shortcut
Quote Reply
Re: DBSQL.pm -- add_record problem In reply to
Ah, I see. DBSQL.pm is designed to work with an AUTO_INCREMENT or SEQUENCE as your primary key. You'll need to change your table to:

CREATE TABLE Search_Log (
ID INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
Term char(25) NOT NULL,
Count int(11) DEFAULT '0' NOT NULL,
Last_Look bigint(21) DEFAULT '0' NOT NULL,
p_key int(11) DEFAULT '0' NOT NULL,
UNIQUE (Term)
);

and it should work. I'll look at changing DBSQL.pm so it will work when the primary key does not need to be kept track of.

Cheers,

Alex
Aug 21, 1999, 2:02 PM
pugdog
Veteran / Moderator (6956 posts)
Aug 21, 1999, 2:02 PM
Post #6 of 9
Views: 4020
Shortcut
Quote Reply
Re: DBSQL.pm -- add_record problem In reply to
Ok,

Now I'm having problems with the program asking for:

Quote:
Fatal Error: Index problem. Query: DELETE FROM Search_Log_Index WHERE ID = '7' . Reason: Table 'links.Search_Log_Index'
doesn't exist at /www/postcards/cgi-bin/LinkSQL/search.cgi line 328

Apparantly, this routine is really set up to handle the "Links" datbase.

In order to be able to add tables, not just fields, we'll need to have a routine that is not expecting a "Links" format database with autoincrement and the *_Index table.

Most mods _are_ going to be additional idexes of sorts to the Links table, a uniform way of addressing them should be added ASAP.

It's too early in this development, and with too few users, to have multiple access methods, when all that is needed is one good set of database utils.

Every time I think I got it... <G>.... another speed bump.
Aug 22, 1999, 7:32 AM
pugdog
Veteran / Moderator (6956 posts)
Aug 22, 1999, 7:32 AM
Post #7 of 9
Views: 4003
Shortcut
Quote Reply
Re: DBSQL.pm -- add_record problem In reply to
I'm really trying to figure this out...

I cloned the "Links_Index" table (with the appropriate changes" and now everything is working...

But...

1) DBSQL.pm should really allow access to the database, not just the links table...

2) I now have a meaningless auto-increment field, and another table indexing that filed -- that is not updated/re-indexed with the re-index command -- making it even more meaningless.

DBSQL.pm really should be the interface to the database -- OOP and all.

Maybe adding a field to database calls so that there is an additional parameter to know whether it's an autoincrement or not?

Or a new routine for adding a non-auto-increment record and managning non-auto-increment tables?

I still don't know enough about the logic of LinkSQL, but I'm learning....

I've made the investment into SQL, and I know I need it, and Links is my best initial platform for the next few years, at least.



------------------
Robert S. Pataki, MD
President, PUGDOG Enterprises, Inc.
http://www.postcards.com
Aug 23, 1999, 12:41 PM
Alex
Administrator (9387 posts)
Aug 23, 1999, 12:41 PM
Post #8 of 9
Views: 4014
Shortcut
Quote Reply
Re: DBSQL.pm -- add_record problem In reply to
Hi,

I think you have one of your fields set to be indexed. Make sure the last column is 0 for all your fields.

The indexing feature is useful if you have TEXT or BLOB fields that mysql can't search quickly through. DBSQL.pm will create and maintain a separate keyword index of that field and use it to search on.

Cheers,

Alex
Aug 23, 1999, 3:56 PM
pugdog
Veteran / Moderator (6956 posts)
Aug 23, 1999, 3:56 PM
Post #9 of 9
Views: 4018
Shortcut
Quote Reply
Re: DBSQL.pm -- add_record problem In reply to
That wasn't something that was clear. It's index weight. But, I guess it also turns 'indexing' on and off, not just 'weighting' of the index.

I'm trying to get an understanding of this, and figured the best way was the keyword logger, since it should have been just a simple search/add/modify routine, but it's the foundation of everything else.

It's very short code, but I'll start another thread on it since this is about the DBSQL.pm and adding records.