Category: Technology

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);

Android 11 Arrived!

This morning, my phone was very slow. It got progressively worse — finally getting to a point where there was a ten second lag between touching something and a response. I was trying to reboot because closing all of the apps didn’t do anything. The phone locked instead. And, when I woke the screen back up, I had a strange circle arrow icon in the notification bar. It turns out my phone had been downloading an OS update. It was soon ready to install, and I was actually able to use my phone again. Installed the update — that took a long while too — and voila, I’ve got Android 11 on a TCL T770B. Woohoo!

Excel – Converting Unix Timestamp to Human Readable Date(time)

You can use the formula =(B2/86400)+DATE(1970,1,1) to convert a unix epoch time to a human readable date (or date time). In my case, I have the unix timestamp in microseconds so I’ve got to divide by 86400000. The value you get is a not-so-meaningful float … but that’s actually a date.

Select a date format to display the value as a date

Or chose a custom format and use something like “m/d/yyyy hh:mm” to display a date and time.

Blocking Device Internet Access

We block Internet access for a lot of our smart devices. All of our control is done through the local server; and, short of updating firmware, the devices have no need to be chatting with the Internet. Unfortunately, our DSL modem/router does not have any sort of parental control, blocking, or filtering features. Fortunately, ISC DHCPD allows you to define per-host options. Setting the router to the device’s IP (0.0.0.0 may work as well) allows us to have devices that can communicate with anything on their subnet without allowing access out to other subnets or the Internet.

Viewing and recording packets using tshark

This time, I’m writing this down so I don’t have to keep looking it up. To display some packet info to the screen while writing a network capture to a file, include the -P option (older versions of tshark used -S)

2021-04-18 13:58:58 [lisa@server ~]# tshark -f "udp port 123" -w /tmp/ntpd.cap -P
Running as user "root" and group "root". This could be dangerous.
Capturing on 'enp0s25'
1 0.000000000 10.x.x.x → x.x.x.18 NTP 90 NTP Version 4, client
2 3.898916081 10.x.x.x → x.x.x.199 NTP 90 NTP Version 4, client
3 7.898948128 10.x.x.x → x.x.x.20 NTP 90 NTP Version 4, client
4 7.928749596 x.x.x.20 → 10.x.x.x NTP 90 NTP Version 4, server
5 9.898958577 10.x.x.x → x.x.x.76 NTP 90 NTP Version 4, client
6 9.949450324 x.x.x.76 → 10.x.x.x NTP 90 NTP Version 4, server
7 10.898981132 10.x.x.x → x.x.x.185 NTP 90 NTP Version 4, client
8 11.009163093 x.x.x.185 → 10.x.x.x NTP 90 NTP Version 4, server

JQuery – Finding a set of checkboxes

A corollary to my JavaScript modifying checkbox values when the box is checked or unchecked … I needed a way to reset the form (in my form, the default is for the boxes to be checked and the value to be 1). The following code identifies all checkboxes with a particular class, checks them, and sets the value to 1.

/**
 * This function checks off each checkbox of the input class
 *
 * @param {string} strCheckboxClass     Name of class identifying in-scope checkboxes
 * @return {null} 
 *
 * @example
 *
 *     checkAllDatabases ('MyBoxes');
 */
 function checkAllDatabases(strCheckboxClass){
    arrayCheckboxes = $('.'+strCheckboxClass);
    for(i = 0; i < arrayCheckboxes.length; i++) {
        $( '#'+arrayCheckboxes[i].name).prop( "checked", true );
        $( '#'+arrayCheckboxes[i].name).val(1);
    } 
}

Changing checkbox value when (un)checked

This bit of code handles another rather esoteric scenario — I have a generic “go to this URL and download the resultant Excel file” JavaScript function. This is because I write a lot of reporting tools and didn’t want to write a lot of code for each new tool. The template is an input form with a submit button that calls the generic function. Params for the elements on the form from which values are read, the URL to call to generate the report, and the POST elements into which each corresponding form value is inserted gets stuffed. Works great for text inputs. Works fine for drop-downs. But the value of a checkbox is really a combination of the potential value (from the value tag) and the checked state. That is — my Button 1 has a potential value of 1, but if the box is checked or not is really important.

Instead of attempting to determine the type of element in each form input so I can evaluate the checked condition, I decided to just change the value when the checkbox state is changed. Now Button 1 has a potential value of 0 when unchecked and a potential value of 1 when checked. I don’t need to know if the box is checked because the value answers that question. So passing along button1’s value to my URL lets the target site know if I want whatever Button 1 represents. (In this case, users are able to select from a list of seven data sources — smaller numbers of data sources reduce the query time but also fail to provide the most robust report).

The JavaScript to handle changing the checkbox value when the checked state changes:

$("#button1").change(function () {
    if ($("#button1").is(':checked')) {
        $("#button1").val(1);
    }
    else{
        $("#button1").val(0);
    }
});

$("#button2").change(function () {
    if ($("#button2").is(':checked')) {
        $("#button2").val(1);
    }
    else{
        $("#button2").val(0);
    }
});

The HTML defining these two checkboxes:

<input type="checkbox" id="button1" name="button1" value="1" checked><label for="ngmss">Thing 1</label>
<input type="checkbox" id="button2" name="button2" value="1" checked><label for="ngmss">Thing 2</label>