insertion through dbi

Hi all,

I am trying to insert 16K records into some table through DBI. But my
script only inserts the last record. I check the array size and the sql
return values. Everything seems normal. Any ideas?


Regards,

Mehmet Baserdem



Here is the my script:



#!/usr/bin/perl=20

use DBI;=20
use DBD::DB2::Constants;
use DBD::DB2;

@IDS =3D();

# open file
open(FILE, "data.txt") or die("Unable to open file");

# read file into an array
@IDS =3D ;

# close file=20
close(FILE);

#print "array size ".@IDS;

# There are IDS to be INSERTED
$dbh =3D DBI->connect("dbi:DB2:testdb", "testuser", "testpasswd");=09
$sth =3D $dbh->prepare("INSERT INTO mytable VALUES (?)");
$sth->execute_array(\%attr, \@IDS);=20
$sth->commit();=20
$sth->finish();
$dbh->disconnect();

Re: insertion through dbi

Baserdem, Mehmet wrote:
> Hi all,
>
> I am trying to insert 16K records into some table through DBI. But my
> script only inserts the last record. I check the array size and the sql
> return values. Everything seems normal. Any ideas?
>
>
> Regards,
>
> Mehmet Baserdem
>
>
>
> Here is the my script:
>
>
>
> #!/usr/bin/perl
>
> use DBI;
> use DBD::DB2::Constants;
> use DBD::DB2;
>
> @IDS =();
>
> # open file
> open(FILE, "data.txt") or die("Unable to open file");
>
> # read file into an array
> @IDS = ;
>
> # close file
> close(FILE);
>
> #print "array size ".@IDS;
>
> # There are IDS to be INSERTED
> $dbh = DBI->connect("dbi:DB2:testdb", "testuser", "testpasswd");
> $sth = $dbh->prepare("INSERT INTO mytable VALUES (?)");

According to the docs (man DBI) this is not a reference:
----------------8<----------------
$sth->execute_array(\%attr, @IDS);
----------------8<----------------

Buf i've never used it.

> $sth->execute_array(\%attr, \@IDS);
> $sth->commit();
> $sth->finish();
> $dbh->disconnect();
>
>


Tom

RE: insertion through dbi

Hi all,

I guess I found the source of the problem. The new line chars at the end
was preventing the insertion at middle rows.=20

Below code works fine.

Regards,=20

Mehmet Baserdem




#!/usr/bin/perl=20

use DBI;=20
use DBD::DB2::Constants;
use DBD::DB2;

@IDS =3D();


# open file
open(FILE, "data.txt") or die("Unable to open file");

# read file into an array

while(){
chomp;
push(@IDS,$_);
}


# close file=20
close(FILE);

print "size".@IDS;

# There are IDS to be INSERTED
$dbh =3D DBI->connect("dbi:DB2:USQLTST1", "USQLCD", "QUEEN");=09
$sth =3D $dbh->prepare("INSERT INTO MB_TEST_FOO VALUES (?)");
$sth->execute_array(\%attr, \@IDS);=20
$sth->finish();
$dbh->disconnect();



=20