We help IT Professionals succeed at work.

perl dbi

saibsk
saibsk asked
on
440 Views
Last Modified: 2012-05-08
i have file with insert statements
i need to have them inserted to a DB in a perl script

also one of the fields has the value

insert table value ('ABC')

but this stored in a string

$name='ABC'

when i do something like tis insert table value ($name) throws an error how can modify this
Comment
Watch Question

Lee WadwellProject Architect
CERTIFIED EXPERT

Commented:
Hi saibsk,

How are you preparing/running your SQL?  Depending on you database you may be able to use bind variables. Can we see your code (or the relevant parts) ... it is hard give suggestions without it.

lwadwell

Author

Commented:
I have file with the insert statement

something like this

insert table (prop_locate_required,street_side_system,act_rmi,internal,special_acct_type_handling,nasdaq_member,competing_mm,affiliate,upstairs_disp_group,cxl_pin_on_recovery,pk_autoexec_elig,pk_auto_group,rej_exec_stop,supress_clearing,amex_member,nyse_member,alpha_group,nyse_member_id,listed_broker_symbol,NHEElig,owning_trader,AGU,QSR,pc_flag,accept_stp,cancel_symbol_change,cancel_dividend,report_to_act,ReportOnBehalf,nasd_member,reinstate_held_order,reinstate_nh_order,bal_smartex_elig,SmartExElig,refuse_preopen_cross,send_trade_cxl_replace,auto_exec_group,act_eligible,short_name,description, broker_symbol,clearing_broker_symbol,clearing_broker_number,reporting_act_broker_symbol,pin_priority,pin_account,customer_account_no,lock_in_trd_type,correspondent_clearing) values (0,0,'A',0,0,0,0,0,1,0,1,0,1,0,0,0,1,'','',0,'',0,0,0,1,1,0,1,0,1,0,0,0,1,0,0,3,'A','$ShortName','$Description','$BrokerSymbol','$ClearingBrokerSymbol','$ClearingBrokerNumber','$ACTBrokerSymbol',0,'','0','N',0)"."\n";

     print FHOUT "insert table (prop_locate_required,street_side_system,act_rmi,internal,special_acct_type_handling,nasdaq_member,competing_mm,affiliate,upstairs_disp_group,cxl_pin_on_recovery,pk_autoexec_elig,pk_auto_group,rej_exec_stop,supress_clearing,amex_member,nyse_member,alpha_group,nyse_member_id,listed_broker_symbol,NHEElig,owning_trader,AGU,QSR,pc_flag,accept_stp,cancel_symbol_change,cancel_dividend,report_to_act,ReportOnBehalf,nasd_member,reinstate_held_order,reinstate_nh_order,bal_smartex_elig,SmartExElig,refuse_preopen_cross,send_trade_cxl_replace,auto_exec_group,act_eligible,short_name,description, broker_symbol,clearing_broker_symbol,clearing_broker_number,reporting_act_broker_symbol,pin_priority,pin_account,customer_account_no,lock_in_trd_type,correspondent_clearing) values (0,0,'A',0,0,0,0,0,1,0,1,0,1,0,0,0,1,'','',0,'',0,0,0,1,1,0,1,0,1,0,0,0,1,0,0,3,'A','$ShortNameManual','$DescriptionManual','$BrokerSymbol','$ClearingBrokerSymbol','$ClearingBrokerNumber','$ACTBrokerSymbol',0,'','0','N',0)"."\n";

     print FHOUT "insert table (prop_locate_required,street_side_system,act_rmi,internal,special_acct_type_handling,nasdaq_member,competing_mm,affiliate,upstairs_disp_group,cxl_pin_on_recovery,pk_autoexec_elig,pk_auto_group,rej_exec_stop,supress_clearing,amex_member,nyse_member,alpha_group,nyse_member_id,listed_broker_symbol,NHEElig,owning_trader,AGU,QSR,pc_flag,accept_stp,cancel_symbol_change,cancel_dividend,report_to_act,ReportOnBehalf,nasd_member,reinstate_held_order,reinstate_nh_order,bal_smartex_elig,SmartExElig,refuse_preopen_cross,send_trade_cxl_replace,auto_exec_group,act_eligible,short_name,description, broker_symbol,clearing_broker_symbol,clearing_broker_number,reporting_act_broker_symbol,pin_priority,pin_account,customer_account_no,lock_in_trd_type,correspondent_clearing) values (0,0,'A',0,0,0,0,0,1,0,1,0,1,0,0,0,1,'','',0,'',0,0,0,1,1,0,1,0,1,0,0,0,1,0,0,3,'A','$ShortNamePink','$DescriptionPink','$BrokerSymbol','$ClearingBrokerSymbol','$ClearingBrokerNumber','$ACTBrokerSymbol',0,'','0','N',0)"."\n"

I fixed the quotes issues though

Author

Commented:
print FHOUT prints the insert stmf to the file
Lee WadwellProject Architect
CERTIFIED EXPERT

Commented:
saibsk,

are you trying to run the INSERT SQL's 'as-is'?  The values like '$ShortName' ... do you want to store the string with the $ sign (in that case it will probably need to be escaped to be '\$ShortName') or evaluate it to variable held in your script of that name?

lwadwell

Author

Commented:


use strict;

open (FH, "<test_customer_setup.csv");
open (FHOUT, ">>update_customer_setup.sql");

