CODEWITHSUNDEEP
sql-serverperldbi
jeffkolez
jeffkolez

Reputation: 648

How do I query a view from SQL server with Perl and DBD::ODBC?

I can query the SQL server DB fine. The problem happens when I try and query a view.

I'm not trying to do anything crazy:

 $sql = 'select * from location_v';
 $stj = $db_destination->prepare($sql);

It keeps dying on the prepare line. Here's what I'm getting back (which isn't all that useful):

DBD::ODBC::db prepare failed: (DBD: st_prepare/SQLPrepare err=-1)

Shouldn't views be handled exactly the same as a table? Thanks in advance.

Upvotes: 0

Views: 947

Answers (3)

jeffkolez
jeffkolez

Reputation: 648

Arg. I found the problem. I had a few queries before I query the view and I wasn't closing my handle before opening a new one. All I needed to do was this before I ran the query:

undef $stj;

Hopefully this helps someone else.

Upvotes: 0

Jonathan Leffler
Jonathan Leffler

Reputation: 754730

Yes, views should be indistinguishable from tables for that query.

Can you actually query the view (rather than just the database in general)?

An error code of -1 is not strongly indicative of anything specific as a problem. It can sometimes be 'no permission', but it could be almost anything.

I suggest looking to see where the problem is by using DBI_TRACE=9 (or maybe smaller numbers) set in the environment (or use $dbh->trace(9)). This should give you lots of information (not all of it comprehensible) about what is going on; it may show you where the trouble actually is.

Upvotes: 1

Aaron Bertrand
Aaron Bertrand

Reputation: 280490

Did you try using the dbo. prefix on the view name (SELECT * FROM dbo.location_v)? Did you check that the view is actually in the dbo. schema? Did you check permissions on the view and/or the base table(s) the view is selecting from? Sadly ODBC does not give a very meaningful error message here, so it could be any number of things, but it is most likely either object not found (because of the prefix, or you are in the wrong database) or permissions.

Upvotes: 3

Related Questions