CODEWITHSUNDEEP
phpstored-procedureslaravel-5
Christopher Roberts
Christopher Roberts

Reputation: 31

Calling stored procedure from Laravel 5.3 application results in 'Uncaught PDO Exception'

I am experiencing an issue when calling a stored procedure from within a Laravel 5.3 application. The page script is failing with the following log items being generated within the apache log file:

  1. PHP Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 842018128 bytes)
  2. Uncaught PDOException: SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active

I've attempted to remedy by increasing the memory limit within the php.ini file to no avail.

The errors are generated at this statement:

$pages = DB::select('call sp_get_pages_for_audio_section(1133)');

My stored procedure is as follows:

CREATE PROCEDURE `sp_get_pages_for_audio_section`(IN topic_id INT UNSIGNED)
BEGIN
    SET @id = topic_id;

    SELECT p.id, p.title, p.page_order, p.include_audio, a.filename,
        a.body, a.directions, a.media_type, a.id as asset_id,
        a.play_blank_mp3, a.cc_file, a.cc_swf, plt.title AS plt_title,
        l.id as lesson_id,
    CASE p.page_layout_template
        WHEN 0 THEN FALSE
        ELSE TRUE
    END
        AS has_plt,
    IF ((a.filename IS NOT NULL AND length(a.filename) > 0), 1, 0)
        AS audio_asset_fulfilled,
    IF ((a.filename IS NULL OR a.filename = '') AND (length(a.body) > 0 OR length(a.directions) > 0), 1, 0)
        AS audio_asset_unfulfilled
    FROM pages p
        LEFT JOIN page_layout_templates plt
            ON plt.id = p.page_layout_template
        LEFT JOIN assets a
            ON a.page_id = p.id
                AND a.asset_type = 'audio'
        INNER JOIN topics t
            on t.id = p.parent_topic_id
        INNER JOIN lessons l
            on t.parent_lesson_id = l.id
    WHERE p.parent_topic_id = @id
        ORDER BY page_order ASC;
  END

I isolated the statement call, 

$pages = DB::select('call sp_get_pages_for_audio_section(1133)');

to a function by itself with the same results.

Thanks for any help!

Upvotes: 1

Views: 399

Answers (1)

Christopher Roberts
Christopher Roberts

Reputation: 31

Putting in the below two lines fixed the issue:

$pdo = DB::connection()->getPdo();
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);

Upvotes: 2

Related Questions