How to do user input with Stored Procedure with Laravel 8

Question

I've installed Laravel 8 on my Linux Mint 20 for my projects and I've searched many sources on how to use stored procedure in Laravel with user input parameters which one of the sources is: How to execute Stored Procedure from Laravel

But when I tried to add data from tambah_transaksisupplier.blade.php it appeared like this :

Illuminate\Database\QueryException SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'exec spSupplier namasupplier, alamatsupplier' at line 1 (SQL: exec spSupplier namasupplier, alamatsupplier)

Here is the piece of tambah_transaksisupplier.blade.php which used to add new data with the stored procedure as an interface for adding data

<p>
    <form action="/home/tambah_transaksisupplier/store_supplier" method="post">
        <h2>TAMBAH SUPPLIER</h2>
        <p>Supplier yang sudah pernah menyuplai sebelumnya, tetap diisi nama dan alamatnya untuk kode transaksi masuk</p><br>
        {{csrf_field()}}
            
            Supplier     :<br>
            <input type="text" name="namasupplier" value=""><br></br>
            Alamat Supplier     :<br>
            <input type="text" name="alamatsupplier" value=""><br></br>

            <input type="Submit" name="tambah_supplier" value="Tambahkan Supplier"><br> </input><br>
    </form>
</p>

Here is the piece of homeController.php which used to processing adding data with stored procedure in the controller file

public function store_supplier(Request $request)
{

    DB::select(DB::raw("exec spSupplier namasupplier, alamatsupplier"),[
        'namasupplier' => $request,
        'alamatsupplier' => $request
    ]);

    return redirect('home/tambah_transaksisupplier');
}

And here is the piece of SQL which contains stored procedure if you need to know

DELIMITER ##
CREATE PROCEDURE spSupplier(vNamaSupplier VARCHAR(255), vAlamatSupplier VARCHAR(255))
BEGIN

    DECLARE kdSup, nSup, aSup, vTMbaru, vKSbaru VARCHAR(255) DEFAULT '';
    DECLARE ada, totdata, caridata INT DEFAULT 0;

    DECLARE cCariNamaAlamat CURSOR FOR
    SELECT kodesupplier, namasupplier, alamatsupplier FROM Supplier;

    SELECT COUNT(*) INTO totdata FROM Supplier;
    SELECT UPPER(vNamaSupplier) INTO @uNamaSupplier;
    SELECT UPPER(vAlamatSupplier) INTO @uAlamatSupplier;
    
    SET caridata = 1;
    OPEN cCariNamaAlamat;
    WHILE caridata<=totdata DO
        FETCH cCariNamaAlamat INTO kdSup, nSup, aSup;
            IF @uNamaSupplier=nSup AND @uAlamatSupplier=aSup THEN
                SET ada=1;
                SELECT (CAST(SUBSTRING(MAX(notransmasuk),4,4)AS INT)+1) INTO @vtmbaru FROM TransaksiMasuk;
                SET vTMbaru = (CASE
                    WHEN @vtmbaru <10 THEN CONCAT('TM-000', @vtmbaru)
                    WHEN @vtmbaru <100 THEN CONCAT('TM-00', @vtmbaru)
                    WHEN @vtmbaru <1000 THEN CONCAT('TM-0', @vtmbaru)
                    WHEN @vtmbaru <10000 THEN CONCAT('TM-', @vtmbaru)
                END);
                INSERT INTO TransaksiMasuk VALUES
                (vTMbaru,NOW(),kdSup);
            END IF;
            SET caridata=caridata+1;
    END WHILE;
    CLOSE cCariNamaAlamat;

    IF ada = 0 THEN
        SELECT (CAST(SUBSTRING(MAX(kodesupplier),4,4)AS INT)+1) INTO @supplierbaru FROM Supplier;
        
        SET vKSbaru =(CASE
            WHEN @supplierbaru <10 THEN CONCAT('S-000', @supplierbaru)
            WHEN @supplierbaru <100 THEN CONCAT('S-00', @supplierbaru)
            WHEN @supplierbaru <1000 THEN CONCAT('S-0', @supplierbaru)
            WHEN @supplierbaru <10000 THEN CONCAT('S-', @supplierbaru)
        END);

        INSERT INTO Supplier VALUES
        (vKSbaru,@uNamaSupplier,@uAlamatSupplier);

        SELECT (CAST(SUBSTRING(MAX(notransmasuk),4,4)AS INT)+1) INTO @vtmbaru FROM TransaksiMasuk;

        SET vTMbaru =(CASE
            WHEN @vtmbaru <10 THEN CONCAT('TM-000', @vtmbaru)
            WHEN @vtmbaru <100 THEN CONCAT('TM-00', @vtmbaru)
            WHEN @vtmbaru <1000 THEN CONCAT('TM-0', @vtmbaru)
            WHEN @vtmbaru <10000 THEN CONCAT('TM-', @vtmbaru)
        END);
                
        INSERT INTO TransaksiMasuk VALUES
        (vTMbaru,NOW(),vKSbaru);

    END IF;
END
##
DELIMITER ;

Can anyone give me a solution to what should I do to repair this? (especially in a controller file)

You don't need to use `DB::raw` when using `DB::select` since it's raw by defaut. Also I think placeholders either need to be `?` or start with `:` — apokryfos, May 05 '21 at 05:31
Is this your answer? https://stackoverflow.com/questions/34497063/how-to-execute-stored-procedure-from-laravel — Shahrukh, May 05 '21 at 06:17

score 0 · Answer 1 · answered May 05 '21 at 06:20

0

i guess you should use the code below:

DB::select('exec spSupplier(?,?)',array($value1,$value2));

answered May 05 '21 at 06:20

Safaar

24
5

How to do user input with Stored Procedure with Laravel 8

1 Answers1