To store user login info into sqlite3 using perl

dhriti · 08-06-2012, 04:44 AM

Hi

I need to connect to SQLite db n store user login info using perl script.
The DB and the table is created.
Now I need to add a part to the code so that it shows the followings

1) 0 - No error
2) -1 - record already present, if one of the primary values are already there.

3) -2 - If any of the information is invalid or incomplete,

Please could anyone help me with hints or code?
I am new to programming.

anomie · 08-07-2012, 02:31 PM

Cool project, but not a trivial one for someone new to programming. (This means you've never programmed in Perl, SQL, or any language?)

It's going to be difficult to help you without any code context, so you may want to post what you've tried so far.

dhriti · 08-08-2012, 02:04 AM

Assuming there is a db called test.db with table Cars in SQLite.

#!/usr/bin/perl

use strict;
use DBI;

my $dbh = DBI->connect(
"dbi:SQLite:dbname=test.db",
{ RaiseError => 1 }
) or die $DBI::errstr;

my $sth = $dbh->prepare( "SELECT * FROM Cars WHERE Id=1" );
$sth->execute();

my ($id, $name, $price) = $sth->fetchrow();
print "$id $name $price\n";
........
.........

$sth->finish();
$dbh->disconnect();

Now the condition is for each new entry to the DB table shouldn;t have same name or price.
If it matches, it should show error, n shouldn't give access to entry to the table.
what should be the logic?

theNbomr · 08-08-2012, 09:39 AM

First, your code looks pretty good for a non programmer. It would be a lot easier to read if it were posted in [CODE] [/CODE] tags to preserve the formatting. Please edit your posts accordingly.
It seems to me that you you should be loading from the existing Db, those records that match any one of the fields of interest. If the returned dataset is non-empty, then you know that at least one record exists matching one of your fields, and you can exit with a '-1' exit code or return value. You will need to have something to search for, which can be specified by parameters passed from the commandline.
Code that looks something like this may help.

Code:

my $inputPrice = $ARGV[0];
my $inputName = $ARGV[1];

#
#  Compose a query string (assuming some table column names here)
#
my $queryString = "SELECT * from Cars WHERE Name like '%$inputName%' or Price like '%$inputPrice%'";

It isn't clear what is meant by 'information is invalid or incomplete'. I guess that one criterion is that both of the 'name' and 'price' arguments must be non-empty. Perhaps you have some criteria that evaluates the input for well-formedness, but you will need to expand on what those criteria are.

--- rod.

dhriti · 08-08-2012, 01:31 PM

Thanks a lot. I got some idea. Yes its a existing DB and about the 3rd criterion what you have told is correct.
I will try now. Thank you both