|
santoo has asked for the wisdom of the Perl Monks concerning the following question:
Hi All, I am trying to run some sql query on perl. I am not getting any error, at the same time also the result is always emty. But when I try same query on sql client I am getting data. Any idea to troubleshoot please
my $conn = $OracleSqlData::connection{$OracleSqlData::server};
$sth =$conn->prepare("select * from users");
$sth->fetchrow_array()
updated
Actually above code is within the method and that method is being called in multiple places and it will return result-set... My problem is for some method calls its returning value but for other calls its giving empty result set... its not consistent across all calls
also in log I check for any error randomly I get bellow error also
ORA-03113: end-of-file on communication channel
Session ID: 1168 Serial number: 37417 (DBD ERROR: OCIStmtExecute)
|
Re: Perl Not returning SQL query result
by erix (Prior) on May 11, 2021 at 12:53 UTC
|
After execute you're only half-way; after that, you still have to fetch the row data. See the DBI docs.
| [reply]
[d/l]
[select] |
Re: Perl Not returning SQL query result
by hippo (Bishop) on May 11, 2021 at 13:09 UTC
|
In addition to the advice you have already received you might want to peruse the tutorial Reading from a database by stephen. It's a gentle introduction which will help you with both the concepts and the syntax.
It is also worth mentioning if you are genuinely connecting to an Oracle database that it has a number of non-standard considerations which may affect things. Others who are more versed in that particular RDBMS can help you with those specifics.
| [reply] |
Re: Perl Not returning SQL query result
by 1nickt (Canon) on May 11, 2021 at 12:25 UTC
|
Hi,
Where does $sth come from, other than C&P from the doc?
To quote the DBI doc: For SELECT statements, execute simply "starts" the query within the database engine. Use one of the fetch methods to retrieve the data after calling execute. The execute method does not return the number of rows that will be returned by the query (because most databases can't tell in advance), it simply returns a true value. Hope this helps!
The way forward always starts with a minimal test.
| [reply]
[d/l] |
|
|
| [reply] |
|
|
Again: Reading from a database
(This time, read better: you have to run the fetch* function in a loop, and catch its result, in that example 'Reading from a database' the result values get caught into: $field1, $field2, $field3)
| [reply] |
|
|
| [reply]
[d/l] |
Re: Perl Not returning SQL query result
by Marshall (Canon) on May 12, 2021 at 19:06 UTC
|
I most often use SQLite.
I use $dbh (data base handle) instead of $conn.
First of all, you have to connect to the Database using the DBI module.
my $dbh = DBI->connect("dbi:SQLite:dbname=$dbfile","","",{RaiseError =
+> 1})
or die "Couldn't connect to database: " . DBI->errstr;
If $dbh is zero undefined, then the connect failed!
If the connection works, then I've given some guidance about how to handle future errors.
Your connect syntax will be different for the Oracle database.
Next step is to prepare your SQL statement:
my $get_all_user_rows = $dbh->prepare ("SELECT * FROM users");
I use all CAPS for SQL keywords, but that is just my preference - doesn't matter.
Now you have to execute the prepared SQL statement:
$get_all_user_rows->execute();
Now you have to retrieve the data from that executed statement.
The easiest in this case, would be to ask for a reference to all of the rows.
my $all_row_ref = $get_all_user_rows->fetchall_arrayref;
Now print the data from this 2-D array:
foreach my $row_ref (@$all_row_ref)
{
print "@$row_ref\n";
}
There is more, a lot more to this than the basics I showed above.
I may have made a mistake which the other Monks will quickly point out.
I don't have your DB, but this is, I think a general "roadmap" to get a first result.
| [reply]
[d/l]
[select] |
|
|
my $dbh = DBI->connect("dbi:SQLite:dbname=$dbfile","","",{RaiseError =
+> 1})
or die "Couldn't connect to database: " . DBI->errstr;
If $dbh is zero, then the connect failed!
Um, no. If $dbh ever becomes 0 in that two lines, something very unexpected has happened in perl, i.e. you have triggered a serious bug and/or corrupted memory. (Or you simply have redefined die to return 0 instead of dieing.)
Yes, I'm bean counting again. Sorry.
First, DBI->connect() is documented to return undef on error, a database handle object if the connection succeeds. It should never return 0 at all, or anything else than undef or a blessed reference (i.e. an object).
Second, 0 and undef are two very different values, and while they are treated the same on the left-hand side of or (as a boolean FALSE value), they are not generally exchangeable.
Side note: Around DBI, there is also the special zero value 0E0 that is treated by perl as a boolean TRUE value, returned by execute() and similar methods (do(), execute_array(), execute_for_fetch()) to indicate success with zero rows affected. Some of the core functions (fcntl, ioctl, msgctl, semctl, shmctl, sysseek) return the special value 0 but true for a very similar purpose. That special value is excempt from warning complaints about improper numeric conversions (documented in ioctl).
Third, should DBI->connect() return undef, 0 or any other value that evaluates to FALSE in boolean context, the right-hand side of or will be executed and die will prevent any assignment to $dbh by either exiting the interpreter or by jumping out of a surrounding eval block. So, $dbh starts unassigned, but will never be assigned 0 (unless you have redefined die to return 0).
Alexander
--
Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)
| [reply]
[d/l]
[select] |
|
|
Yes, I'm bean counting again. Sorry. No worries!...Let's get the details right! These threads have a way of relentlessly getting the exact details right.
Ok, the code that I showed will work. 0 and undef are treated the same in this logic, but I did misspeak. I accept your point! My post updated. I was correct in saying, "I may have made a mistake which the other Monks will quickly point out."
Yes, the string "0 but true" is hard coded into Perl from ancient times and will evaluate to 0 when used in a numeric context (and no warning will be issued, even if use warnings; is in effect).
The DBI doesn't use that nowadays. The DBI will return the string "0E0" which evaluates to "True" in a logical context (it is a non-null string), but zero in a numeric context. Yes, this is the DBI's version of "true, but zero rows affected." This value cannot be returned by a SELECT SQL statement, but can be returned by say an ALTER.
In the code shown, if the SELECT does not return any data, the array_ref will be a reference to an array with zero elements. There was another thread recently where this was discussed at length and the specific point that the returned scalar value from execute() won't work for a SELECT.
I am not quite sure about your Third point. If the DB connect fails, this code will DIE. The idea of an eval{} block was not under consideration here. The OP was asking some very basic questions and I tried (perhaps not successfully) to give a simple "roadmap".
But yes, in the context of an Oracle DB with username and password, there should be some way to recover and get better credentials. In the SQLite world, there is no username or password. Connect will fail if 1) wrong file name, 2)wrong path to that file name, or 3)wrong permissions or perhaps 4) corrupt DB file. I copied my connect code from a program that is in its 6th year of production. In this code, the user doesn't have anything to say about things. If the code that this is in bombs, there is a serious problem that my Perl code cannot fix.
Update: SQLite is an amazing critter. It generates some journal files as it works. It is possible for that thing to recover itself to a known ACID state even after a serious event like power fail. I have never personally seen (4) corrupt DB file because SQLite is much better at recovery than you might think. But I have communicated with others who have seen this.
https://en.wikipedia.org/wiki/ACID
| [reply]
[d/l] |
|
|
|
|
|
|
Re: Perl Not returning SQL query result
by Anonymous Monk on May 12, 2021 at 15:34 UTC
|
prepare simply evaluates the SQL text to create a reusable handle which can then be used to execute the same query any number of times without evaluating the SQL text again. You must then execute the query, possibly supplying a list of values for any ? placeholders which appear, which will be used in this particular execution of the prepared query. Now, you can fetch the results. (For simple, one-time queries, this can simply be done in one step: prepare, then execute, and return the results-handle.) | [reply] |
|
|
Title Re: Perl Not returning SQL query result
Author Anonymous Monk
Reputation 0
Reason for reaping Admin.
Votes (keep/reap/edit) 0/0/0
... what?
| [reply]
[d/l] |
|
|
wups. sorry. hit the button on the wrong node. restored.
| [reply] |
|
|
