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

Please excuse my complete newbieness. This problem may actually be a SQL syntax error, but I don't know enough to tell. I'm executing a command to get users who are logged into some pcs (defined by a list) and then dump the results into a SQL (MS) table. I'm using win32::odbc to connect to the database (connectivity appears to be fine). 
my($db) = new Win32::ODBC("user_logons");
while (my $machine = <MACHINEFILE>) {
            
      my @Results = ();            
          @Results = `psloggedon.exe \\\\$machine`;
      my $LocUsr = $Results[6];
      [...]
      
      $db->Sql("INSERT INTO <Data> (pc_name, user_name,time+date) " . 
+"VALUES ($machine, $LocUsr, GETDATE())");
}

[download]
The table name "Data" and field names are correct. $machine and $LocUsr variables are defined - they seem fine when I print. The error message I get is "Can't call method SQL on an undefined value at line 46, <machinefile> line 1". Obviously, if that syntax looks ok, I'll recheck connectivity-type stuff. Any wisdom dispensed much appreciated!

Replies are listed 'Best First'.
Re: win32::odbc/ SQL error
by Coruscate (Sexton) on May 23, 2003 at 01:16 UTC

    I am 90% sure that you're problem is that the database connection is failing. This would result in your database handle being undefined. Invalid username/password combo, non-exitsent database, something like that. The fix is to change your first line to the following and examine the error message produced: 

    my $db = new Win32::ODBC('user_logons')
 or die "db connect failed: $!";

    [download]

    If the above content is missing any vital points or you feel that any of the information is misleading, incorrect or irrelevant, please feel free to downvote the post. At the same time, please reply to this node or /msg me to inform me as to what is wrong with the post, so that I may update the node to the best of my ability.

[reply]
[d/l]
      Yes, of course I should have put error handling in. D'oh! However, after doing so, I'm not getting any connect errors. I'll go and check those field names again...
[reply]
        ... <Data> probably should just be data or data when you have table or column names that have spaces in them you use square brackets ex: SELECT data.first name, data.last name, machine.user name FROM data, user WHERE ... blah, blah...the error you are seeing is because the DB is telling it.. <DATA> is not a name of a table unless you named your table '<DATA>'?
        I do have one suggestion... try DBI, just my 2cents... JamesNC
[reply]
        After inserting the error checking, are you geting the same error message? Your previous error message indicated that the $db variable was not defined. That indicates that your 'new' statement is failing. Fix that before moving on.

        I agree with JamesNC. I would use DBI. The knowledge you gain from using it on MS SQL would be (mostly) transferable to Oracle, mysql, etc. Just my 2 cents.

        Most importantly, don't give up. Keep plugging away. It's worth it.

[reply]
Re: win32::odbc/ SQL error
by tcf22 (Priest) on May 23, 2003 at 15:12 UTC
    It really looks like the connection is failing. Also you really should error trap everything, even the execution of Sql(). On a call to new Win32::ODBC::Error() returns the last error, I don't believe $! contains the error message. Additionally it looks like your SQL had flaws. I assume the table isn't '<DATA>', so it should be written as DATA or [DATA]. Unless $machine and $LocUsr are being inserted into numeric fields you need to single quote them.

    Maybe try something like this: 
    my $db;
if(!($db = new Win32::ODBC('user_logons'))){
    die 'Connect failed: ' . Win32::ODBC::Error() . "\n";
}
 
my $sql = "INSERT INTO [Data] (pc_name, user_name,time+date) VALUES ('
+$machine', '$LocUsr', GETDATE())";

if ($db->Sql($sql)) {
    print "Sql Error: " . $db->Error() . "\n";
    print "SQL: $sql\n";
}

    [download]
[reply]
[d/l]

      Thanks, everyone, for pointing out my SQL syntax errors - while working on it myself last night, I realised the problem with the table name and lack of single quotes for the variables, but it's good to get confirmation.

      As for the suggestions re the DBI module, I did consider that, but since I'm such a newbie, I thought Win32::ODBC looked "easier". I should have a look at it again in the light of this very good learning experience.

      Thanks especially tcf22 for the excellent error-checking code. I very stupidly didn't specify a username/password for the connection, so that was where it was failing - the error report pointed that out very clearly!

[reply]

Back to Seekers of Perl Wisdom