Gossamer Forum
Home : Products : Gossamer Links : Discussions :

New Install - Database Errors

Quote Reply
New Install - Database Errors
I just installed my new license and I am receiving errors any time I try to do any kind of database operation.

For example, When trying to add a category, this is the error:

Perl Version: 5.006001
Links SQL Version: 2.1.1
DBI.pm Version: 1.28
Persistant Env: mod_perl (0) SpeedyCGI (0)
GT::SQL::error = Failed to execute query: 'SELECT ID FROM lsql_Category WHERE FatherID = ? AND Name = ? ' Reason: [Microsoft][ODBC SQL Server Driver][SQL Server]Disallowed implicit conversion from datatype 'varchar' to datatype 'int'
Table: 'somd.somd_admin.lsql_Category', Column: 'FatherID'
Use the CONVERT function to run this query. (SQL-37000)(DBD: st_execute/SQLExecute err=-1)


This is a Windows 2000 Server with SQL Server 6.5. I am thinking that maybe this product doesn't really support MSSQL 6.5? I asked if it did in a pre-sales thread and was told yes.
Quote Reply
Re: [vicos2] New Install - Database Errors In reply to
Checkout README.MSSQL in the zip file.
Quote Reply
Re: [PerlPod] New Install - Database Errors In reply to
1. Use ANSI Nulls, Paddings and Warnings is _NOT_ checked.

2. "Links SQL has been tested under Microsoft SQL Server 7" does not jive with what I was told here:

http://www.gossamer-threads.com/...;;page=unread#unread

Which leaves me back at my first post.
Quote Reply
Re: [vicos2] New Install - Database Errors In reply to
I don't use M$ stuff if at all possible, but looking at the message you pointed to, maybe go into the admin area, and make sure the little drop-down box in the database-> properties areas for the categories, links and users tables is set to NONINDEXED then repair/rebuild the database and searches.


PUGDOG� Enterprises, Inc.

The best way to contact me is to NOT use Email.
Please leave a PM here.
Quote Reply
Re: [pugdog] New Install - Database Errors In reply to
If this is the actual statement being executed:

SELECT ID FROM lsql_Category WHERE FatherID = ? AND Name = ?

The problem lies here since this is not a valid SQL statement for MSSQL 6.5

I would like to get an answer from the support staff so I know for sure what the problem is.

I am aware that many people do not care for MS products. Their high price is the primary reason that we are still using 6.5 and not 2000. Unfortunately, this is a legacy system.

If I can still use MS Access as a front end to mysql across TCP/IP, I will consider evaluating a migration. I can build an administrative front-end for our applications in a fraction of the time it would take to build them using PHP or Perl or whatever. Administering a database via an Access front-end is also a zillion times more efficient than having to rely on a web-based tool.
Post deleted by vicos2 In reply to
Quote Reply
Re: [vicos2] New Install - Database Errors In reply to
Hi,

Can you make sure you have the latest perl, and DBI and DBD::ODBC modules? That error seems to be from DBD::ODBC not converting the type automatically, and shouldn't be dependant on your version of MS SQL.

Cheers,

Alex
--
Gossamer Threads Inc.
Quote Reply
Re: [Alex] New Install - Database Errors In reply to
Alex, This is my development server and I just built it 2 weeks ago with the latest version of everything. I installed DBI/DBD just a day or two ago when I installed SQL-LINKS. These were all fresh downloads from the net.
Quote Reply
Re: [vicos2] New Install - Database Errors In reply to
Also, if you look at my first post, you will see the version numbers of all of the key modules.
Quote Reply
Re: [vicos2] New Install - Database Errors In reply to
I just executed a simple "select * from" statement to a pre-exisitng table in that database via your SQL Monitor tool and the query works fine. So, the database and ODBC defs are fine.
Quote Reply
Re: [vicos2] New Install - Database Errors In reply to
Hi,

Hmm, can you try editing admin/GT/SQL/Driver/ODBC.pm and change:

$self->{sth}->bind_param ($idx, undef, $type);

to:

$self->{sth}->bind_param ($idx, undef, $type);
if ($type =~ /INT/) { $_[$idx] = int $_[$idx]; }

and see if that fixes it? Also, what version of DBD::ODBC do you have? The issue is in DBI/DBD::ODBC and how it binds parameters and passes it off to MS SQL.

Cheers,

Alex
--
Gossamer Threads Inc.
Quote Reply
Re: [Alex] New Install - Database Errors In reply to
DBD::ODBC is 0.28. This is ActiveState Perl and DBI and DBD were installed via PPM.
Quote Reply
Re: [vicos2] New Install - Database Errors In reply to
No, sorry. I added that line and the results are the same.
Quote Reply
Re: [vicos2] New Install - Database Errors In reply to
Hmm, one more thing to try (I wish I could reproduce that here). In the same area of code, right before:

my $rc = $self->{sth}->execute(@_) or return $self->error ("CANTEXECUTE", "WARN", $self->{query}, $DBI::errstr);

add:

foreach (@_) { /^\d+$/ and ($_ = int $_); }
my $rc = $self->{sth}->execute(@_) or return $self->error ("CANTEXECUTE", "WARN", $self->{query}, $DBI::errstr);

Let me know if that works for you.

Cheers,

Alex
--
Gossamer Threads Inc.
Quote Reply
Re: [Alex] New Install - Database Errors In reply to
Hmmm, no good on that code change.

This data conversion error has to do with passing chars when it expects an int and vice versa. If I say "SELECT * FROM lsql_users where username=1" you will get that error because username is varchar and it wants quotes around the value.

I'll pick this up in the am. I have a modem on that box, so I can dial into the net and give you the IP if you want to get in and look around