Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer
 
PerlMonks  

Need help to correct a simple script

by terrykhatri531 (Novice)
on Jan 07, 2013 at 15:27 UTC ( #1012045=perlquestion: print w/ replies, xml ) Need Help??
terrykhatri531 has asked for the wisdom of the Perl Monks concerning the following question:

Hi, I am no perl programmer however I need to create a script that creates a desired number of tables with desired number of inserts per table, here is the code :
#!/usr/bin/perl use DBI; $tables=100; $rows=10000; $count=1 if (!($dbh = DBI->connect ('DBI:Oracle:orcl',"hr","hr"))) { die ("Failed to connect to database: " . DBI->errstr); }; $sql = "CREATE TABLE test_data($count)(". "id PRIMARY KEY,". "group_id NOT NULL,". "created_at NOT NULL,". "text NOT NULL) AS ". "SELECT ". "rownum,". "MOD(rownum, 5),". "TO_DATE('1-jan-07', 'dd-mon-yy') + INTERVAL ' +1' MINUTE * rownum,". "CAST ('xyz' || rownum AS VARCHAR2(50)) ". "FROM dual ". "CONNECT BY LEVEL <= $rows"; while ($count<$tables) { if (!($dbh->do ($sql))) { die ("Failed to insert row: " . DBI->errstr); }; $count++; } $dbh->commit; $dbh->disconnect;

It should create tables from test_data1 to test_data100

Can someone be please kind enough to correct the code.

Thank you very much in advance.

Rgds

Terry

Comment on Need help to correct a simple script
Download Code
Re: Need help to correct a simple script
by blue_cowdawg (Prior) on Jan 07, 2013 at 15:36 UTC

    so... what errors are you seeing?


    Peter L. Berghold -- Unix Professional
    Peter -at- Berghold -dot- Net; AOL IM redcowdawg Yahoo IM: blue_cowdawg
Re: Need help to correct a simple script
by marto (Chancellor) on Jan 07, 2013 at 15:42 UTC

    Hi, at first glance

    $count=1

    Should be:

    $count=1;

    Also Oracle will complain about that SQL statement:

    CREATE TABLE test_data($count)(

    Shoud be:

    CREATE TABLE test_data$count(

    See Use strict and warnings, Create Table and the DBI documentation. Pay attention to the section on placeholders and bind variables.

    Update: At second glance there's other problems. Let me fix this and update.

    Update 2:

    With Oracle (and perhaps other databases) Bind variables can be used for DML (Select, insert) but not DDL (Create, Alter, Drop) statements. (See What are the difference between DDL, DML and DCL commands?). This means that my previous advice on placeholders and bind variables won't help you much, though the topic is still worth reading. I'd try to create a stored procedure within Oracle to do the table create, and call if from your Perl script passing in the appropriate table name.

      With respect to Update 2, you also can't use bind variables in respect of table names - see comment below.
      A Monk aims to give answers to those who have none, and to learn from those who know more.
Re: Need help to correct a simple script
by space_monk (Chaplain) on Jan 07, 2013 at 15:57 UTC
    I like to write complex SQL statements as HEREDOCs, as removing the Perl syntax from within the SQL syntax makes for much easier reading...

    i.e. it is much easier to read your CREATE TABLE statement when written as....

    my $sql = <<EOF; CREATE TABLE test_data$count( id PRIMARY KEY, group_id NOT NULL, created_at NOT NULL, text NOT NULL) AS SELECT rownum, MOD(rownum, 5), TO_DATE('1-jan-07', 'dd-mon-yy') + INTERVAL ' +1' MINUTE * rownum, CAST ('xyz' || rownum AS VARCHAR2(50)) FROM dual CONNECT BY LEVEL <= $rows EOF
    This incorporates martos suggestions above, and I'll update if anyone spots any other issues.

    A Monk aims to give answers to those who have none, and to learn from those who know more.
Re: Need help to correct a simple script
by space_monk (Chaplain) on Jan 07, 2013 at 16:08 UTC
    The SQL needs to be declared in the loop, as you're changing the variable $count each time. Consider the more idiomatic
    foreach my $count (1..$tables) { my $sql =<<EOF; .... EOF die ("Failed to insert row: " . DBI->errstr) if (!($dbh->do ($sql) +)); }

    Unfortunately you can't prepare and execute this statement with bound variables, as the table name is formed from one of the variables.

    A Monk aims to give answers to those who have none, and to learn from those who know more.
      Hi

      Thank you for your quick responses, I adjusted my script accordingly and now it works like a charm.

      Rgds

      Terry

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1012045]
Approved by marto
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others chilling in the Monastery: (5)
As of 2014-08-23 17:06 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The best computer themed movie is:











    Results (175 votes), past polls