while (my $line = <FH>){
  my @data = split/\,/, $line;
  my $ShortName = $data[0];
  my $Description = $data[1];
  my $customerSymbol = $data[2];
  my $ClearingcustomerSymbol = $data[3];
  my $ClearingcustomerNumber = $data[4];
  my $ARTcustomerSymbol = $data[5];
  $ARTcustomerSymbol =~ s/\s+//g;

  if ($customerSymbol =~ /^[A-Za-z]{4}$/ && $ClearingcustomerSymbol =~ /^[A-Za-z]{4}$/ && $ARTcustomerSymbol =~ /^[A-Za-z]{4}$/ && $ClearingcustomerNumber =~ /^\d{4}$/) {
     my $ShortNameManual = $ShortName.'M';
     my $DescriptionManual = $Description. ' - MANUAL';
     my $ShortNamePink = 'PINK'.$ShortName;
     my $DescriptionPink = $Description. ' - PINK';

     print FHOUT "insert cust_short_name (prop_locate_required,street_side_system,ART_rmi,internal,special_acct_type_handling,nasdaq_member,competing_mm,affiliate,upstairs_disp_group,cxl_pin_on_recovery,pk_autoexec_elig,pk_auto_group,rej_exec_stop,supress_clearing,amex_member,nyse_member,alpha_group,nyse_member_id,listed_customer_symbol,NHEElig,owning_trader,AGU,QSR,pc_flag,accept_stp,cancel_symbol_change,cancel_dividend,report_to_ART,ReportOnBehalf,nasd_member,reinstate_held_order,reinstate_nh_order,bal_smartex_elig,SmartExElig,refuse_preopen_cross,send_trade_cxl_replace,auto_exec_group,ART_eligible,short_name,description, customer_symbol,clearing_customer_symbol,clearing_customer_number,reporting_ART_customer_symbol,pin_priority,pin_account,customer_account_no,lock_in_trd_type,correspondent_clearing) values (0,0,'A',0,0,0,0,0,1,0,1,0,1,0,0,0,1,'','',0,'',0,0,0,1,1,0,1,0,1,0,0,0,1,0,0,3,'A','$ShortName','$Description','$customerSymbol','$ClearingcustomerSymbol','$ClearingcustomerNumber','$ARTcustomerSymbol',0,'','0','N',0)"."\n";

     print FHOUT "insert cust_short_name (prop_locate_required,street_side_system,ART_rmi,internal,special_acct_type_handling,nasdaq_member,competing_mm,affiliate,upstairs_disp_group,cxl_pin_on_recovery,pk_autoexec_elig,pk_auto_group,rej_exec_stop,supress_clearing,amex_member,nyse_member,alpha_group,nyse_member_id,listed_customer_symbol,NHEElig,owning_trader,AGU,QSR,pc_flag,accept_stp,cancel_symbol_change,cancel_dividend,report_to_ART,ReportOnBehalf,nasd_member,reinstate_held_order,reinstate_nh_order,bal_smartex_elig,SmartExElig,refuse_preopen_cross,send_trade_cxl_replace,auto_exec_group,ART_eligible,short_name,description, customer_symbol,clearing_customer_symbol,clearing_customer_number,reporting_ART_customer_symbol,pin_priority,pin_account,customer_account_no,lock_in_trd_type,correspondent_clearing) values (0,0,'A',0,0,0,0,0,1,0,1,0,1,0,0,0,1,'','',0,'',0,0,0,1,1,0,1,0,1,0,0,0,1,0,0,3,'A','$ShortNameManual','$DescriptionManual','$customerSymbol','$ClearingcustomerSymbol','$ClearingcustomerNumber','$ARTcustomerSymbol',0,'','0','N',0)"."\n"

this is the entire script

Lee WadwellProject Architect
CERTIFIED EXPERT

Commented:
saibsk,

So, are you trying to add DBI to your script to run the INSERT statements into your database?

lwadwell

Author

Commented:
the file now has insert statmenets. Either i could use isql in the perl script or DBI? ANything to insert the statements to the DB.
Lee WadwellProject Architect
CERTIFIED EXPERT

Commented:
saibsk,

you could use either.  DBI would be my pick - and with DBI you do not need a file - the program can run the SQL as you create them.  I assume you have DBI (and DBD etc) installed.  I would suggest you look over the documentation (http://search.cpan.org/~timb/DBI-1.609/DBI.pm).

But what you need to do is:
1. create a DBI connection, as I do not know what sort of database etc you have ... please refer to the documentation.
$dbh = DBI->connect($data_source, $username, $password)
            or die $DBI::errstr;

2. Inside the loop make and run the SQL against the connected database. e.g.
my $SQL1 = "insert cust_short_name (prop_locate_required,street_side_system,ART_rmi,internal,special_acct_type_handling,nasdaq_member,competing_mm,affiliate,upstairs_disp_group,cxl_pin_on_recovery,pk_autoexec_elig,pk_auto_group,rej_exec_stop,supress_clearing,amex_member,nyse_member,alpha_group,nyse_member_id,listed_customer_symbol,NHEElig,owning_trader,AGU,QSR,pc_flag,accept_stp,cancel_symbol_change,cancel_dividend,report_to_ART,ReportOnBehalf,nasd_member,reinstate_held_order,reinstate_nh_order,bal_smartex_elig,SmartExElig,refuse_preopen_cross,send_trade_cxl_replace,auto_exec_group,ART_eligible,short_name,description, customer_symbol,clearing_customer_symbol,clearing_customer_number,reporting_ART_customer_symbol,pin_priority,pin_account,customer_account_no,lock_in_trd_type,correspondent_clearing) values (0,0,'A',0,0,0,0,0,1,0,1,0,1,0,0,0,1,'','',0,'',0,0,0,1,1,0,1,0,1,0,0,0,1,0,0,3,'A','$ShortName','$Description','$customerSymbol','$ClearingcustomerSymbol','$ClearingcustomerNumber','$ARTcustomerSymbol',0,'','0','N',0)";
my $rows = $dbh->do($SQL1)           or die $dbh->errstr;

repeat for your second SQL.

lwadwell
Top Expert 2009
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.