By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,498 Members | 1,558 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,498 IT Pros & Developers. It's quick & easy.

MS SQL + Perl = Phantom rows

P: 3
Hi, I just posted this in the MS SQL Section, but maybe my problem is rooted in Perl, so it's more appropriate to post here instead (sorry for the double post)


I'm writing a PERL script to access and insert rows into a Microsoft SQL. i'm using MS SQL Server Management Studio Express (2005) to architect tables and queries.

I've inserted two records into my "Pools" table
One from MS SQL Manager as a test, and another from my PERL script.

The one inserted by MS SQL Manager shows up everytime. The row from my PERL script doesn't show up on MS SQL Manager and ONLY shows up on my PERL script and ONLY if the INSERT query isn't commented out.

For me, this is the second day of troubleshooting. Does anybody have any insight?

Expand|Select|Wrap|Line Numbers
  1. $dbHandle = S_dbConnect($hostname, $database, $username, $password, $port);
  2.  
  3. $query = qq{INSERT INTO Pools (site_ID, pool_name, last_job_ID)
  4.     VALUES(1, 'CSE_IAL_intnb6', 0)};
  5. print "$query\n";
  6.  
  7. $dbHandle->do($query);
  8.  
  9. $query = qq{SELECT * FROM Pools};
  10. print "$query\n";
  11.  
  12. $m_sth = S_dbExecute($dbHandle, $query);
  13.  
  14. while(my $row = $m_sth->fetchrow_hashref()) {
  15.     print "$row->{pool_ID}\t$row->{site_ID}\t$row->{pool_name}\t$row->{last_job_ID}\n";
  16. }
  17. print "END\n";
  18.  
  19. ######################################################################
  20. # Subroutine: S_dbConnect
  21. # Function:
  22. #       Creates a database connection to the database specified by the global
  23. #       variables in the database initialization section at the top of this
  24. #       file.
  25. # Variables:
  26. #       $g_serverName contains the name of the database server.
  27. #       $g_dbName contains the name of the database on the server.
  28. #       $g_interfacesFile contains the path to the interfaces file which is
  29. #               required by Perl::DBI.
  30. #       $g_dbUsername contains the username which will be used to access the DB.
  31. #       $g_dbPassword contains the password used to authenticate with the DB.
  32. # Returns:
  33. #       $H_DB is the database handle established by the database connection
  34. #
  35. ######################################################################
  36.  
  37. sub S_dbConnect{
  38.         my ($server,$db,$user,$password,$port) = @_;
  39.         print "Trying to connect to Database...   " if ($debug) ;
  40.         $H_DB = DBI->connect("dbi:Sybase;server=$server:$port;database=$db",$user,$password, 'Sybase' )
  41.         || die "DBI: Unable to connect to the database - $DBI::errstr\n";
  42.         $H_DB->{'RaiseError'} = 1;
  43.         print "Connected to the Database\n" if ($debug) ;
  44.         return $H_DB;
  45. }
  46.  
  47. ######################################################################
  48. # Subroutine: S_dbExecute
  49. # Function:
  50. #       Executes a SQL query on an already established database connection.
  51. # Variables:
  52. #       $m_sql contains the SQL query to be executed on the database.
  53. #       $H_DB is the database handle the sql query is to be executed on.
  54. # Returns:
  55. #       $m_sth is the handle for the data returned by the database.
  56. #
  57. ######################################################################
  58.  
  59. sub S_dbExecute{
  60.         my ($H_DB, $m_sql) = @_;
  61.         my $m_sth = $H_DB->prepare($m_sql);
  62.         # if prepare fails, sth is undefined
  63.         if (!defined $m_sth){
  64.                 print "Error preparing sql record: " . $H_DB->errstr .my @ret_val = (1,$m_sth);
  65.                 return @ret_val; "\n";
  66.         }
  67.  
  68. # sth returns true if it succeeds
  69.         if ($m_sth->execute()){
  70.                 return $m_sth;
  71.         }
  72. # sth returns false if it fails
  73.         else{
  74.                 die "Error quering Database\n";
  75.         }
  76. }
The two functions above are not mine, they are borrowed

Here are the results:
Expand|Select|Wrap|Line Numbers
  1. filc0201> ./test_db2.pl
  2. Trying to connect to Database...   Connected to the Database
  3. INSERT INTO Pools (site_ID, pool_name, last_job_ID)
  4.         VALUES(1, 'CSE_IAL_intnb6', 0)
  5. SELECT * FROM Pools
  6. 22      1       test    0
  7. 30      1       CSE_IAL_intnb6  0
  8. END
Here is my server's info:
Expand|Select|Wrap|Line Numbers
  1. filc0201> uname -a
  2. Linux filc0201 2.6.5-7.276.PTF.196309.1-smp #1 SMP Mon Jul 24 10:45:31 UTC 2006 x86_64 x86_64 x86_64 GNU/Linux
  3. filc0201> cat /etc/issue
  4.  
  5. Welcome to SUSE LINUX Enterprise Server 9 (x86_64) - Kernel \r (\l).
Jul 1 '08 #1
Share this Question
Share on Google+
1 Reply


P: 3
Ah, nevermind,
I needed to add a
$dbHandle->commit(); for the changes to take effect.

Also, Does anybody know if there is a way to retrieve the auto incremented ID in the same SQL statement? It would help cut down the over head in my script.
Jul 1 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.