Warning: I’m going to get bombastic here, because this drives me crazy. 99%* of the time, it’s a ridiculous micro-optimization that people have some vague idea makes things ‘better’. This completely ignores the fact that, unless you’re in an extremely tight and busy loop over millions of SQL results all the time, which is hopefully … Read more
Here is a self contained example of how to “execute the anonymous PL/SQL and get the resultset object” import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Types; import oracle.jdbc.OracleTypes; public class CallPLSQLBlockWithOneInputStringAndOneOutputStringParameterAndOneOutputCursorParameter { public static void main(String[] args) throws Exception { DriverManager.registerDriver(new oracle.jdbc.OracleDriver()); // Warning: this is a simple example program : In a … Read more
JDBC will simply name the columns by what is specified in the query – it doesn’t know about table names etc. You have two options: Option 1: Name the columns differently in the query, ie SELECT a.columnName as columnNameA, b.columnName as columnNameB, … from table1 a, table2 b where (WHATEVER) then in your java code … Read more
Empty or not, but doing the following is always faulty: resultSet = statement.executeQuery(sql); string = resultSet.getString(1); // Epic fail. The cursor isn’t set yet. This is not a bug. This is documented behaviour. Every decent JDBC tutorial mentions it. You need to set the ResultSet’s cursor using next() before being able to access any data. … Read more
The default for ResultSet.getInt when the field value is NULL is to return 0, which is also the default value for your iVal declaration. In which case your test is completely redundant. If you actually want to do something different if the field value is NULL, I suggest: int iVal = 0; ResultSet rs = … Read more
You’ll need a beginner’s understanding of PHP, and probably some understanding of relational databases. Pagination is often implemented with some simple query parameters. stackoverflow.com/myResults.php?page=1 The page increments the query parameter: stackoverflow.com/myResults.php?page=2 On the back end, the page value usually corresponds to the limits and offsets in the query that is being used to generate the … Read more
I think you’re missing something here. This is how you can get multiple results from stored procedure using mysqli prepared statements: $stmt = mysqli_prepare($db, ‘CALL multiples(?, ?)’); mysqli_stmt_bind_param($stmt, ‘ii’, $param1, $param2); mysqli_stmt_execute($stmt); // fetch the first result set $result1 = mysqli_stmt_get_result($stmt); // you have to read the result set here while ($row = $result1->fetch_assoc()) { … Read more
I think there’s a way to use less memory (a fixed and not linear amount depending on data cardinality) but this imply to change the method signature. In fact we may print the Json data directly on an output stream as soon as we fetch them from the ResultSet: the already written data will be … Read more
You can’t access PLSQL objects (cases 2 & 5 = package-level objects) from java, see “java – passing array in oracle stored procedure”. You can however access SQL types (case 1 and 4). To get OUT parameters from PL/SQL to java, you can use the method described in one of Tom Kyte’s thread using OracleCallableStatement. … Read more
You can use foreach here just fine. foreach ($rows as $row) { echo $row[‘id’]; echo $row[‘firstname’]; echo $row[‘lastname’]; } I think you are used to accessing the data with numerical indices (such as $row[0]), but this is not necessary. We can use associative arrays to get the data we’re after.