Category: Database

PHP oci_bind_by_name Caveat (a.k.a. a reminder to read the documentation)

This is readily apparent when you actually read the documentation for oci_bind_by_name … but, if you quickly skim through the doc and get mostly what it’s telling you, you can lose data when implementing oci_bind_by_name in your code. That’s because the variable that gets bound into the name is a reference. It’s right there in the description

And again in the more verbose part of the description

Well? They really mean It!

I usually iterate through arrays with for $x=0; $x<count($arrayOfData);$x++ — which is fine because $arrayOfData[$x] is a valid location in memory that contains the data I want. I had a program, though, that accepted user input. Input that frequently contains a bunch of extraneous newline characters in the middle of the data. To avoid making the users clean up their input, I just threw an array_filter on the input. But I don’t need the reorganized data, so I didn’t “waste” the cycles to re-index the filtered array. Instead, I used

$iIterator=0;
for( $itemInput in array_filter( $arrayOfData ) ) {
     oci_bind_by_name($stmt, ‘:placeholder’.$iIterator++, $itemInput);
}

Which is fine in most circumstances. But, with oci_bind_by_name … the memory address where $inputItem happened to be stashed is bound to the names :placeholder0, :placeholder1, …, :placeholdern – that memory address happened to still contain the last element of arrayOfData I happened to stash there because the server isn’t so busy that memory is being reallocated a fraction of a second later. But this loop does not bind each element of arrayOfData to its corresponding placeholder — instead of saying “select * from MyTable where ID IN (1,2,3,4,5)” … I had “select * from MyTable where ID IN (5,5,5,5,5)”.

In this case you need to use the array index

for( $x = 0; $x < count( $arrayOfData ); $x++ ) {
     $strPlaceholderName = ":inputclli".$x;
     oci_bind_by_name($stmt, $strPlaceholderName, $arrayOfData[$x]);
}

So each unique memory address is bound to a placeholder.

Adding MariaDB/MySQL Users

Quick notes on creating a database user — MariaDB and MySQL use a combination of username and source host to determine access. This means ‘me’@’localhost’ and ‘me’@’remotehost’ can have different passwords and privilege sets. How do you know what the hostname is for your connection? I usually try to connect and read the host from the error message — it’ll say ‘someone’@’something’ cannot access the database.

# Create a user that is allowed to connect from a specific host
create user 'username'@'hostname' identified by 'S0m3P@s5w0rd';
GRANT ALL PRIVILEGES ON dbname.* TO 'username'@'hostname';

# Create a user that is allowed to connect from a specific IP
create user 'username1'@'10.5.1.2' identified by 'S0m3P@s5w0rd';
GRANT ALL PRIVILEGES ON dbname.* TO 'username1'@'10.5.1.2';

# Create a user that is allowed to connect from database server
create user 'username2'@'localhost' identified by 'S0m3P@s5w0rd';
GRANT ALL PRIVILEGES ON dbname.* TO 'username2'@'localhost';

# Create a user that is allowed to connect from any host
create user 'username3'@'%' identified by 'S0m3P@s5w0rd';
GRANT ALL PRIVILEGES ON dbname.* TO 'username3'@'%';

# Flush so new privileges are effective
flush privileges;

# View list of database users
SELECT User, Host FROM mysql.user;
+----------------+------------+
| User           | Host       |
+----------------+------------+
| username3      | %          |
| username2      | 10.5.1.2   |
| username       | hostname   |
| root           | 127.0.0.1  |
| root           | ::1        |
| root           | localhost  |
+----------------+------------+
6 rows in set (0.000 sec)

Oracle Collection Instead of Dual

I’m still retrofitting a bunch of SQL queries to use bind_by_name and came across a strange scenario. I created a recursive query (STARTS WITH / CONNECT BY PRIOR) but I needed to grab the original value too. The quickest way to accomplish this was to union in something like “select 12345CDE as equipment_id from dual”. But the only way to get a bunch of these original values grafted onto the result set is to iterate through the array once to build my :placeholder1, :placeholder2, …, placeholderN placeholders and then iterate through the array again to bind each placeholder to its proper value.

I’ve been working with Oracle collections for LIKE and IN queries, and thought I could use a table that only exists within the query to glom the entire array into a single placeholder. It works! A query like

select column_value equipment_id from TABLE(sys.ODCIVARCHAR2LIST('12345CDE', '23456BCD', '34567ABC') );

Adds each of the values to my result set.

Which means I can use a query like “select column_value as equipment_id from TABLE(:myIDs)” and bind the collection to :myIDs.

Oracle – Collections and IN or LIKE Queries

I’ve been retrofitting a lot of PHP/SQL queries to use oci_bind_by_name recently. When using “IN” clauses, you can iterate through your array twice, but it’s an inefficient approach.

