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

All i have a strange issue, when i try and enter some strings into a database using DBD::Oracle and DBI i get the PIPE symbol entered into the same column.

        if($debug){&writelog(1,"INFO: Sent Order Order id = $msgsendid
+: \"$sendclient,$sendordid,$sendlotsfill,$sendfillprice,$sendlotsact,
+$sendtransno,$send
dborderid,$sendlotsordered,$sendamedorderid,$sendorderprice,$sendtrade
+rid,$sendbuysell,$sendconsid,$sendexchange,$sendfilltype,$sendlotssti
+llopen,$sendmaturi
ty,$sendordertype,$sendaction,$sendacc,$sendtimetransmis,$appiamsgdiff
+,$appiamsgdiffsecs\" Full String");}
        # Now we have all the data we insert into the database table
        my $sql = qq{INSERT INTO $dbtable VALUES(GFMIS.FIXCUSTOMERPERF
+_SEQ.nextval,'$recclient','$recordid','$senddborderid','$reclotsorder
+ed',
                                                                      
+     '$recorderprice','$rectraderid','$recbuysell','$recconsid','$rec
+ordertype',
                                                                      
+     '$recaction','$recacc','$rectimetransmis','$sendlotsfill','$send
+fillprice',
                                                                      
+     '$sendfilltype','$sendtimetransmis','$appiamsgdiff','$appiamsgdi
+ffsecs')};
        my $sth = $dbh->prepare($sql);
        $sth->execute();
        $sth->finish;

[download]


The log shows this before it enters it.
20-12-2005 11:38: INFO: Sent Order Order id = TESTER-5-send: "TESTER,f
+ud646_20051118,0.0000,0.00000000,0.0000,1049,1317260662,5.0000,0,1461
+0.065,DESRISKGATEW
AY,Buy,ANYTHING,0,Ack,5.0000,200512,Symbol,Report,ATOP11,20051118-05:0
+8:10,1812,1.812" Full String

[download]

But in the database my columns look like this.
FIXID | CLIENTNAME | CLIENTORDERID |
8 | TESTER | fud646_20051118| |

So the "fud646_20051118|" should be "fud646_20051118" why is the pipe getting inserted. ?

Replies are listed 'Best First'.
Re: Pipe tag being entered into database
by tirwhan (Abbot) on Dec 20, 2005 at 11:56 UTC

    You should use placeholders when prepare()ing a DB insertion. Try this:

    my $sql = qq{INSERT INTO $dbtable VALUES(GFMIS.FIXCUSTOMERPERF_SEQ.nex
+tval,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)};
my $sth = $dbh->prepare($sql);
$sth->execute($recclient,
              $recordid,
              $senddborderid,
#              ... (and so on for the rest of your variables)
              );

    [download]

    This is cleaner, usually more efficient and also avoids lots of quoting issues. (Note, this is probably not the cause of your problem, as holli explains above, but could save you grief in the future.)


    Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible, you are, by definition, not smart enough to debug it. -- Brian W. Kernighan
[reply]
[d/l]
Re: Pipe tag being entered into database
by holli (Abbot) on Dec 20, 2005 at 11:55 UTC
    There is nothing in your code that would explain this. But I strongly assume, that the pipes are in your data. Dump your variables to screen to see if that is true.

    Maybe you split a pipe separated text file and split it wrong?


    holli, /regexed monk/
[reply]
[d/l]
Re: Pipe tag being entered into database
by Perl Mouse (Chaplain) on Dec 20, 2005 at 12:32 UTC
    In your log, you print the value of 23 variables. But you insert 18 values, many of which you don't print in your log. And I don't know what you mean by "your columns look like this". Do you mean you have 6 columns? How can you then insert 18 values? And while 'FIXID' and 'CLIENTNAME' look like column names, '8' and 'fud646_20051118' don't. How do these columns relate to your insert statement? And what relevance does the log have? And where does 'fud646_20051118|' come from anyway?
    Perl --((8:>*
[reply]
      I think that it has something to do with the fact that each value i am extracting from my log file looks like.
      [8=FIX.4.2^A9=0399^A]

      [download]
      So the ^A is getting replaced with | when inserting into the database.
[reply]
[d/l]
Re: Pipe tag being entered into database
by duff (Parson) on Dec 20, 2005 at 15:49 UTC

    Are you sure those pipe symbols aren't just artifacts of the method you're using to look at the rows in your table? For instance, when I use mysql interactively to look at data I get rows of data where the columns are separated by pipes.

    If you truly are printing the data immediately and exactly as it is entered in the database, then there are no pipe symbols in your data. Otherwise, you're testing the wrong thing or you're doing something else wrong.

    duff

[reply]
      Well i thought it was that, but the file is being created on windows and you can see the ^A after each character that i am trying to extract. T did try a search and replace on ASCII chars $_ =~ s/\x41\x88//g; Which should be the ^A but that did not work. it is very strange if you open this line of the file in VI you see the ^A but if you more the file or cat it you don't
[reply]

Back to Seekers of Perl Wisdom