-----Original Message----- My code with line numbers is below. Without line numbers is in the attachment.
Thank you very much for taking an interest in my problem! The problem does not seem to occur for smaller records. Siegfried From: Charles K. Clarkson [mailto:[EMAIL PROTECTED] Sent: Monday, September 19, 2005 7:03 AM To: beginners@perl.org Subject: RE: Where is the data I just inserted? Siegfried Heintze <mailto:[EMAIL PROTECTED]> wrote: : I'm using the DBI with ODBC on MS Access. : : I perform a SELECT. If this fails to find anything, I perform an : "INSERT" and "SELECT MAX(id)". : : Then I perform the exact same SELECT statement that I first : performed and it does not find what I just inserted. Argghhh! Show us the code. We know INSERT commands work, so there is probably an error in the code, but we can't tell you about the error without seeing your code. Charles K. Clarkson -- Mobile Homes Specialist 254 968-8328 1 #!c:/perl/bin/perl 2 3 # 4 # $Log$ 5 # 6 # 7 # Begin commands to execute this file using Perl with bash 8 # TZ=mdt 9 # export TZ 10 # date 11 # ./select-insert-select-failure.pl 12 # date 13 # End commands to execute this file using Perl with bash 14 # 15 16 use strict; 17 use warnings; 18 use DBI; 19 use dbPersist; 20 use POSIX qw(strftime); 21 use Time::localtime; 22 use Date::Manip; 23 use dice; 24 use monster; 25 use hotjobs; 26 my $database = 'job-search.mdb'; # Access 2000 db 27 my $DSN = "driver={Microsoft Access Driver (*.mdb)};dbq=$database"; 28 my $dbh = DBI->connect("dbi:ODBC:driver={Microsoft Access Driver (*.mdb)};dbq=$database","Admin","") or die "$DBI::errstr\\n"; 29 $| = 1; 30 31 my @sSQLSELECTClauses=(' sJobTitle = ?',' sExperience = ?',' urlOriginal = ?',' sPayRate = ?',' sEmployer = ?',' sEducation = ?',' sJobSiteJobID = ?'); 32 my @sSQLSELECTFieldValues=('SENIOR C# .NET DEVELOPER - Play a key role in building & mentoring a development team: The Hire Advantage' 33 ,'5-10 Years Experience' 34 ,'hotjobs.com' 35 ,'Unspecified' 36 ,'The Hire Advantage' 37 ,'Bachelor of Science' 38 ,'DEV'); 39 my @sSQLSELECTFieldNames=('sJobTitle','sExperience','urlOriginal','sPayRate','s Employer','sEducation','sJobSiteJobID'); 40 41 42 my @sSQLINSERTNameClauses=(' sJobTitle',' sExperience',' urlOriginal',' fkEmployer',' urlJobEmployerSite',' sPayRate',' urlJobJobSite',' sEmployer',' dtPost',' dtCreate',' sEducation',' sJobSiteJobID'); 43 my @sSQLINSERTValueClauses=(' ?',' ?',' ?',' ?',' ?',' ?',' ?',' ?',' ?',' ?',' ?',' ?'); 44 my @sSQLINSERTFieldValues=('SENIOR C# .NET DEVELOPER - Play a key role in building & mentoring a development team: The Hire Advantage' 45 ,'5-10 Years Experience' 46 ,'hotjobs.com' 47 ,'2' 48 ,'http://hotjobs.yahoo.com/Company-Profiles/T/The-Hire-Advantage_50962' 49 ,'Unspecified' 50 ,'http://hotjobs.yahoo.com/jobseeker/jobsearch/job_detail.html?job_id=J45063 0OO&search_url=%2Fjobseeker%2Fjobsearch%2Fsearch_results.html%3Fkeywords_all %3DCSharp%26ulm_input1%3DBoulder%252C%2BCO%26country1%3DUSA%26search_type_fo rm%3Dquick%26updated_since%3Dsixtydays%26basicsearch%3D0%26advancedsearch%3D 0%26metro_area%3D1%26search%3DSearch%26kw%3DCSharp&keywords_any=&keywords_al l=c%2523&keywords_phrase=' 51 ,'The Hire Advantage' 52 ,'2005-09-17' 53 ,'2005-09-19 15:05:19' 54 ,'Bachelor of Science' 55 ,'DEV'); 56 my @sSQLINSERTFieldNames=('sJobTitle' 57 ,'sExperience' 58 ,'urlOriginal' 59 ,'fkEmployer' 60 ,'urlJobEmployerSite' 61 ,'sPayRate' 62 ,'urlJobJobSite' 63 ,'sEmployer' 64 ,'dtPost' 65 ,'dtCreate' 66 ,'sEducation' 67 ,'sJobSiteJobID'); 68 69 70 my $sTableName='JobPosting'; 71 my $sIndent = ' '; 72 my $nMaxLength = 255; 73 74 if(@sSQLSELECTClauses){ 75 $sTableName =~ s/^h//; # I decided to not use hungarian on the table names. 76 my $sSelect = "SELECT id FROM $sTableName WHERE ".join(" AND ", @sSQLSELECTClauses).""; 77 print sprintf("%-3.3d",__LINE__)."$sIndent r=$sSelect\n"; 78 my $sthSelect = $dbh->prepare($sSelect); 79 unless ($sthSelect){ 80 die "No sth: ".sprintf("%-3.3d",__LINE__)." statment=$sSelect ".$dbh->errstr; 81 } 82 for ([EMAIL PROTECTED]) { 83 # $sSQLSELECTFieldValues[$_-1] =~ s/\s+/ /g; 84 my $len = length $sSQLSELECTFieldValues[$_-1]; 85 if ($len > $nMaxLength) { 86 $sSQLSELECTFieldValues[$_-1] = substr($sSQLSELECTFieldValues[$_-1],0,$nMaxLength); 87 } 88 print sprintf("%-3.3d",__LINE__)."$sIndent Bind ($_) length=".(length $sSQLSELECTFieldValues[$_-1])." $sSQLSELECTFieldNames[$_-1] = '$sSQLSELECTFieldValues[$_-1]' ".$sSQLSELECTClauses[$_-1]."\n" ; 89 # $sthSelect->bind_param($_, $sSQLSELECTFieldValues[$_-1]); 90 } 91 unless ($sthSelect->execute(@sSQLSELECTFieldValues)){ # @sSQLSELECTFieldValues 92 die sprintf("%-3.3d",__LINE__)." no select: '$sSelect' ". $sthSelect->errstr; 93 } 94 my $bFound = 0; 95 my $row; my $id; 96 if ($row=$sthSelect->fetch) { 97 $bFound++; 98 $id = $row->[0]; 99 print sprintf("%-3.3d",__LINE__)."$sIndent found: id=$id\n"; 100 #my $urlJobEmployerSite = $obj->{urlJobEmployerSite}; 101 #UpdateLastSeen(dbh=>$dbh, lastSeenTables=>$sLastSeenTables, table=>$sTableName, dtCreate=>$dtCreate, id=>$id, urlJobEmployerSite=>$urlJobEmployerSite, fkEmployer=>$obj->{fkEmployer} ); 102 } else { 103 # Did not find it, so insert it 104 my $iInsert = "INSERT INTO $sTableName (".join(",", @sSQLINSERTNameClauses).") VALUES (".join(",", @sSQLINSERTValueClauses).")"; 105 print sprintf("%-3.3d",__LINE__)."$sIndent r=$iInsert\n"; 106 my $sthInsert = $dbh->prepare($iInsert); 107 unless ($sthInsert){ 108 die "No sth: ".sprintf("%-3.3d",__LINE__).$dbh->errstr; 109 } 110 for ([EMAIL PROTECTED]) { 111 # $sSQLINSERTFieldValues[$_-1] =~ s/\s+/ /g; 112 my $len = length( $sSQLINSERTFieldValues[$_-1]); 113 if ($len > $nMaxLength) { 114 $sSQLINSERTFieldValues[$_-1] = substr($sSQLINSERTFieldValues[$_-1],0,$nMaxLength); 115 } 116 print sprintf("%-3.3d",__LINE__)."$sIndent Bind ($_) length=".(length $sSQLINSERTFieldValues[$_-1])." $sSQLINSERTFieldNames[$_-1] = '$sSQLINSERTFieldValues[$_-1]' \n" ; 117 $sthInsert->bind_param($_, $sSQLINSERTFieldValues[$_-1]); 118 } 119 $dbh->begin_work; 120 unless($sthInsert->execute()){ # @sSQLINSERTFieldValues 121 print __LINE__. "no insert: '$iInsert' ". $sthInsert->errstr; 122 } 123 # Accommodate brain damaged MSAccess 124 my $sSelectMax = "SELECT MAX(id) FROM $sTableName"; 125 print sprintf("%-3.3d",__LINE__)."$sIndent r=$sSelectMax\n"; 126 my $sth = $dbh->prepare($sSelectMax); 127 unless ($sth){ 128 die "No sth: ".sprintf("%-3.3d",__LINE__).$dbh->errstr; 129 } 130 unless ($sth->execute){ 131 die "no select: '$sSelectMax' ". $sth->errstr; 132 } 133 if ($row=$sth->fetch) { 134 $bFound++; 135 $id = $row->[0]; 136 } 137 $dbh->commit; 138 139 # Check our work 140 unless ($sthSelect->execute(@sSQLSELECTFieldValues)){ # @sSQLSELECTFieldValues 141 die sprintf("%-3.3d",__LINE__)." no select: '$sSelect' ". $sthSelect->errstr; 142 } 143 $bFound = 0; my $id2=0; 144 if ($row=$sthSelect->fetch) { 145 $bFound++; 146 $id2 = $row->[0]; 147 print __LINE__. " Found it: \$id = $id \$id2=$id2\n"; 148 } else { 149 print __LINE__. " Where did the record go that we just inserted?\n"; 150 } 151 # End check our work 152 } 153 } 154 155
select-insert-select-failure.pl
Description: Binary data
-- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] <http://learn.perl.org/> <http://learn.perl.org/first-response>
