Win has asked for the wisdom of the Perl Monks concerning the following question:

Dear Monks

There are a number of ways that I could do this. I would like to do it on the fly without printing to file. Am I on the right lines? 

    my $sthH= $dbh->prepare("SELECT code from Disease_Types") or die "
+Couldn't prepare query: ".$dbh->errstr;
    $sthH->execute or die "Couldn't execute query: ".$sthG->errstr;;
    my @names =();
    while (my ($name) = $sthH->fetchrow_array) {
      push @names, $name;
    }
    my @new_name = ();
    foreach (@names){
      my $name = $_;
      my $cat_name = "A"."$name";
     
      push @new_name, $cat_name;
      
    }
    #print @new_name;
    
    foreach (@new_name){
      my $sthI = $dbh->prepare('INSERT into Disease_Types (Adapted_cod
+e) Values ("$_")');
    }

[download]

Replies are listed 'Best First'.
•Re: Inserting values in to table with DBI
by merlyn (Sage) on Dec 08, 2003 at 12:34 UTC
    Can't you do it entirely database-side? Or do you literally need to have the "A" be something else for each entry?

    In standard SQL, this is either: 

    UPDATE disease_types
SET adapted_code = "A" || code;

    [download]
    if you're trying to modify the records in place, or if you really wanna create a whole new set of records, more like: 
    INSERT INTO disease_types (adapted_code)
SELECT "A" || code FROM disease_types;

    [download]

    You can execute either of these with a $dbh->do(...) operation.

    -- Randal L. Schwartz, Perl hacker
    Be sure to read my standard disclaimer if this is a reply.

[reply]
[d/l]
[select]
      The MS SQL Standard 
          my $sthH = $dbh->prepare("INSERT INTO Disease_types (Adapted_code)
SELECT ('A'+code) FROM Disease_types") or die "Couldn't prepare query:
+ ".$dbh->errstr;
    
    $sthH->execute() or die "Couldn't execute query: ".$sthH->errstr;

      [download]
      gives.... 
      
DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver][SQL 
+Server]Str
ing or binary data would be truncated. (SQL-22001)
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been 
+terminated
. (SQL-01000)(DBD: st_execute/SQLExecute err=-1) at Creating_table.pl 
+line 389
.

      [download]

      I know that I need to read up on SQL .... but when the Microsoft page: 

      http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlr
+ef/ts_operator_3qov.asp

      [download]
      .... tells me this, I just stuck.
[reply]
[d/l]
[select]
Re: Inserting values in to table with DBI
by cchampion (Curate) on Dec 08, 2003 at 13:00 UTC

    Didn't you ask the same question at CONCAT Perl or SQL?

    Did you listen to the advice that was offered there?

    It seems that you know very little Perl, and even less SQL. Your questions look like you are asking while keeping your head in a cloud of smoke. If you don't have a clear vision of your problems, neither can we.

    I am sorry to say it, but what you need to do is go through a good SQL tutorial, practice a bit, and then come back asking your question, if you still have one. ;-)

[reply]
Re: Inserting values in to table with DBI
by Roger (Parson) on Dec 08, 2003 at 13:04 UTC
    Why not an SQL only solution? something along the lines of ...

    my $sth = $dbh->prepare( qq{
  INSERT into Disease_Types(Adapted_code)
  SELECT CONCAT("A", code) from Disease_Types
}) or die "SQL prepare error!";

$sth->execute();

    [download]
[reply]
[d/l]
    A reply falls below the community's threshold of quality. You may see it by logging in.
Re: Inserting values in to table with DBI
by mpeppler (Vicar) on Dec 08, 2003 at 15:58 UTC
    In another node you write that you get the following error: 
    String or binary data would be truncated, terminating

    [download]
    when executing 
    update <table>
   set Adapted_code = 'A' + code
...

    [download]
    This means that "Adapted_code" is not large enough to hold the value of "code" plus one extra character.

    I haven't checked your other nodes, so I don't remember off-hand what the definition of the two columns are, but "Adapted_code" needs to be at least one char wider than the widest string in "code".

    If I remember correctly you want to UPDATE each row of your table, not insert NEW rows. That is NOT what your code snippet above does - it tries to insert NEW rows into the table using default values for all the columns except "Adapted_code".

    Michael

[reply]
[d/l]
[select]
Re: Inserting values in to table with DBI
by dws (Chancellor) on Dec 08, 2003 at 17:43 UTC
    A side note: use strict; is your friend. It helps catch things like 
    $sthH->execute or die "Couldn't execute query: ".$sthG->errstr;;
    ^                                                ^

    [download]
    Problems like this can really confound attempts to debug failures.

[reply]
[d/l]
[select]
Re: Inserting values in to table with DBI
by kodo (Hermit) on Dec 08, 2003 at 12:38 UTC
    I'd suggest you to have a look at perldoc DBI and DBI FAQ and if you still can't get your task done then, learn howto use google.

    greetings,


    kodo
[reply]

Back to Seekers of Perl Wisdom