Insert into DB problem

pjofnj has asked for the wisdom of the Perl Monks concerning the following question:

I am trying to insert into a table. And It is giving me an error. Can someone please tell me what am I doing wrong?

foreach my $sub (keys %data) {
    ($Artist, $Song) = split(/\|/, $sub);

       $DictIDNums = join(", ", @{$data{$sub}});


    $CheckQuery = "Insert into MeterMusicTemp..QCStuff Select dictid,f
+ilename,'$Song' from (select dictid,filename,artist,title,song,path,h
+holdid,updated_by,filesize  from FMDATAFILE3 where PPMonth=$PPMonth a
+nd ($RuleID) and dictid in (select dictid from dictionary d,artist a,
+song s where d.artistid=a.artistid and d.songid=s.songid and artist='
+$Artist' and song = '$Song') and matchlevel<3) as T1 where lower(File
+Name) not like lower('%$Song%') order by filename ";
    print "$CheckQuery\n";
    my $dbh = DBI->connect( "DBI:ODBC:MusicWatch", "App_User", "au", {
+ RaiseError => 1, AutoCommit => 0 });

eval {
$dbh->do("$CheckQuery");
$dbh->commit(  );
};
    
if ($@) {
      eval { $dbh->rollback(  ) };
      die "Couldn't roll back transaction" if $@;
}
[download]

Thanks, PJOfNJ

Comment on Insert into DB problem Download Code

Replies are listed 'Best First'.
Re: Insert into DB problem by jZed (Prior) on May 25, 2005 at 15:52 UTC
Please format your query in a legible manner if you want other people to look at it. Please tell us what the error is if you want people to help explain why it happens. At first glance I see two things which may not be errors but which are not the best way to do things. First, take the connect statement out of the loop, or at a minimum replace it with connect_cached() - there's no reason I can think of to keep connecting again and again. Second, use placeholders! On the error side ... do you really want two periods after "MeterMusciTemp"?	[reply]
Re^2: Insert into DB problem by pjofnj (Initiate) on May 25, 2005 at 16:01 UTC
I need those periods there because I am inserting into a differnt database than selecting from. The query is as follows: Insert into MeterMusicTemp..QCStuff Select dictid,filename, 'SAVE A HORSE (RIDE A COWBOY)' from (select dictid, filename, artist, title, song, path, hholdid, updated_by, filesize from FMDATAFILE3 where PPMonth=424 and (RuleID >= 70 and RuleID < 80) and dictid in (select dictid from dictionary d, artist a, song s where d.artistid=a.artistid and d.songid=s.songid and artist='BIG & RICH' and song = 'SAVE A HORSE (RIDE A COWBOY)') and matchlevel<3) as T1 where lower(FileName) not like lower('%SAVE A HORSE (RIDE A COWBOY)%') + order by filename [download] Thanks, PJOfNJ	[reply] [d/l]
Re^3: Insert into DB problem by jZed (Prior) on May 25, 2005 at 16:09 UTC
I need those periods there because I am inserting into a differnt database than selecting from. I don't know about whatever DBMS you're using, but ANSI SQL syntax for qualified table names is Catalog.Schema.Table or Schema.Table or Table. I suspect that what you are calling a database is a Schema and therefore you only want one period. If what you are calling a database is a catalog, then I suspect you need to put the Schema name between the two periods.	[reply]
Re: Insert into DB problem by terce (Friar) on May 25, 2005 at 16:07 UTC
I'm not sure that ODBC connections allow you to specify database names in this way. Try it without the `MeterMusicTemp..` altogether, or with MeterMusicTemp fully qualified: `SERVERNAME.MusicMeterTemp.OWNER.QCStuff`. Or use a stored procedure, which would be a far more efficient way of handling this type of insert, and would save you crufting up your code with SQL.	[reply] [d/l] [select]
Re^2: Insert into DB problem by pjofnj (Initiate) on May 25, 2005 at 16:55 UTC
Thanks.. Creating a proc did the job. PJOFNJ	[reply]
Re: Insert into DB problem by mifflin (Curate) on May 25, 2005 at 15:49 UTC
what is the error?	[reply]
Re^2: Insert into DB problem by pjofnj (Initiate) on May 25, 2005 at 15:56 UTC
The error MEssage is: " DBD::ODBC::db do failed: Microsoft ODBC SQL Server Driver SQL ServerInvalid object name 'MeterMusicTemp..QCStuff'. (SQL-42S02)(DBD: Execute immediate failed err=-1) at HashTest.pl line 78. " for every row that it tries to insert into. Thanks, PJOfNJ	[reply]
Re^3: Insert into DB problem by mifflin (Curate) on May 25, 2005 at 16:00 UTC
Well, as jZed pointed out, are you sure 'MeterMusicTemp..QCStuff' is a valid table name? I'm not familiar with SQL Server but I know it would be invalid in Oracle.	[reply]
Re: Insert into DB problem by Grygonos (Chaplain) on May 25, 2005 at 17:46 UTC
That's a SQL Server specific way of saying from this database on the same server, use this table. As was mentioned above, a stored procedure will be able to handle that. Also worth noting, is that if you choose to create any more sp's in this fashion that will be returning data, you will need to `SET NOCOUNT ON` Grygonos	[reply] [d/l]