Perl DBI Insert Problems

[Zippeh]

I'm trying to get some "INSERT" statements running on Perl but for some reason, the insert is executed twice!

Here's a snippet of the code:
Code:

  if(Encode::is_utf8("$line")) {
    $sqlstatement=
    qq{
      insert into items (parentid,title,description,archivedate,archiveid,imagename,status,indexed) values ($parentid,N'$fields[2]',N'$fields[4]',N'$fields[3]',$archiveid,N'$image',0,0)
    };
  }
  else {
    $sqlstatement=
    qq{
      insert into items (parentid,title,description,archivedate,archiveid,imagename,status,indexed) values ($parentid,'$fields[2]','$fields[4]','$fields[3]',$archiveid,'$image',0,0)
    };  
  }
  $sth = $db->prepare("$sqlstatement");
  $sth->execute();

What it does is it processes a text file, inserting each of the lines contained within it. I've done a "print" of the SQL just before the execute, and it only prints out 7 insert commands (which is correct as I have 7 lines in the text file im testing with). And I have also exited the loop straight after the execute statement, but it STILL gets inputted twice!!

Heeeeeelp!

 

[PaulTEG]

Zippeh,

Can we see some more code, if it's getting inserted twice, there must be something to do with the control structure

--Paul

cigless ...

 

[Zippeh]

Sure here it is:

LINE: while ($line=<DATA>) {
#while (1 == 0) {
  chomp $line;
  @fields=split(/\t/, $line);
  $fields[4]=~ s/"/&quot;/g;
  $fields[4]=~ s/'/&rsquo;/g;

  if ($fields[1] eq 'c') {
    $parentid=$form{'parentid'};
    $addparent=1;
  }
  if ($fields[1] eq 'i') {
    $parentid=$currentparent;
  }
  if ($fields[1] eq 'p') {
    $parentid=$form{'parentid'};
  }

  if(Encode::is_utf8("$line")) {
    $sqlstatement=
    qq{
      insert into items (parentid,title,description,archivedate,archiveid,imagename,status,indexed) values ($parentid,N'$fields[2]',N'$fields[4]',N'$fields[3]',$archiveid,N'$image',0,0)
    };
  }
  else {
    $sqlstatement=
    qq{
      insert into items (parentid,title,description,archivedate,archiveid,imagename,status,indexed) values ($parentid,'$fields[2]','$fields[4]','$fields[3]',$archiveid,'$image',0,0)
    };  
  }
  $sth = $db->prepare("$sqlstatement");
  $sth->execute();
  $sth->finish();
  print("$sqlstatement");
  
  $count++;
  if ($addparent==1) {
	# we've just added a category so we need to retrieve its id.
	$sqlstatement=
	qq{
	  select itemid from items where title='$fields[2]' order by itemid DESC;
	};
	
	$sthitemid = $db->prepare("$sqlstatement");
	$sthitemid->execute();
	$row = $sthitemid->fetchrow_hashref;

	$currentparent = $row->{'itemid'};
	$addparent=0;
  }
  $sqlstatement=qq{ select max(itemid) as max from items; };
  $sthmax = $db->prepare("$sqlstatement");
  $sthmax->execute();

  $row = $sthmax->fetchrow_hashref;
  
  $itemtoinsert = $row->{'max'};
  #$itemtoinsert = $db->Data();
  #if ($itemtoinsert>0) { add_record_to_index($itemtoinsert,$fields[2],$fields[4]);}

}
$db->disconnect();

The file I am testing with contains 7 items that need to be inserted. When I print out the $count, its value is 7. So it does seem to be looping the right number of times. When I look in the table its meant to be inserted into, the order is like the loop has been run 14 times. eg they go 1, 2, 3, 4, 5, 6, 7, 1, 2, 3, 4, 5, 6, 7. If that makes sense

 

[tchatzi]

I dont think that it has to do with your call to the database, but with the opening and reading from your file or whatever <DATA> represents.


``The wise man doesn't give the right answers,
he poses the right questions.''
TIMTOWTDI

 

[PaulTEG]

Can't see anything obvious

