Beefy Boxes and Bandwidth Generously Provided by pair Networks
Your skill will accomplish
what the force of many cannot

DBI insert statement

by cbtshare (Monk)
on Nov 21, 2016 at 23:09 UTC ( #1176287=perlquestion: print w/replies, xml ) Need Help??

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

Hello All, I writing a script pull content from one DB and insert into another, but my issue is that after the script runs, I get no errors but the data is not inserted into the table of the destination DB.Any ideas? #select statement
my $table_results= $dbh2->prepare("SELECT * from $feed_table WHERE ent +ry_time >= $time"); $table_results->execute();
#insert statement
$sth_insert = $dbh3->prepare("INSERT IGNORE INTO $feed_table (id_code, +entry_time,parent_id_code,author_name,author_code,author_url,author_i +mage_url,entry_url, entry_types,status_code,entry_text,entry_data,last_update,pull_time,qu +eue_code) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"); while (my @insert = $table_results->fetchrow_array()) { #print("@insert", "\n"); $sth_insert->execute(@insert); }

Replies are listed 'Best First'.
Re: DBI insert statement
by poj (Abbot) on Nov 22, 2016 at 07:51 UTC
    I get no errors

    That is probably because you are using INSERT IGNORE, try without IGNORE

    From the MySQL docs

    If you use the IGNORE keyword, errors that occur while executing the INSERT statement are ignored. For example, without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted. With IGNORE, the row is discarded and no error occurs. Ignored errors may generate warnings instead, although duplicate-key errors do not.

    If the dbh2 and dbh3 connections are the same,you will be selecting and inserting records from the same table so duplicates are likely.

    Use of uninitialized value $insert[2] in join or string at /home/rdsco line 108. Use of uninitialized value $insert[14] in join or string at /home/rdsc line 108.

    Those warnings mean you are missing data for parent_id_code and queue_code. Are NULLs allowed in these fields ?.

Re: DBI insert statement
by erix (Prior) on Nov 22, 2016 at 03:57 UTC
    Any ideas?

    This kind of unwillingness on the part of databases often means the omission of commit (be it explicit or as autocommit).

    Try a $dbh->commit; at the end (or every few thousand inserts).

    If that's not it, a small self-sufficient testcase would help (but including connection params, create table, some minimal data).

      thank you tried, that but got this message: "commit ineffective with AutoCommit enabled "
Re: DBI insert statement
by GrandFather (Saint) on Nov 22, 2016 at 00:17 UTC

    Generate a small test script using perhaps DBD::SQLite that demonstrates your issue. With no idea of the content or configuration of the databases and no error logs there's not a lot can do. See I know what I mean. Why don't you? for hints about how to create your test script.

    Premature optimization is the root of all job security
      thank you, I am not sure what the test script would do or prove, everything works, and I can get data back, but my insert is the issue.
        everything works

        Really? Then what are you asking? If the insert is the issue, write a small self contained script that demonstrates the issue with the insert.

        Actually, there are two possibilities, either the insert is failing or you are failing to validate that the insert succeeded. We can't tell anything about either of those options based on the information we have so far.

        Premature optimization is the root of all job security

        thank you, I am not sure what the test script would do or prove, everything works, and I can get data back, but my insert is the issue.


        Imagine you're talking to an auto mechanic on the telephone

        You say car works , gas/battery/keys all work, car wont start ... you text a picture of the car ...

        Mechanic says bring the car into the shop

        Reproducing the problem in 20 lines is the equivalent of showing the mechanic the car, the least frustrating way to solve impossible problems

Re: DBI insert statement
by Marshall (Canon) on Nov 22, 2016 at 04:22 UTC
    The first step in writing Perl to do a complex SQL interaction is to write SQL statement(s) from your DB's command line that does what you want in SQL, at least for one record.

    The Perl DBI is fantastic and SQL statements translate very directly into Perl.

Re: DBI insert statement
by stevieb (Canon) on Nov 21, 2016 at 23:23 UTC

    I see you've been here exactly a year to the day, congrats :)

    Can you please show us your connect() statement for the DB, and also, uncomment the print statement inside that while loop and let us know if you are actually retrieving any results.

      Thank you for your the felicitation.
      #connect prod
      my $dbh2 = DBI->connect( $from_feed_dsn,$from_feed_user, $from_feed_pass, { RaiseError => 1,PrintError => 1 }) or die ( "Couldn't connect to database: " . DBI->errstr );
      #connect staging
      my $dbh3 = DBI->connect( $from_feed_dsn, $from_feed_user, $from_feed_p +ass, {RaiseError => 1, PrintError => 1 }) or die ( "Couldn't connect +to database: " . DBI->errstr );

      Yes, there is data being printed, so the select statement does find data, but print I do see an error,throughout printed results of the select statement, not sure if its just due to formatting: Use of uninitialized value $insert2 in join or string at /home/ line 108. Use of uninitialized value $insert14 in join or string at /home/ line 108.

      [{"id":"2188595841sws21","length":7,"name":"OuiSurf","offset":46,"type +":"page"},{"id":"28193822473sw4","length":17,"name":"Old Style Pilsne +r","offset":58,"type":"page"}],"media":{"image":{"height":555,"src":" +https:\/\/\/v\/t1.0-9\/s720x720\/14642467_117801 +9732256761_147125933148544115_n.jpg?oh=ad1bd37dd9e86fd463ff628455b0ef +5b&oe=58B9A92E","width":720}},"target":{"id":"1178019732256761","url" +:"https:\/\/\//photos\/gm.809764315832456\/1178019732 +256761\/?type=3"},"type":"photo","url":"https:\/\/\\/ +photos\/gm.809764315832456\/1178019732256761\/?type=3"}]}} 2016-10-11 + 13:18:31 2016-11-16 18:53:34
