DBI module craching with invalid HTTP headers?

[1DMF]

Hi,

Can someone explain why DBI module is crashing when I use an asterisk for column selection in my SQL statement.

I want all columns which you indicate wil an asterisk in the SQL ODBC specification.

Win32:ODBC works fine with it why won't DBI?

And why isn't my || die working? I catch erros yet DBI isn't showing them yet Win32::ODBC does?

Thanks
1DMF



"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[audiopro]

What statement is it objecting to?

Keith

http://www.studiosoft.co.uk

 

[1DMF]

Hi Keith,

It's falling over on the execute statement.

    # Run SQL Command
    my $sth = $db->prepare("$sel") || &sql_error("Error in getSQL ($error): $sel");
    $sth->execute();

I've found that it's not just because I used an asterisk, as I went into SQL ran the same query (which changes the asterisk to all the column names), then copy pasted the column names into the SQL statement and it's doing the same thing.

I'm not geting an error, it's just giving the 'failed to return set of HTTP headers' generic error.

I am probably having to go through the select statement a column at a time on Monday to see if I can work out which column is causing the crash.

At least that might point to a reason why the execute is falling over, yet the prepare isn't.



"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[audiopro]

You should be able to pull all of the columns out as a * without it falling over.
I use a different syntax to yours for my statements but I have never seen the headers problem before.
Are you sure the error is being caused in the SQL call and not somewhere in the code which displays the results.

This is the code I use and never have a problem with it.

my $sql="SELECT * FROM PROMEMBER WHERE USER='USERNAME' && PASS='PASSWORD'";
my $sth=$dbh->prepare($sql);
$sth->execute();
my $rv=$sth->rows;

Keith

http://www.studiosoft.co.uk

 

[1DMF]

Hi Keith,

Well I put debug statements in step by step to narrow down where the error occured.

I'll know tomorrow when I can do more testing.




"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[1DMF]

OK, found out what is killing it.

well it's either number of columns or string length of colums.

If I remove some columns it works, as soon as I go over some invisible 'limit' it crashes without any error message?

So now what?

I even put

    print "Content-type: text/html\n\n";

as the first line of the script yet I still get the http headers error, even though the header is printed before the SQL query is made.

I don't know what to do and its taken out a major system, any ideas why this is happening?






"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[audiopro]

Is it definitely crashing on the execute?
Can you give an example of print "$sel";

Keith

http://www.studiosoft.co.uk

 

[1DMF]

yes definately dying on execute, I've even done this...

$sth->execute() || die "Error : " . $sth->errstr();

but I just get invalid headers.

here is the $sel ...

SELECT * FROM My_Table WHERE Memno='xyz' AND Rec_ID = 153 ORDER BY Memno Desc

names have been changed to protect the innocent ;-)

it's only selecting one specific record and as I say if I change the asterisk to every column bar a few it works, but I've mix and matched to ensure it wasn't a specific column and it isn't , it seems to be the number of columns causing the crash?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[1DMF]

oh and to clarify I have even done this

        $sth->execute();
        die "got here";

I don't get the die!

If I put it above the execute (after the prepare) it executes the die command no problem, that to me isolates it to the execute statement.

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[audiopro]

This is the point where we have to start grabbing at fresh air. It has to be something simple, they usually are.

Do you get the same result when pulling another record?
Have you tried creating a record with very basic data ie. no chance of illegals or speech marks etc.
How many fields are in the table?
How much data does rec 153 contain?
Is this a small part of a big app?
Does it do the same if you query via a test app?
Is it possible the invalid headers are created by a slightly duff table.


Keith

http://www.studiosoft.co.uk

 

[1DMF]

1. yes
2. the records are very basic, and it works fine for any record if you don't select all columns.
3. 78 columns
4. not much at all, most fields are integers with 0 in them, a few date/time, some ntext and some decimals and some money fields.

here is the whole record ...columns

Rec_ID	MemNo	Issue_Date	Period_Start	Period_End	Due_Date	Comp_Date	Broker_Fees_Reg	Broker_Fees_Non_Reg	Proc_Fees_Reg	Proc_Fees_Reg_Dets	Proc_Fees_Non_Reg	Proc_Fees_Non_Reg_Dets	Income_Commercial	Income_Commercial_Dets	Income_Loans	Income_Loans_Dets	Income_Insurance	Income_Insurance_Dets	Income_Other	Income_Other_Dets	App_Person	App_Person_Dets	LifeTime	Staff_Tot	Staff_Mort	Staff_Ins	Staff_PT	PT_Hours	Leavers	Leavers_Dets	Sel_Mark_List_Mort	Sel_Mark_List_Ins	Sel_Ref_Intro_Mort	Sel_Ref_Intro_Ins	Sel_Ref_Brokers_Mort	Sel_Ref_Brokers_Ins	Sel_Telesales_Mort	Sel_Telesales_Ins	Sel_ColdCall_Mort	Sel_ColdCall_Ins	Sel_Visits_Mort	Sel_Visits_Ins	Sel_Postal_Mort	Sel_Postal_Ins	Sel_Direct_FP_Mort	Sel_Direct_FP_Ins	Sel_Repeat_Mort	Sel_Repeat_Ins	Sel_Internet_Mort	Sel_Internet_Ins	Sel_Ad_News_Mort	Sel_Ad_News_Ins	Sel_Ad_Mag_Mort	Sel_Ad_Mag_Ins	Sel_Ad_TV_Mort	Sel_Ad_TV_Ins	Sel_Ad_Radio_Mort	Sel_Ad_Radio_Ins	Sel_Ad_Internet_Mort	Sel_Ad_Internet_Ins	Sel_Ad_Other_Mort	Sel_Ad_Other_Ins	Sel_Ins_Motor	Sel_Ins_Home	Sel_Ins_PP	Sel_Ins_Travel	Sel_Ins_ASU	Sel_Ins_Legal	Sel_Ins_PMI	Sel_Ins_CIC	Sel_Ins_PHI	Sel_Ins_Life	Sel_Ins_HCPlan	Sel_Ins_Ex_War	Complaints	Declaration	Status

