Any tips to connect my php to my oracle DB? (FreeBSD 12.1 AMD)

I'm using FreeBSD 12.1 with amd, php73 and I'm trying to connect my oracle database. and I made a few attempts but to no avail, apparently a missing drive is missing for FreeBSD + AMD.

FreeBSD server.privatusdev.com.br 12.1-STABLE FreeBSD 12.1-STABLE #1 r361584M: Wed Sep 2 18:15:32 -03 2020 root@iso.proapps.serveru.us:/usr/obj/usr/src/amd64.amd64/sys/PROAPPS amd64

Well, my first try was connect with PDO:

$db_username = "USERX";
$db_password = "PASSWORD";
$db = "oci:dbname=MYIP:MYPORT/orcl";
$conn = new \PDO($db, $db_username, $db_password);
$stmt = $conn->exec("Select * from MYTABLE");

And i had this error:

[Fail to executed api. Return: "could not find driver" Code: 0, FileLine/Oracle.php:35 exception_type: PDOException]

my second try was connect via odbc_connect

$user = "USERX";
$password = "PASSWORDY";
$ODBCConnection = \odbc_connect("Driver={Devart ODBC driver for Oracle};Direct=true;Host=MYIP;Port=MYPORT;Service Name=orcl;User ID=USERX;password=PASSWORDY", $user, $password);

And, guess what:

[Fail to executed api. Return: "odbc_connect(): SQL error: [unixODBC][Driver Manager]Can't open lib 'Devart ODBC driver for Oracle' : file not found, SQL state 01000 in SQLConnect" Code: 2, FileLineOracle.php:28 exception_type: yii\base\ErrorException]

Well, after understanding that Drive was missing, I started the quest to try to install either the PDO or the PECL responsible for oracle db.

First thing I did was look for oracle in the freebsd packages

(root@server) /home/tiago# pkg search php | egrep -i "oracle|oci|oci8|orcl" --color Exit 1

Okay, if you don't have it in pkg, it could be that it exists in ports. let's look there?

# cd /usr/ports/databases/oracle8-client/ && make install clean
===>  oracle8-client-0.2.0_2 is only for i386, while you are running amd64.
*** Error code 1

Stop.
make: stopped in /usr/ports/databases/oracle8-client
Exit 1

Without losing hope, I tried to think simpler and install via PECL, but before downloading, I saw a suggestion on the internet, to download the SDK and put it in a specific folder and put it in a specific folder and at the time of installation pass the path to that folder with the SDK..

#pecl install oci8-2.2.0

Please provide the path to the ORACLE_HOME directory. Use 'instantclient,/path/to/instant/client/lib' if you're compiling with Oracle Instant Client [autodetect]: instantclient,/opt /oracle/instantclient_12_2

But, i had this error now:

checking Oracle Instant Client library version compatibility... configure: error: Oracle Instant Client libraries libnnz.so and libclntsh.so not found
ERROR: `/tmp/pear/oci8/configure --with-php-config=/usr/local/bin/php-config --with-oci8=instantclient,/opt/oracle/instantclient_19_8/' failed

I tried to install these missing LIBDs, but I couldn't find any packages that install it.

:( at that moment I lost my hopes and I come to ask the community for help

Solution

After almost 3 weeks of fighting, I found a way to get around the problem. Let's go to some considerations, before anything. There is no Oracle ODBC that works well for FreeBSD so the best way to get around this was to find a system that connects to my Oracle using native libs and without having to install a connector on my operating system. BY a miracle of god I remembered that there is .netcore, it talks to oracle using DLL, that is, it doesn't matter which operating system you use, the DLL will always run, you just need to run .netcore on your operating system. for this, follow the steps I followed:

Follow these steps to download and install .netcore on your freebsd x64 (in this option, I left version 3.0.0, his explanation is more complete, but I opted to use the SDK in version 3.1.1) jasonpugsley/installer

After doing this installation, I created a small project in my visual studio to make a quick connection to the database.

using System;
using Oracle.ManagedDataAccess.Client;
namespace oracle
{
    class Program
    {
        static void Main(string[] args)
        {
            using (OracleConnection connection = new OracleConnection("User Id=hr;Password=oracle;Data Source=//localhost:1521/orcl"))
            {
                using (OracleCommand cmd = connection.CreateCommand())
                {
                    try
                    {
                        connection.Open();
                        cmd.BindByName = true;
                        cmd.CommandText = "select * from countries";
                        OracleDataReader reader = cmd.ExecuteReader();

                        Console.WriteLine("rows" + reader.HasRows);

                        while (reader.Read())
                        {
                            Console.WriteLine(reader.GetValue(0));
                            Console.WriteLine(reader.GetString(0));
                        }

                    }
                    catch (Exception e)
                    {
                        Console.WriteLine(e.Message);
                    }
                }
                connection.Close();
            }
            Console.WriteLine("okay, its works guys! its alive. thx odin");
        }
                           
    }
   
}

Ah, I just need to download, via nuget, Oracle for .netcore

Well, so what is the idea that I implemented and is working 100%:

My PHP, when you need to talk to oracle, PHP opens a connection to .netcore and makes this conversation with oracle. I hope I helped, friends!

PS: A special thank you to jasonpugsley for posting this .netcore documentation with freebsd, it helped me a LOT! jasonpugsley