Using - $id = $query->insertid, not working...

[Tasuki]

I'm not sure if I'm using this correctly.

For the following piece of code:

$id = $query->insertid

[What it does: If you have a field that is auto incremented, then this function will come in handy. It will return to you the the unique id given to the field after the query.]

I have two tables that I want to insert into, one table will generate the autonumber that should be pulled by the code above, and the other table will insert that into it's database to link the two databases.

My code:

use DBI;

$dbh = DBI->connect('dbi:mysql:databases','login','pw');

open (INFILE, "marinedata.txt");

while (<INFILE>) {
   $line = $_;
   chomp($line);
   ($sampnum, $multipanel, $date, $location, $foundid, $locationid, $vessel, $sitename, $rest) = split(/\t/, $line, 9);
   ($month, $day, $year) = split(/\//, $date, 3);
   $date2 = $year."-".$month."-".$day;

   $query1 = "INSERT INTO tblNetClass (SampleNum, Multipanel, Date, NetLocation, FoundID, LocationID, Vessel, SiteName)
	 	VALUES ('$sampnum', '$multipanel', '$date2', '$location', '$foundid', '$locationid', '$vessel', '$sitename')";

   $sth = $dbh->prepare($query1);

   $sth->execute || 
           die "Could not execute SQL statement!";

   $netclassid = $query1->NetClassID;

   ($subsamp, $stretch, $twine, $twist, $strands, $floats, $rope, $weight, $rdia1, $rlmn1, $rlmx1, $rdia2, $rlmn2, $rlmx2, $rdia3, $rlmn3, $rlmx3, $trimedge, $foul, $breaks, $picid, $initial, $initiald, $classid, $otherid, $constrid, $color, $nettype, $trimclr, $comment) = split(/\t/, $rest, 30);
   ($month, $day, $year) = split(/\//, $initiald, 3);
   $date4 = $year."-".$month."-".$day;

   $query2 = "INSERT INTO tblNetDesc (SubSample, StretchMeshSize, TwineDiam, Twist, NumStrands, Floats,
	   			  Rope, Weight, RopeDiam1, RopeLngth1min, RopeLngth1max, RopeDiam2,
				  RopeLngth2min, RopeLngth2max, RopeDiam3, RopeLngth3min, RopeLngth3max,
				  TrimmedEdge, Foul, BreaksEasily, PictureName, Initial, InitialDate, NetClassID,
				  ConstructionID, ColorID, NetTypeID, TrimColorID, Comment)
	     VALUES ('$subsamp', '$stretch', '$twine', '$twist', '$strands', '$floats', '$rope', '$weight',
		  '$rdia1', '$rlmn1', '$rlmx1', '$rdia2', '$rlmn2', '$rlmx2',
		  '$rdia3', '$rlmn3', '$rlmx3', '$trimedge', '$foul', '$breaks', '$picid', '$initial',
		  '$date4', '$netclassid', '$constrid', '$colorid', '$nettype', '$trimclr', '$comment')";

   $sth2 = $dbh->prepare($query2);

   $sth2->execute || 
           die "Could not execute SQL statement!";
}

The error that I get through PERL:

Can't locate object method "NetClassID" via package "INSERT INTO tblNetClass (SampleNum, Multipanel, Date, NetLocation, FoundID, LocationID,
iteName)
                VALUES ('1.01', '4', '2003-5-20', 'Pearl & Hermes Atoll', '1', '3', 'OF', 'MAZE')" (perhaps you forgot to load "INSERT INTO
s (SampleNum, Multipanel, Date, NetLocation, FoundID, LocationID, Vessel, SiteName)
                VALUES ('1.01', '4', '2003-5-20', 'Pearl & Hermes Atoll', '1', '3', 'OF', 'MAZE')"?) at dbconn.pl line 55, <INFILE> line 1.

It inserts the first record into the first table just fine, but everything after that does not work. Does anyone have any idea what I may be doing wrong?

Thanks for your time, and looking.

-T

 

[Tasuki]

Nevermind I figured it out... using something like this:

$NetTypeID = $sth->{'mysql_insertid'};

Returns the last inserted auto-incremented value.

Hope that will be of help to someone looking to insert something similar to this.

-T

 

[siberian]

Just a note, this practice is discouraged if you want to make your code cleanly cross-database compatible. As a victim of many migrations from mysql to other systems (oracle/firebird etc) this can really hurt.

Minimally you should isolate your database calls into a single module so that you only have one place to make changes.

Of course, if this is just a quick and dirty webtool or something your probably ok, just had to make my usual 'scalability and practices' comments

 

[Tasuki]

I'm not sure what you really mean, is there something that I shouldn't be doing in this code?

It is actually a one-time use thing, importing data from a tab-delimited text file (exported from Excel file) into MySQL for a website. I wanted to keep a one-to-many relation between the NetClass and NetDesc table by using the AutoNumberID assigned to NetClass (after a new insert) and plugging it into the insert of the NetDesc table.

I took care of the duplicate insert by checking to see if the SampleNumber is already there and if it is, make the $NetTypeID equal to the retrieved autonumber of the record.

I appreciate and thank you for your comment.

 

[siberian]

Using

$sth->{'mysql_insertid'};

is a mysql specific construct and will not cross to other databases. Not a big deal if your doing one off code but in any sustainable system that sort of dependancy is not a super fun thing.

Don';t get me wrong, I do it all the time and it hurts