try changing sqlstatement to sqlstatement1,sqlstatement2..., or debug print it before execution and preparation

You're calling execute and prepare on the sqlstatement, and if it's still got the same value as the insert clause, this could be where the problem is coming form

HTH
--Paul



cigless ...

 

[MOrac]

You would be better off using the DBI ->do call for single-shot non-query executable statements, not a ->prepare / ->execute / ->finish set; it's more efficient. If you want to stay with prepared statements, you ought to actually use it beneficially - i.e. use prepare on the statement before the loop, use placeholders for the inserted values, only use bind and execute within the loop and call finish only when all the values have been inserted.

None of which is relevant to your problem, sadly. If you're getting a full replay of your inserted values, the problem lies in either your database or your connection to it. What type of database is it, and how are you connecting to it? Is it transactional, and if so, are you using the functionality or not (typically, the AutoCommit flag in DBI, should the database support it)?

Perhaps you could try (within the program) retrieving and printing out the content of the table before your disconnect line, to see if duplication occurs before or after you close the connection?

 

[Zippeh]

Regarding printing out the sqlstatements, I did this to see if the same one was getting executed more than once, but this was not the case. When I comment out the execute() method that inserts, then it doesn't get inserted once even.

So the loop goes around the right number of times, but somehow that execute manages to insert things twice, even though if you print out all the sql insert statements before they are executed, only 7 appear!!

 

[Zippeh]

I've just changed the names of the variables holding the other SQL statements to test, and still no luck

 

[MikeLacey]

Hello Zippeh,

I had one like this a while ago - I tore out what remained of my hair and used some quite bad language before I thought to check the DATA file for duplicates... Turned out my code was doing it right but the data being inserted was a bit dicky.

Mike

You cannot really appreciate Dilbert unless you've read it in the
original Klingon.

Want great answers to your Tek-Tips questions? Have a look at faq219-2884

 

[MOrac]

I didn't say print out the SQL statements; you said you'd done that. I said, retrieve the table data over the DB connection and print *that* to see if the duplication occurs before or after your disconnect statement.

 

[tchatzi]

Try like this

if(Encode::is_utf8("$line")) {
	$sth = $db->prepare(qq{INSERT INTO items 
			(parentid,title,description,archivedate,archiveid,imagename,status,indexed) 
			values
			($parentid,N'$fields[2]',N'$fields[4]',N'$fields[3]',$archiveid,N'$image',0,0)});
}else {
	 $sth = $db->prepare(qq{INSERT INTO items 
			(parentid,title,description,archivedate,archiveid,imagename,status,indexed)
			values
			 ($parentid,'$fields[2]','$fields[4]','$fields[3]',$archiveid,'$image',0,0)});
}
$sth->execute();

``The wise man doesn't give the right answers,
he poses the right questions.''
TIMTOWTDI

 

[MOrac]

perluserpengo - what for? That literally achieves nothing new.

 

[tchatzi]

Yea i know, i guess i need some sleep, just discard that please.

Zippeh can you show us the DATA file?


``The wise man doesn't give the right answers,
he poses the right questions.''
TIMTOWTDI

 

[Zippeh]

I'm at home now so don't have access to the files

I will do first thing in the morning when i get back!

How sad am I coming on here at home to see if someone can help me with work things???

 

[Zippeh]

The file is tab separated. Here's a small example of one.

1 c XB13/204-209 Llyfrau Cofnodion-Pwyllgor Tai Minute Books-Housing Committee
2 i XB13/204 1929 June- MINUTE BOOK: Housing Committee. 1930 April
3 i XB13/205 1934 Jan.- MINUTE BOOK: Housing Committee. 1952 Dec. Welsh
4 i XB13/206 1953 Jan.- MINUTE BOOK: Housing Committee. 1963 Dec. Welsh
5 i XB13/207 1966 March- MINUTE BOOK: Housing Committee. 1968 Dec. Welsh
6 i XB13/208 1969 Jan.- MINUTE BOOK: Housing Committee. 1970 Dec. Welsh
7 i XB13/209 1971 Jan.- MINUTE BOOK: Housing Committee. 1973 Dec. Welsh