How to do user input with Stored Procedure with Laravel 8

I've installed Laravel 8 on my Linux Mint 20 for my projects and I've searching many source how to use stored procedure in Laravel with user input parameters which one of 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 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 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 solution what should I do to repair this? (especially in controller file)



Read more here: https://stackoverflow.com/questions/67395582/how-to-do-user-input-with-stored-procedure-with-laravel-8

Content Attribution

This content was originally published by pup_in_the_tree at Recent Questions - Stack Overflow, and is syndicated here via their RSS feed. You can read the original post over there.

%d bloggers like this: