Help Needed: Calling Stored Function from Laravel Controller

[ferjosegonza]

Hi everyone, first of all sorry for the extension but I think that the only way you could understand is by giving you the whole picture.
I hope someone can help me with this issue I'm facing. Here's the context:

• In my current job, I'm working on a large Laravel project. I'm using the MVC paradigm that needs to display data obtained by calling a stored function in a PostgreSQL database using Oracle SQL Developer.
• Below, I'll share the code from the controller where I call the function and the code for the function itself. The problem is that when I call the function directly in the database, it returns the correct amount the user owes. However, when I try to pass the same parameters from the Laravel project's controller, it always returns 0. This means that the function in the database does not validate everything correctly to return the amount owed by the user.
• I should mention that I'm using a syntax to call the function that works correctly for another function in the database. There are differences between the database functions, as the one causing the error can receive a default parameter. I suspect that the error might be related to this, but I'd like to understand how the query is generated from Laravel or how to obtain the correct value.
  I hope my explanation is clear. Any suggestions or help would be greatly appreciated.

Here is the code for the function call that returns the correct value (and which I used as an example for the one causing the error):
`public function tieneTitulo(Request $request){
$ope = $request->operatoria;
$barrio = $request->barrio;
$adju = $request->adju;

    $query = "SELECT usua400.fun_tiene_titulo(:ope, :barrio, :adju, 'T') as respuesta from dual";
    $resultados = DB::select(DB::raw($query), ['ope' => $ope, 'barrio' => $barrio, 'adju' => $adju]);

    //dd($resultados);
    if (empty($resultados)) {
        return response()->json(['message' => 'Sin datos.']);
    } else {
        return response()->json($resultados[0]);
    }
}`

Here is the code for the function call that always returns 0 (and which I'm trying to debug):
`public function consultarAdeuda(Request $request) {
$ope = $request->operatoria;
$barrio = $request->barrio;
$adju = $request->adju;
$fecha = Carbon::createFromFormat('d/m/y', '15/07/24')->format('d-m-y');

    try {
        $result = DB::select('SELECT iprodha.fun_adeuda_mont(:ope, :barrio, :adju, :fecha) as respuesta from dual', [
            'ope' => $ope,
            'barrio' => $barrio,
            'adju' => $adju,
            'fecha' => $fecha,
        ]);

        if ($result === null){
            return response()->json(['respuesta' => 'Sin datos.']);
        } else {
            return response()->json(['respuesta' => $result[0]->respuesta]);
        }
    } catch (\Exception $e) {
        return response()->json(['message' => 'Ocurrió un error al ejecutar la consulta: .'], $e->getMessage());
    }
}`

The code of routes:
Route::get('juridicos/juicios/consultarAdeuda', [JuiciosController::class, 'consultarAdeuda'])->name('juridicos.juicios.consultarAdeuda');

Here is the code for the Oracle database function in Oracle SQL Developer (you can use any of both links):
https://drive.google.com/uc?export=view&id=1MUQd3laGzfw1w2uW5L5X7hPDwmooO-t9
https://drive.google.com/file/d/1MUQd3laGzfw1w2uW5L5X7hPDwmooO-t9/view?usp=drive_link

Thank you very much for your help!

[carlosmintfan]

Hi there,

I don't know that much but I think maybe the problem is that in the non-working example, you're not wrapping the query in DB::raw() while you're doing that in the first example?

[ferjosegonza]

Hi Carlos,
Yes you're right I didn't. The thing is that I already tried that way and in many other ways, without having the correct result. I think do refer to put it in that way:

$query = "SELECT iprodha.fun_adeuda_mont(:ope, :barrio, :adju, :fecha) as respuesta from dual"; $result = DB::select(DB::raw($query), ['ope' => $ope, 'barrio' => $barrio, 'adju' => $adju, 'fecha' => $fecha]);

To follow the same sintax of the other example that works fine. I did it, but it doesn't work because it returns 0. If you see the code of the database's stored function, you'll see that it has many "else" that returns 0 and if none of those unexpected situations ocurr, only then does it return the correct value.

[carlosmintfan]

Hi @ferjosegonza, can you make the SQL function return "error 1", "error 2" etc. instead of 0 in a testing environment?

[ferjosegonza]

I'm not able to modify the function because it's the task of the DBA. Can you explain me why do you suggest that?

[carlosmintfan]

Because it should be the simplest way to debug why the return is null. Maybe you can copy the function to a local database on your own computer? It's essential to have a testing environment where you can play around and debug to be able to work efficiently and solve such issues 🚀

[ferjosegonza]

The thing is that I already test it with the Oracle SQL Developer. I call the stored function with the same values from the Oracle SQL Developer Database and from the Laravel project. From the Oracle SQL Developer it returns the correct value, but from the Laravel project it doesn't. I think that it could be a way in which Laravel generates the sentence or the way that it calls the function.

[carlosmintfan]

Hi @ferjosegonza, please try ->format('Y-m-d'); at the end of the Carbon call.

[ferjosegonza]

I did it and in the log still show "fecha" as a string:
{"ope":{"value":"CR","type":"string"},"barrio":{"value":265,"type":"integer"},"adju":{"value":1,"type":"integer"},"fecha":{"value":"2024-07-15","type":"string"}}

[carlosmintfan]

I know it's a string, but maybe it's formatted the right way now. Do you get the right result or still null now?

[ferjosegonza]

it returns 0, not the right result

[carlosmintfan]

How do you format the date when calling the function from the Oracle SQL Developer?

[ferjosegonza]

The only format that I gave it was with Carbon:
$fecha = Carbon::createFromFormat('d/m/y', '15/07/24')->format('d-m-y');

[carlosmintfan]

And it works in the SQL developer with d-m-y format? Because I think Y-m-d would be best. But I don't know if there's some localization there and so on...hmm..that's a weird one :) I also com across issues in my development that are too hard to be solved right now, but later it works :) I'm not sure if I can really help here anymore :( The best thing would be to important the function to a local environment and return different values from the ifs...

[ferjosegonza]

If I execute the select right in Oracle SQL Developer (the local enviroment) it returns the right value, I mean with that date in d-m-y format so I think maybe the format wouldn't be the problem. What do you mean with localization? I thought that the problem maybe was related to stored funcion permissions but it isn't. Now I'm trying to do it with pure PHP Vanilla inside the Laravel project even though I know that it isn't a good practice, but just to debug the problem and check if the problem is in how Laravel manage the select.

[carlosmintfan]

I mean that dates have different formats in different countries... but yeah, that's a good idea. Are you using PDO now?

[ferjosegonza]

actually I don't, just tried to repeat what was working before