php - 存储过程无法使用 mysql 中的 php 代码传递 xml 作为输入

Question

我正在将 xml 传递给存储过程。它从 xml 字符串中获取值并将其插入表中。这就是它应该如何工作的。

当我从 phpmyadmin 手动点击时，它直接工作正常。但是当我从 php 代码执行它时，它会读取第一行，其中代码从 xml 字符串中提取值，然后返回它。我还验证了 xml 。 xml中没有错误。我还在 php 和存储过程中添加了 sql 异常处理代码。但它什么也没显示。

这是我的 xml -

<CaptureResults><UsrId>15</UsrId><SnsType>MediaSensorAbr1</SnsType><HostType>AndroidMobile</HostType><SessionId>5</SessionId><MaxNullPrct>1.1</MaxNullPrct><SubjExperience>0</SubjExperience><AssetName>Curry Top 10 Shots</AssetName><SessionDuration>00:07:40</SessionDuration><Vendor>Apple</Vendor><HTTP_4_x_x_x_Errors>9</HTTP_4_x_x_x_Errors><AvgBR>19089</AvgBR><HostName>fbba54e81e15fa45</HostName><TextBundle>[]</TextBundle><StreamType>Other</StreamType><AssetURL>http:\/\/tinyurl.com/hqk63rq</AssetURL><RtspErrors>7</RtspErrors><TimePerProfile></TimePerProfile><RtmpErrors>7</RtmpErrors><MinNullPrct>1.3</MinNullPrct><AudioBundle>[]</AudioBundle><Client>GEMAndroidSDKPlayer</Client><HostIP>10.73.234.252</HostIP><RtspRespFailure>10</RtspRespFailure><SnsId>146295290680851</SnsId><HostId>1645</HostId><CDN>Akamai</CDN><StartTime>2016-05-16 14:16:51</StartTime><HttpRespFailure>4</HttpRespFailure><Distributor>tinyurl.com</Distributor></CaptureResults>

这是存储过程代码 -

     MY_BLOCK$$: BEGIN
 DECLARE EXIT HANDLER FOR SQLEXCEPTION 
 insert into tmptable select "",'SQLException invoked';
 DECLARE EXIT HANDLER FOR 1062 
         insert into tmptable select "", 'MySQL error code 1062 invoked';
  DECLARE EXIT HANDLER FOR 1048 
         insert into tmptable select "", 'MySQL error code 1048 invoked';
 DECLARE EXIT HANDLER FOR SQLSTATE '23000'
 insert into tmptable select "", 'SQLSTATE 23000 invoked';

insert into tmptable select "","sp started";

insert into tmptable select  "",CONCAT_WS(",","2",EVENT_VAL);

START TRANSACTION;
select @HostId_for_event_key := ExtractValue (EVENT_VAL, '\/\/HostId/text()');
select @event_dttm := ExtractValue (EVENT_VAL, '\/\/StartTime/text()');


 insert into tmptable select  "",CONCAT_WS(",","4",EVENT_VAL,@HostId_for_event_key);
Select @SnsId_for_event_key := ExtractValue (EVENT_VAL, '\/\/SnsId/text()');
set @utc_time_for_event_key = CONVERT_TZ(@event_dttm,'US/Pacific','UTC');
set @utctime_remove_dashes = DATE_FORMAT(@utc_time_for_event_key,"%Y%m%d%h%i%s.%s");
set @event_key =  concat(@HostId_for_event_key,'.',@SnsId_for_event_key,'.',@utctime_remove_dashes);
Select @UsrId := ExtractValue (EVENT_VAL, '\/\/UsrId/text()');
Select @HostId := ExtractValue (EVENT_VAL, '\/\/HostId/text()');
Select @SnsId := ExtractValue (EVENT_VAL, '\/\/SnsId/text()');
Select @SessionId := ExtractValue (EVENT_VAL, '\/\/SessionId/text()');
Select @HostIP := ExtractValue (EVENT_VAL, '\/\/HostIP/text()');
Select @HostName := ExtractValue (EVENT_VAL, '\/\/HostName/text()');
Select @HostType := ExtractValue (EVENT_VAL, '\/\/HostType/text()');
Select @StartTime := ExtractValue (EVENT_VAL, '\/\/StartTime/text()');
Select @SessionDuration := ExtractValue (EVENT_VAL, '\/\/SessionDuration/text()');
Select @AssetURL := ExtractValue (EVENT_VAL, '\/\/AssetURL/text()');
Select @AssetName := ExtractValue (EVENT_VAL, '\/\/AssetName/text()');
Select @VideoBundle := ExtractValue (EVENT_VAL, '\/\/VideoBundle/text()');
Select @AudioBundle := ExtractValue (EVENT_VAL, '\/\/AudioBundle/text()');
Select @Vendor := ExtractValue (EVENT_VAL, '\/\/Vendor/text()');
Select @StreamType := ExtractValue (EVENT_VAL, '\/\/StreamType/text()');
Select @Client := ExtractValue (EVENT_VAL, '\/\/Client/text()');
Select @Distributor := ExtractValue (EVENT_VAL, '\/\/Distributor/text()');
Select @CDN := ExtractValue (EVENT_VAL, '\/\/CDN/text()');
Select @HTTP_4_x_x_x_Errors := ExtractValue (EVENT_VAL, '\/\/HTTP_4_x_x_x_Errors/text()');
Select @HTTP_5_x_x_x_Errors := ExtractValue (EVENT_VAL, '\/\/HTTP_5_x_x_x_Errors/text()');
Select @RtspErrors := ExtractValue (EVENT_VAL, '\/\/RtspErrors/text()');
Select @RtmpErrors := ExtractValue (EVENT_VAL, '\/\/RtmpErrors/text()');
Select @HttpRespFailure := ExtractValue (EVENT_VAL, '\/\/HttpRespFailure/text()');
Select @RtspRespFailure := ExtractValue (EVENT_VAL, '\/\/RtspRespFailure/text()');
Select @TimePerProfile := ExtractValue (EVENT_VAL, '\/\/TimePerProfile/text()');
Select @TPP_1 := ExtractValue (EVENT_VAL, '\/\/TPP_1/text()');
Select @TPP_2 := ExtractValue (EVENT_VAL, '\/\/TPP_2/text()');
Select @TPP_3 := ExtractValue (EVENT_VAL, '\/\/TPP_3/text()');
Select @TPP_4 := ExtractValue (EVENT_VAL, '\/\/TPP_4/text()');
Select @TPP_5 := ExtractValue (EVENT_VAL, '\/\/TPP_5/text()');
Select @TPP_6 := ExtractValue (EVENT_VAL, '\/\/TPP_6/text()');
Select @TPP_7 := ExtractValue (EVENT_VAL, '\/\/TPP_7/text()');
Select @TPP_8 := ExtractValue (EVENT_VAL, '\/\/TPP_8/text()');
Select @TPP_9 := ExtractValue (EVENT_VAL, '\/\/TPP_9/text()');
Select @TPP_10 := ExtractValue (EVENT_VAL, '\/\/TPP_10/text()');
Select @TPP_11 := ExtractValue (EVENT_VAL, '\/\/TPP_11/text()');
Select @TPP_12 := ExtractValue (EVENT_VAL, '\/\/TPP_12/text()');
Select @TPP_13 := ExtractValue (EVENT_VAL, '\/\/TPP_13/text()');
Select @TPP_14 := ExtractValue (EVENT_VAL, '\/\/TPP_14/text()');
Select @TPP_15 := ExtractValue (EVENT_VAL, '\/\/TPP_15/text()');
Select @AvgBR := ExtractValue (EVENT_VAL, '\/\/AvgBR/text()');
Select @MaxNullPrct := ExtractValue (EVENT_VAL, '\/\/MaxNullPrct/text()');
Select @MinNullPrct := ExtractValue (EVENT_VAL, '\/\/MinNullPrct/text()');
Select @SUBJ_EXPERIENCE := ExtractValue (EVENT_VAL, '\/\/SubjExperience/text()');
Select @OBJ_EXPERIENCE := ExtractValue (EVENT_VAL, '\/\/OBJ_EXPERIENCE/text()');
Select @STARTUP_DELAY := ExtractValue (EVENT_VAL, '\/\/STARTUP_DELAY/text()');
Select @VIDEO_BUFFERING_TIME := ExtractValue (EVENT_VAL, '\/\/VIDEO_BUFFERING_TIME/text()');

insert into tmptable select  "","10";



insert into tmptable select  "","20";



insert into tmptable select  '',@event_dttm;
Insert into collected_events (EVENT_VALUE,EVENT_DTTM,EVENT_KEY,EVENT_LAST_PROCESSED) Values (EVENT_VAL,@utc_time_for_event_key,@event_key,@utc_time_for_event_key);
insert into tmptable select  '','insert to collected events';


COMMIT;

insert into tmptable select "",o_error_status;

SHOW ERRORS;
SHOW WARNINGS;
END MY_BLOCK$$

这是我的 php 代码 -

$rs = $mysqli->query( "CALL Insert_Crunch(@o_error_status,'$myxmlData')");

Answer 1

使用PDO解决了这个问题。我的猜测是 Mysqli 根本不支持存储过程。