Solved

problem in DBD::ODBC

Posted on 2000-04-26
13
299 Views
Last Modified: 2013-12-25
i have installed the dbi and DBD::ODBC module in my computer.  Now i am trying to update and add record to my logintable.  There record cannot be added and updated due to don't know what reason.  Here is my code :


       my $dbh = DBI->connect('DBI:ODBC:Login','yeeling','111');
       $sql= "INSERT INTO LoginTable (LoginCount,UserName,Password,Email,Folio,LoginDate) VALUES (0,'" .$UserName ."','" . $Pass . "','" . $Email . "','" . $Folio . "'," . scalar localtime(time). ")";
       $sth = $dbh->prepare($sql);
       $sth->execute($sql) ||
          die "unable to save record";


i even have tried other way , the code is :

     my $sql = qq/
               INSERT INTO LoginTable
               (LoginCount, UserName, Password, Email, Folio, LoginDate)
                VALUES
               (0,$UserName,$Pass, $Email, $Folio, scalar localtime(time))
               /;
     $sth = $dbh->prepare( $sql);
     my $rv = $dbh->do($sql) || die print "Error while updating";


The Select statement works well.
please help me !

0
Comment
Question by:riyugie
  • 5
  • 3
  • 3
  • +1
13 Comments
 
LVL 10

Expert Comment

by:makerp
ID: 2750504
that date you are putting ! if your on access the date must be wrapped in #'s, if sql server then you must use the convert function.

access.

#12/12/2000#

now the date you attempt to enter must be in US format, i dont know why but it does even if you date settings specify (dd/mm/yyyy).

now if your attempting to enter the current date do this

UserName,Password,Email,Folio,LoginDate) VALUES (0,'" .$UserName ."','" . $Pass . "','" . $Email . "','" . $Folio . "',Date()")";

Date() is a vb fucntion that works in access/sql server. do not wrap this in #'s only do this for dates you build up and want to enter

Hope this helps
       

 
0
 
LVL 16

Expert Comment

by:maneshr
ID: 2751203
Also check if the user that you are logging in with has the right to insert, update & delete from the database.

0
 

Author Comment

by:riyugie
ID: 2753663
I have tried the statement u suggested , however the new record still cannot be added into the table.  

For you information there is an auto increment field in the table - the LoginID , do i need to include this field in my sql statement as well??

I have tried to construct the sql statement to look loke this

$sql= "INSERT INTO LoginTable (LoginID, LoginCount,UserName,Password,Email,Folio,LoginDate) VALUES (,0,'" .$UserName ."','" . $Pass . "','" . $Email . "','" . $Folio . "',Date(),Date())";

$sth = $dbh->prepare($sql);
$sth->execute($sql) ||
     die "unable to save record";

is it a valid sql statement??

i know that the date might be the main cause for this problem because i have tried a sql UPDATE statement that update the LoginCount field and LoginDate it cannot be executed as well.

But the Update statement works when i choose to NOT update the LoginDate field.

Hope u can give more suggestion on  this!  Thank you makerp and maneshr.
0
 
LVL 10

Accepted Solution

by:
makerp earned 50 total points
ID: 2754433
no take the auto field entry out of your insert. dont list it in the first lot of () or the values ()

"INSERT INTO LoginTable ( LoginCount,UserName,Password,Email,Folio,LoginDate) VALUES (0,'" .$UserName ."','" . $Pass . "','" . $Email . "','" . $Folio . "',Date())";

0
 
LVL 16

Expert Comment

by:maneshr
ID: 2755325
i would suggest you try this....


Before the $sth = $dbh->prepare($sql);  statement
add the following

print $sql,"\n";
exit;


Now you can see the actual SQL statement that will be executed in your browser.

Next copy that SQL statement and try executing it from the Command Prompt. if the SQL fails you know the reason could be due to lack of proper params to the insert.

If the insert succeeds from the prompt, you know the problem could be due to insufficient insert permissions.

Hope that helps.
0
 
LVL 6

Expert Comment

by:christopher sagayam
ID: 2756318
Have you tried

$t = scalar localtime(time) ;

$sql= "INSERT INTO LoginTable(LoginCount,UserName,Password,Email,Folio,LoginDate) VALUES (0,'$UserName','$Pass','$Email','$Folio','$t')";
       
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:riyugie
ID: 2757818
i found that even the sql seems to not executed for the xitami server, perl or whatever.
Because i have tried to create one simple table that have 2 text fields inside and try to INSERT into the table new record from the cgi page .  The record cannot be added as well.

Do anybody knows what is the cause of this problem??
0
 
LVL 16

Expert Comment

by:maneshr
ID: 2763020
can you insert the record from the command prompt??
0
 
LVL 6

Expert Comment

by:christopher sagayam
ID: 2763256
Well then it is definetly permission problem ..

what type of dsn are your using ?

System DSN or File DSN ?

What database are you using ? Ms-Access or Ms-SQL-server ?
0
 

Author Comment

by:riyugie
ID: 2767781
I have set it to be the system dsn and it is MS ACCESS database.

and i would like to know also on how to access the dbase(*.dbf) database???


i am also having problem with the pagfe redirection thing.. This is what i want to do:

firstly, i try to validate and check whether the username and password is valid.  AND if it is a valid login, i would like to redirect the page to let the user to view their inbox or else the user will need to reenter the login info.


Can i know how to redirect the page to the inbox page??

i have tried to use the CGI.pm object

print $query->redirect(-location=>"http://localhost/cgi-bin/home/inbox.cgi"

however i does not seem to work, i want to redirect it only after verified that the login is valid , not straight away redirect it whenever the page is browsed.


thank you


0
 
LVL 6

Expert Comment

by:christopher sagayam
ID: 2768199
You can access a dbf file using

DBD::Xbase or

even ODBC driver for dbf files ...


You can use the redirect command ONLY if you have NOT used any print $query->header .. or any print "" statements before the redirect query in your program ...


Try

print $query->redirect("http://localhost/cgi-bin/home/inbox.cgi")

and It will work ..



BUT The below will not work !
 
print $q->header;

print "hello" ;

print $query->redirect("http://localhost/cgi-bin/home/inbox.cgi")





0
 

Author Comment

by:riyugie
ID: 2771542
how to solve this problem?
0
 

Author Comment

by:riyugie
ID: 2771544
Adjusted points from 30 to 50
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

If you get a (Blue Screen of Death), your system writes a small file called a minidump. Your first step is to make certain your computer is setup to record memory dumps. Right click My Computer, choose properties. Click on the advanced tab, an…
This tutorial will discuss the log-in process using WhizBase. In this article I assume you already know HTML. I will write the code using WhizBase Server Pages, so you need to know some basics in WBSP (you might look at some of my other articles abo…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now