and values...

153	MY_MEMNO	23/03/2010	18/03/2010	31/03/2010	17/04/2011	13/04/2010	£0.00	£0.00	£0.00		£0.00		£0.00		£0.00		£0.00		£0.00		0		0	0	0	0	0	0	0		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	Agree     	COMP

5. it's a self contained script which serves a specific purpose

6. sort of answered by 5. but basicall ythe script does this...

#!/usr/bin/perl

######################
# Set Error Trapping #
######################

use CGI::Carp qw(fatalsToBrowser warningsToBrowser); 
use warnings;
use strict;

# Set path to user modules
use FindBin qw($Bin);
use lib "$Bin";

######################
# Use Globals Module #
######################
use Memglobs;

##################
# Use SQL Module #
##################
use Sql2;
    
#######################
# Use Template Module #
#######################
use Template;

##########################
# Use Currency Formatter #
##########################
use Locale::Currency::Format;

##################
# Use CGI Module #
##################
use CGI;

###################
# Set SMTP Server #
###################
use constant SMTP_DOMAIN => "127.0.0.1";

# Read FORM or URL data
my $cgi = new CGI;

# Set Action & User variables
my $action = $cgi->param('FA');
my $user = $cgi->param('user');

# Set Date & Time
my @ukdate = &uk_date;


#################################################################
# UNCOMMENT CODE BELOW IF YOU WANT TO STOP MEMBERS USING SYSTEM #
#################################################################
if(!STAFF){
    print "Content-type: text/html\n\n";
    print "<h3 style=\"color:red;\">Sorry system is currently unavailable due to maintenance, please try again later</h3>";}
    exit();
}

############### ARE THEY LOGGED IN CHECK ###################

if(&log_chk("$user") ne "yes"){&sys_error(2,"no"); exit();}

# Check is AR Pricipal
if(!&getSQL("mytable","Role","ID = '$user' AND Role='myrole'")){
    &sys_error(5,"no"); exit();
}
 
################# FUNCTIONS ####################   

if($action eq "VIEW"){
   &view_rmar();
   exit();
}

there is other functions but no point in showing them as they aren't called....

then the view sub which is crashing...

######### VIEW RMAR ###########

sub view_rmar {

# Update WhereIS record
&where_is("$user","Viewing Completed RMAR");

my $rec = $cgi->param('ID');

my @sel;
[blue]
# get RMAR
my @rmar = &getSQL("My_Table","*","Memno='$user' AND Rec_ID = $rec","Memno Desc","test"); 
[/blue]

there is code below but the blue line is where it crashes.

I don't see how the table is duff, it works with MS Access, SQL enterprise manager and Win32::ODBC with perl, it's ONLY DBI that is causing the problem.

The most annoying thing in all this is not being able to get the error outputted! I'm assuming it must be throwing one?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[1DMF]

cracked it!

It was

$db->{LongReadLen} = 9999999;

I found another thread with someone else having problems and they played with the LongReadLen attribute, I made it smaller and it worked.

So the moral of the story is, don't go making it some rediculously large number just to stop the problem when it crashes when LongReadLen hasn't been set!

I'm guessing there must be some cache or buffer or something and it is getting full limiting the number of columns that can be selected.

Perhaps an out of memory or something similar!

Man this was a headache to work out, but at least I got there!

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[stevexff]

Phew! I was thinking of quitting the forum and slashing my wrists for suggesting that you give DBI a try in the first place

Now I guess *all* you have to do now is see if it sorts out your memory leak or not...

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[1DMF]

stevexff -> don't think I wasn't cussing you at times

But seriously, if it fixes the initial problem, then you will have earned your star!

If it doesn't I'll be tracking you down and slashing your wrists for you!

We are also assuming I don't get further problems with DBI, but I have a feeling the worst is behind us...

Keeps us on our toes I guess

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[audiopro]

Glad you sorted it - these things can drive anyone nuts!

I do a lot with MySQL reads via DBI and have never encountered 'LongReadLen' at all so I am either missing something vital or saving myself a lot of bother - not sure which.


Keith

http://www.studiosoft.co.uk

 

[1DMF]

Not sure it's required with mySQL, I use DBI on my Google Rank Extractor project and mySQL with no problems.

It seems related to the DBD::ODBC part of DBI and MS SQL.

If you don't set the LongReadLen it baulks with a truncation error to do with the way it queries the length of a column before fetching, but then when it converts the data with UTF-8 the returned column can be longer than originaly thought.

Still not sure what a good number to set it to is, I currently have it at 9999, so if anyone knows the optimum, I'd appreciate it.

cheers, 1DMF





"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts