Insert date into MySQL database with DBI

[jimineep]

Hey I'm using DBI to put a date entered value into a mysql database. However I'm having problems. My date column in NOTNULL but when i enter the date (as a string) from my perl script, whilst all other columns are filled, the date entered column remains 0000-00-00. I know it is calculating the date corectly, the prob seems to be entering the date into the database

Any ideas? Here's my code

my ($day,$mon,$year) = (localtime)[3..5];
my $dateImported=(printf "%d-%02d-%02d\n",$year+1900,$mon+1,$day); #COOL CODE, BUT NOT WORKING (SHOULD RETURN INTEGER NOT STRING? NOT SURE WHY THIS IS)

#print $dateImported;

#open(OUT, '>output.txt') or die "couldnt open";

#$dump = Dumper($query);
#print Dumper($query);
	 #all important mySQL to put into the database what is needed 
 my $sql1 = "INSERT INTO user_BP_Files (fileName,userName,bioPaxfile,dateImported) VALUES (?,?,?,?)"; 

 #now prepare and execute 
 my $sth1 = $dbh->prepare( $sql1 ); 
                    
$sth1->bind_param(1, $fileName);
$sth1->bind_param(2, "userName");
$sth1->bind_param(3, $BPfile);
$sth1->bind_param(4, $dateImported); #NOT WORKING? NOT SURE WHY

Thanks a lot

Jim

 

[TonyGroves]

Why do you have a "\n" in $dateImported? Drop it and see what happens. You could also embed the variables directly in the query; it's simpler and more transparent than using bind_param.

 

[TonyGroves]

Just noticed, that should be sprintf, not printf.

 

[jimineep]

I bind them because I am dumping large XML files with ' and "s in, so they need to be binded or DBI messes up (this got me for a while!) I will try getting rid of the \n, i just found the code to get the time in this format on a random forum

 

[jimineep]

I dont think the printf vs sprintf is the problem, as i say the print $dateImported will print 2006-05-16 to screen no problem............

 

[TonyGroves]

Sure, it prints the date to the screen, but, with your code, $dateImported then gets assigned printf's return value which is probably 1.

 

[jimineep]

Hmm i tried sprintf and dropping the \n ie

my ($day,$mon,$year) = (localtime)[3..5];
my $dateImported=(sprintf "%d-%02d-%02d",$year+1900,$mon+1,$day);

but unfortunately still no dice

I might look for some different code that returns current date to the variable instead of printing it to screen

 

[ishnid]

Tony's right about the reasons it's failing. Change printf to sprintf and remove the newline in your sprintf pattern.

I disagree that embedding variables is simpler, however. Using bind parameters removes the necessity to have to escape particular characters in the data that's being inserted, while also taking care of the correct way to quote various types of values. More importantly, however, is the security aspect. Prepared statements with placeholders are the single greatest protection against SQL injection attacks.

 

[jimineep]

I heard that too about the security! But really I'm doing it because i had many characters to escape I expect it is more efficient than substition commands to escape the special characters

but i still cant get it to work!!!

 

[TonyGroves]

That should be right now; sprintf "returns the current date to the variable instead of printing it to screen".

 

[TonyGroves]

That last post was written before ishnid's post but I didn't click Submit fast enough.

The security comment has been noted; I always embed variables but use a string-cleanup function first.

 

[jimineep]

ok its printing fine to screen now but for some reason it still wont go into the database which remains 0000-00-00

oh and also the date has become 1900-01-00 :S

my $dateImported=(sprintf "%d-%02d-%02d",$year+1900,$mon+1,$day); #COOL CODE, BUT NOT WORKING (SHOULD RETURN INTEGER NOT STRING? NOT SURE WHY THIS IS)

print " testing $dateImported testing2";

#open(OUT, '>output.txt') or die "couldnt open";

#$dump = Dumper($query);
#print Dumper($query);
	 #all important mySQL to put into the database what is needed 
 my $sql1 = "INSERT INTO user_BP_Files (fileName,userName,bioPaxfile,dateImported) VALUES (?,?,?,?)"; 

 #now prepare and execute 
 my $sth1 = $dbh->prepare( $sql1 ); 
                    
$sth1->bind_param(1, $fileName);
$sth1->bind_param(2, "userName");
$sth1->bind_param(3, $BPfile);
$sth1->bind_param(4, $dateImported); #NOT WORKING? NOT SURE WHY

 
 $sth1->execute();

Any ideas???

 

[jimineep]

HAHA I got fed up so had a brief look at some mysql and found out i can just add now() mysql funtion and do away with trying to work out the date!!!!

cheers for ure help though, I've noted the sprintf vs printf though i expect thats prob quite basic stuff :$

 

[stevexff]

I was going to suggest you use the SQL 'constant' CURRENT_DATE in your INSERT statement - it has the advantage of being portable across DBMSs...

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]