// Build the query string with a bunch of placeholders
$strQuery = "select Col1, Col2, Col3 from TableName where ColName IN (";
for($i=0; $i < count($array); $i++){
    if($i > 0){
        $strQuery = $strQuery . ", ";
    }
    $strQuery = $strQuery . ":bindvar" . $i;
}
$strQuery = $strQuery . ")";
... 
// Then bind each placeholder to something
for($i=0; $i < count($array); $i++){
    oci_bind_by_name($stmt, ":bindvar".$i, $array[$i]);
}

Building a table from the array data and using an Oracle collection object creates cleaner code and avoids a second iteration of the array:

$strQuery = "SELECT indexID, objName FROM table WHERE objName in (SELECT column_value FROM table(:myIds))";
$stmt = oci_parse($conn, $strQuery);

$coll = oci_new_collection($kpiprd_conn, 'ODCIVARCHAR2LIST','SYS');
foreach ($arrayValues as $strValue) {
     $coll->append($strValue);
}
oci_bind_by_name($stmt, ':myIds', $coll, -1, OCI_B_NTY);
oci_set_prefetch($stmt, 300);
oci_execute($stmt);

A simple like clause is quite straight-forward

$strNameLikeString = "SomeName%";
$strQuery = "SELECT ds_dvrsty_set_nm from ds_dvrsty_set WHERE ds_dvrsty_set_nm LIKE :divsetnm ORDER BY ds_dvrsty_set_nm DESC fetch first 1 row only";

$stmt = oci_parse($connDB, $strQuery);
oci_bind_by_name($stmt, ":divsetnm", $strNameLikeString);
oci_set_prefetch($stmt, 300);
oci_execute($stmt);

But what about an array of inputs essentially reproducing the LIKE ANY predicate in PostgreSQL? There’s not a direct equivalent in Oracle, and iterating through the array twice to build out a query WHERE (Field1 LIKE ‘Thing1%’ OR Field1 LIKE ‘Thing2%’ OR Field1 LIKE ‘Thing3%’) is undesirable. The with EXISTS allows me to create a LIKE ANY type query and only iterate through my array once to bind variables to placeholders using the same collection approach as was used with the IN clause. 

$arrayLocs = array('ERIEPAXE%', 'HNCKOHXA%', 'LTRKARXK%');
$strQuery = "SELECT location_id, clli_code FROM network_location WHERE EXISTS (select 1 FROM TABLE(:likelocs) WHERE clli_code LIKE column_value)";
$stmt = oci_parse($connDB, $strQuery);

$coll = oci_new_collection($connDB, 'ODCIVARCHAR2LIST','SYS');
foreach ($arrayLocs as $strLocation) {
    $coll->append($strLocation);
}
oci_bind_by_name($stmt, ':likelocs', $coll, -1, OCI_B_NTY);
oci_execute($stmt);
print "<table>\n";
print "<tr><th>Loc ID</th><th>CLLI</th></tr>\n";
while ($row = oci_fetch_array($stmt, OCI_ASSOC+OCI_RETURN_NULLS)) {
    print "<tr><td>" . $row['LOCATION_ID'] . "</td><td>" . $row['CLLI_CODE'] . "</td></tr>\n";
}
print "</table>\n";

There are many different collection types in Oracle which can be used with oci_new_collection. A full list of the system collection types can be queried from the database. 

SELECT * FROM SYS.ALL_TYPES WHERE TYPECODE = 'COLLECTION' and OWNER = 'SYS';

PostgreSQL Sequences

I’m having a problem with a database refusing to change INSERTS to UPDATES on the ON CONFLICT condition — it insists that a ‘duplicate key value violates unique constraint’. A little time with a search engine tells me that sequences can get out of sync, and then you cannot insert items into the table. How do you know your sequence is out of sync?

SELECT NEXTVAL((SELECT PG_GET_SERIAL_SEQUENCE('"table_name"', 'name_of_column_with_sequence'))) as "NextValue", MAX("name_of_column_with_sequence") AS "Max Value" FROM "table_name";

So null seems like it would be a problem!

For future reference, when the next value is smaller than the max value in the table, the solution is to set the series value based on the max value

SELECT SETVAL((SELECT PG_GET_SERIAL_SEQUENCE('"table_name"', 'name_of_column_with_sequence')), (SELECT (MAX("name_of_column_with_sequence") + 1) FROM "table_name"), FALSE);

Oracle Function – Keeping Null Records With LISTAGG

I have been using LISTAGG to group a bunch of records together to be presented in a single HTML table cell. Problem is LISTAGG doesn’t do anything with null field values. As such, the data doesn’t line up across columns. The three ID values have two string values, which basically get centered in the cell. You cannot tell which ID value goes to which name value.

By adding a concatenation to the LISTAGG value, something will be included in the result set even when the record value is null.

Voila — records line up and I can tell the first ID doesn’t have an associated string value.