Re: DBI insert statement
by tweetiepooh (Hermit) on Nov 22, 2016 at 15:40 UTC

    Looking at the examples it seems the structures of the tables is the same, you are inserting a subset to the new table. If so and there isn't some other parsing happening can't you offload the whole to the database

    insert into targettable select * from sourcetable where ...

    If target doesn't need to permanently exist you can

    create targettable as (select * from sourcetable where...)

    Then drop the new table at end of run.

      OP states they want to insert the data into another DB, so a pure SQL solution is possible only if both databases are serviced by the same server (and are more or less just different namespaces).

      Update: Just to make it clear: I concur with tweetiepooh that in setups, where it is possible at all, you can bet important body parts on it being the most efficient solution, too.

        Don't know enough about their setup but Oracle allows DB links between databases, even on different servers and the idea would still work, you just need to tell the SQL that one database is over a link. This still offloads the work to the database, maybe even better if both servers are in a data centre as traffic doesn't flow from one DB to Perl to other DB (OK Perl maybe at same place as one of the DB's).

        Again with Oracle you can use views and synonyms to make the remote table appear to be local so you only need to connect to one database. When I was doing something similar I had to create the link on the fly because I was dealing with multiple remote databases and wanted to use the same link name so that is also possible. This also meant that when script wasn't running the link was absent too so users couldn't access the remote databases.

Re: DBI insert statement
by Marshall (Canon) on Nov 23, 2016 at 15:39 UTC
    I still like my suggest at Re: DBI insert statement - Make sure that you have the correct SQL from the DB command line. That takes Perl "out of the equation". After that works, the issue becomes how to make Perl do what you already know works.

    I looked at other answers in this thread. You keep saying that "insert doesn't work". Debug 101 to me would be "what the heck does insert say that it did?

    For a non-SELECT statement, execute returns the number of rows affected, if known. If no rows were affected, then execute returns "0E0", which Perl will treat as 0 but will regard as true. Note that it is not an error for no rows to be affected by a statement. If the number of rows affected is not known, then execute returns -1.
    returns a value. What is it?
    my $rv = $sth_insert->execute(@insert); print "insert says: $rv/n";

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1176287]
Approved by stevieb
Domain Nodelet?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others about the Monastery: (3)
As of 2022-09-28 23:35 GMT
Find Nodes?
    Voting Booth?
    I prefer my indexes to start at:

    Results (125 votes). Check out past polls.