Обсуждение: Create tables using Perl DBI

I've been trying to learn how to use PostgreSQL and Perl together on a test
machine at work.  My problem is that I can not seem to get it working.  The
documentation on the subject don't seem to be that in-depth and tutorials
seem lacking :)

Below is my third or forth try at getting my database to connect and create
a table.  I've included the error code for the code snippet.  Any help would
be GREATLY appreciated.


=== Begin Code ===

#!/usr/bin/perl

# Use the Database Binding Interface
use DBI;

# We want to use the  database which is kwsclient
$dbh = DBI->connect("dbi:Pg:dbname=kwsclient","db","PASSWORD")or die "Connecting: $DBI::errstr";

#abcdefghijklmnopqrstuvwxyz
$sth = $dbh->prepare("create table customer (ID serial PRIMARY KEY, name
text NOT NULL,                     email text NOT NULL, phone text NOT NULL, fax text
DEFAULT, address                 text NOT NULL, address2 text DEFAULT, zip text NOT
NULL, city text                 NOT NULL, state text NOT NULL, country text NOT NULL")        or die "Could not Create
Table:$DBI::errstr";
 
$sth->execute;
#disconnect from the database.
$dbh->disconnect();
# END

=== End Code ===

=== Error Message ===
DBD::Pg::st execute failed: ERROR:  parser: parse error at or near ","


Thanks for the help.

David Coley
davidcoley@home.com
http://www.davidcoley.com

Hi,

> $sth = $dbh->prepare("create table customer (
>  ID serial PRIMARY KEY,
>  name text NOT NULL, 
>  email text NOT NULL,
>  phone text NOT NULL,
>  fax text DEFAULT, 
[...]

there is a default text required I guess!


bye Alvar



-- 
Alvar C.H. Freude  |  alvar.freude@merz-akademie.de
   Demo: http://www.online-demonstration.org/  |  Mach mit!
Blast-DE: http://www.assoziations-blaster.de/   |  Blast-Dich-Fit
Blast-EN: http://www.a-blast.org/               |  Blast/english

>$sth = $dbh->prepare("create table customer (ID serial PRIMARY KEY, name
>text NOT NULL,      email text NOT NULL, phone text NOT NULL, fax text
>DEFAULT, address     text NOT NULL, address2 text DEFAULT, zip text NOT
>NULL, city text     NOT NULL, state text NOT NULL, country text NOT NULL")
>or die "Could not Create Table: $DBI::errstr";

It looks like you're missing a ")" in there somewhere.
Parse error usually = syntax error.

--
Jeremy Buchmann
System Admin/Database Programmer
Wells Gaming Research

----------
>From: "David Coley" <davidcoley@home.com>
>To: <pgsql-interfaces@postgresql.org>
>Subject: [INTERFACES] Create tables using Perl DBI
>Date: Mon, Nov 20, 2000, 12:50 PM
>

> I've been trying to learn how to use PostgreSQL and Perl together on a test
> machine at work.  My problem is that I can not seem to get it working.  The
> documentation on the subject don't seem to be that in-depth and tutorials
> seem lacking :)
>
> Below is my third or forth try at getting my database to connect and create
> a table.  I've included the error code for the code snippet.  Any help would
> be GREATLY appreciated.
>
>
> === Begin Code ===
>
> #!/usr/bin/perl
>
> # Use the Database Binding Interface
> use DBI;
>
> # We want to use the  database which is kwsclient
> $dbh = DBI->connect("dbi:Pg:dbname=kwsclient","db","PASSWORD")
>  or die "Connecting: $DBI::errstr";
>
> #abcdefghijklmnopqrstuvwxyz
> $sth = $dbh->prepare("create table customer (ID serial PRIMARY KEY, name
> text NOT NULL,      email text NOT NULL, phone text NOT NULL, fax text
> DEFAULT, address     text NOT NULL, address2 text DEFAULT, zip text NOT
> NULL, city text     NOT NULL, state text NOT NULL, country text NOT NULL")
>    or die "Could not Create Table: $DBI::errstr";
> $sth->execute;
> #disconnect from the database.
> $dbh->disconnect();
> # END
>
> === End Code ===
>
> === Error Message ===
> DBD::Pg::st execute failed: ERROR:  parser: parse error at or near ","
>
>
> Thanks for the help.
>
> David Coley
> davidcoley@home.com
> http://www.davidcoley.com
>
> 

On Tue, Nov 21, 2000 at 12:38:41PM -0800, Jeremy Buchmann wrote:
> 
> It looks like you're missing a ")" in there somewhere.
> Parse error usually = syntax error.
> 

Good eye Jeremy. David, add a right paren between the last NULL and the close
quote. Should work then.

Ross

-- 
Open source code is like a natural resource, it's the result of providing
food and sunshine to programmers, and then staying out of their way.
[...] [It] is not going away because it has utility for both the developers 
and users independent of economic motivations.  Jim Flynn, Sunnyvale, Calif.

Hum... I tried adding that extra ) at end of the prepare:

$sth = $dbh->prepare("create table customer (ID serial PRIMARY KEY, name
text NOT NULL, email text NOT NULL, phone text NOT NULL, fax text DEFAULT,
address text NOT NULL, address2 text DEFAULT, zip text NOT NULL, city text
NOT NULL, state text NOT NULL, country text NOT NULL)")   or die "Could not Create Table:
$DBI::errstr";$sth->execute;#disconnectfrom the database.$dbh->disconnect();
 

>
> It looks like you're missing a ")" in there somewhere.
> Parse error usually = syntax error.
>

Still the same error:

DBD::Pg::st execute failed: ERROR:  parser: parse error at or near ","

David Coley
davidcoley@home.com
http://www.davidcoley.com

Hmm, after reformating your SQL so I can read it, I found this:

"create table customer (         ID serial PRIMARY KEY,         name text NOT NULL,         email text NOT NULL,
phone text NOT NULL,
 
--->      fax text DEFAULT,         address text NOT NULL,
--->      address2 text DEFAULT,         zip text NOT NULL,         city text NOT NULL,         state text NOT NULL,
    country text NOT NULL)"
 

What are you expecting to happen with DEFAULT with nothing after it?
That's the 'at or near ","' part. Either drop the DEFAULT or add a text
to default to.

Are you perhaps looking for NULL? (as apposed to NOT NULL?) It's allowed
in the grammar, but is the default, so you don't need it.

Ross

On Tue, Nov 21, 2000 at 04:50:54PM -0500, David Coley wrote:
> Hum... I tried adding that extra ) at end of the prepare:
> 
> $sth = $dbh->prepare("create table customer (ID serial PRIMARY KEY, name
> text NOT NULL, email text NOT NULL, phone text NOT NULL, fax text DEFAULT,
> address text NOT NULL, address2 text DEFAULT, zip text NOT NULL, city text
> NOT NULL, state text NOT NULL, country text NOT NULL)")
>     or die "Could not Create Table: $DBI::errstr";
>  $sth->execute;
>  #disconnect from the database.
>  $dbh->disconnect();
> 
> >
> > It looks like you're missing a ")" in there somewhere.
> > Parse error usually = syntax error.
> >
> 
> Still the same error:
> 
> DBD::Pg::st execute failed: ERROR:  parser: parse error at or near ","
> 
> David Coley
> davidcoley@home.com
> http://www.davidcoley.com
> 

Wow... it worked.  See what happens when you learning you make little
mistakes.  Should have realized that earlier when someone else suggested.
Thanks a lot everyone.

DC

>>>

Hmm, after reformating your SQL so I can read it, I found this:

"create table customer (         ID serial PRIMARY KEY,         name text NOT NULL,         email text NOT NULL,
phone text NOT NULL,
 
--->      fax text DEFAULT,         address text NOT NULL,
--->      address2 text DEFAULT,         zip text NOT NULL,         city text NOT NULL,         state text NOT NULL,
    country text NOT NULL)"
 

What are you expecting to happen with DEFAULT with nothing after it?
That's the 'at or near ","' part. Either drop the DEFAULT or add a text
to default to.

Are you perhaps looking for NULL? (as apposed to NOT NULL?) It's allowed
in the grammar, but is the default, so you don't need it.

Ross