PHP PDO + PostgreSQL - 交易处理

问题描述：

我有一个问题，我无法找到适当的答案在可用的来源。PHP PDO + PostgreSQL - 交易处理

问题描述 当我打电话使用PDO从PHP Postgres的功能，我没有收到应答（AJAX调用正在等待），结果我得到超时。参数paaasing没有问题。功能工作正常，当我直接在postgres作为交易运行它时：

begin; 
select lcp_mess_ordering(1); 
fetch all po_cursor; 
commit;

我在做什么错？

代码细节：

PHP部分

try { 
$conn = pdoDbConnect(); 
$conn->beginTransaction(); 

$query = 'select lcp_mess_ordering(
      pi_msg_id := :post_pi_msg_id 
     )'; 

$stmt = $conn->prepare($query); 
$stmt->bindParam('post_pi_msg_id', $_POST['msg_id'], PDO::PARAM_INT); 
$stmt->execute(); 

$result = $stmt->fetchAll();        

$stmt->closeCursor(); 
$conn->commit(); 
unset($stmt); 
(...)

PostgreSQL的功能

CREATE OR REPLACE FUNCTION lcp_mess_ordering(IN pi_msg_id integer, OUT po_cursor refcursor, OUT po_err_num integer, OUT po_err_desc text) 
RETURNS record AS 
$BODY$ 
DECLARE 
v_proc_name text; 
v_step integer; 
v_next_step integer; 
v_msg_id integer; 
v_message_row record; 
v_max_step integer; 
mess_cursor cursor for select "MSG_ID", "MSG_STEP" from lct_messages_tmp where "MSG_AUDIT_RD" is null; 

BEGIN 
(...) 

select "MSG_STEP" into v_step from tbr_messages where "MSG_ID" = pi_msg_id; 
select max("MSG_STEP") into v_max_step from tbr_messages where "MSG_AUDIT_RD" is null; 

for v_msg_id in select "MSG_ID" from tbr_messages where "MSG_STEP" = v_step 
loop 
    select "MSG_NEXT_STEP" into v_next_step from tbr_messages where "MSG_ID" = v_msg_id; 
    IF v_next_step = (v_step + 1) THEN 
     update tbr_messages set 
     "MSG_NEXT_STEP" = null, 
     "MSG_AUDIT_MD" = now() 
     where "MSG_ID" = v_msg_id; 
    END IF; 
end loop; 

update tbr_messages set 
"MSG_STEP" = (v_step + 1), 
"MSG_AUDIT_MD" = now() 
where "MSG_STEP" = v_step; 

open mess_cursor; 
loop 
    fetch mess_cursor into v_message_row; 
    exit when not found; 
    IF v_message_row."MSG_STEP" = v_step + 1 THEN 
     update tbr_messages set 
     "MSG_STEP" = v_step, 
     "MSG_AUDIT_MD" = now() 
     where "MSG_ID" = v_message_row."MSG_ID"; 
    END IF; 
end loop; 

OPEN po_cursor FOR 
(...) 
RETURN; 

EXCEPTION 
    WHEN others THEN 
    po_err_num := SQLSTATE; 
    po_err_desc := SQLERRM; 
    RETURN; 



END; 
$BODY$ 
LANGUAGE plpgsql VOLATILE

哪里是你的Ajax代码呢？ – 2014-09-26 11:56:55

答

缺少AJAX部分：

$('body').on('click', '#down_btn', function(e){ 
    e.preventDefault(); 


    var msg_id = $(this).attr('data-msg_id'); 


    var request = $.ajax({  
     url: '../admin_php/mess_move_down.php', 
     type: 'post', 
     data: { 'msg_id': msg_id }, 
     dataType: "html", 
    }); 

    request.done(function(data) { 
     $('#assigned_section').html(data); 
    }); 


});

答

问题是与地方

$conn->commit();

是位于代码。

值得一读：http://php.net/manual/en/pdo.transactions.php

PHP PDO + PostgreSQL - 交易处理

相关推荐