Admiloper: March 2017

Thursday, March 16, 2017

Autocomplete with PHP, Jquery UI and Oracle - 2

searchUser.php

<?php
session_start();
date_default_timezone_set('EST');

    if ($_SERVER['REQUEST_METHOD'] === 'POST')
    {
        foreach ($_POST as $key => $value)
                eval("\$$key = \"$value\";");
    }

$userName = trim($_GET['term']);
$userName = strtoupper($userName);

putenv("TNS_ADMIN=/opt/.wallet");
putenv("ORACLE_HOME=/app/oracle/product/11.2.0.3");
putenv("LD_LIBRARY_PATH=/app/oracle/product/11.2.0.3/lib");

$conn = oci_connect("SYS", "password", "mydatabase", null, OCI_SYSDBA);
//$conn = oci_connect("/", "", "ITSERVICE", "AL32UTF8", OCI_CRED_EXT);

if (!$conn) {
        $m = oci_error();

        echo $m['message'] . "\n";
        exit;
} else {
        //$sql = 'BEGIN system.exposed_to_low_privs_users.GET_ACCOUNT_STATUSES(:P_OUT); END;';
        //$sql = "select username,ACCOUNT_STATUS from dba_users where username like '" . $userName . "%'";
        $sql = "select username,ACCOUNT_STATUS from dba_users order by USERNAME";
        $stmt = oci_parse($conn,$sql);

        // Bind the output parameter

        oci_execute($stmt);

                //oci_execute($cursor);

                $return_array=array();

                while (($row = oci_fetch_array($stmt, OCI_BOTH)) != false) {
                        $row_array['id'] = $row['USERNAME'];
                        $row_array['value'] = $row['USERNAME'];
                        $row_array['label'] =$row['USERNAME'];

                        //array_push($return_array,$row['USERNAME'],$row['ACCOUNT_STATUS']);
                        array_push($return_array,$row_array);
                }
                //var_dump($return_array);
                echo json_encode($return_array);
      }

// Close the Oracle connection
oci_free_statement($stmt);
oci_close($conn);
?>

Autocomplete with PHP, Jquery UI and Oracle - 1

searchUserUI.php
<?php

session_start();
date_default_timezone_set('EST');

echo "
<!doctype html>
<html>
<head>
<TITLE>jQuery AJAX Autocomplete - Country Example</TITLE>

";

echo '
<link rel="stylesheet" href="//code.jquery.com/ui/1.12.1/themes/smoothness/jquery-ui.css">
<link rel="stylesheet" href="//code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css">
<link rel="stylesheet" href="/css/autocomplete.css">
<script src="//code.jquery.com/jquery-1.12.4.js"></script>
<script src="//code.jquery.com/ui/1.12.1/jquery-ui.js"></script>
';

echo "
<script>
$(document).ready(function () {

$(\"#userName\").autocomplete({
            dataType: \"json\",
            minLength: 2,
            source: \"searchUser.php\",
            select: function(event,ui) {
                $(\"#userName\").val(ui.item.username);
            }
});

});

</script>
";
echo "
<script>

$(function(){

       $(document).on('click', \"#checkUserSessions\", function (e) {
                var user=$(\"#userName\").val();
                e.preventDefault();                $(\"#typeinUserName\").hide();

                var inputData=$(\":input\").serializeArray();

                        $.ajax({
                                type: \"POST\",
                                url: \"/oraoperation/showUserActiveSessions.php\",
                                dataType : 'html',
                                data: inputData,
                                success: function(data, textStatus, jQxhr ){
                                        $(\"#contentDiv\").html(data);
                                        },
                                error: function( jqXhr, textStatus, errorThrown ){
                                        $(\"#typeinUserName\").show();
                                        $(\"#messageDiv\").html(errorThrown);
                                            console.log( errorThrown );
                                        }
                        });
            });

});
</script>
";

echo "
</head>
<body>

";

                echo "<table border=0 id=\"outLineTable\" class=\"centretable\" width=\"100%\">";
                echo "<tbody id=\"typeinUserName\">";
                echo "<tr>";
                echo "<td align=\"center\">";

                echo "<table border=1 id=\"sessionContentTable\">";

                echo "<form id=\"chkDBSessions\" method=\"POST\">";
                echo "<tr>";
                echo "<td>Oracle User Name: </td> <td><input type=\"text\" id=\"userName\" name=\"userName\" class=\"search\"/>";
                echo "</td>";
                echo "</tr>";

                echo "</table>";
                echo "</td>";
                echo "</tr>";
                echo "<tr>";
                echo "<td align=\"center\">";
                echo "<input type=\"submit\" id=\"checkUserSessions\" name=\"checkUserSessions\" value=\"Check User Sessions\"/>";
                echo "</td>";
                echo "</tr>";
                echo "</form>";
                echo "</tbody>";
                echo "<tr>";
                echo "<td>";
                echo "<div id=\"messageDiv\" class=\"mssgDiv\">";
                echo "<md></md>";
                echo "</div>";
                echo "</td>";
                echo "</tr>";

                echo "</table>";
echo "</body>
</html>
";

?>

Monday, March 6, 2017

PHP and Oracle oci_connect() fails sometimes

In system level, I can connect to database using the wallet name, but sometimes, got below error:

1) PHP Warning: oci_connect(): ORA-12715: invalid character set specified
2) ORA-01017: invalid username/password; logon denied
3) PHP Warning: oci_connect(): OCIEnvNlsCreate() failed. There is something wrong with your system

PHP and Oracle oci_connect() fails sometimes, I tried change Character Set, cannot fix it.

In PHP,
putenv("TNS_ADMIN=/opt/.wallet");
putenv("ORACLE_HOME=/usr/app/oracle/product/11.2.0.3");
putenv("LD_LIBRARY_PATH=/usr/app/oracle/product/11.2.0.3/lib");

$conn = oci_connect("/", "", "WalletName", null, OCI_CRED_EXT);

In /etc/sysconfig/httpd:

export ORACLE_HOME=/usr/app/oracle/product/11.2.0.3
export LD_LIBRARY_PATH=/usr/app/oracle/product/11.2.0.3/lib
export TNS_ADMIN=/opt/.wallet