odbc_prepare/odbc_execute problems

bipsen · June 28, 2013

Hi

I am trying to import a CSV file into a MSSQL server from a linux box using php via the php ODBC driver (unixODBC/freetds) - but have a wierd problem....

The code looks like:

if (($handle = fopen($CsvOutFile, "r")) !== FALSE)

{

$stmtsql="INSERT INTO TsmStageDb.dbo.t_DSA_Actlog (DATE_TIME,MSGNO,SEVERITY,MESSAGE,ORIGINATOR,NODENAME,OWNERNAME,SCHEDNAME,DOMAINNAME,SESSID,SERVERNAME,SESSION,PROCESS,Update_Date,TSM_Instance,BatchTimeStamp) ";

$stmtsql=$stmtsql."VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

$stmt = odbc_prepare($conn, $stmtsql);

while (($data = fgetcsv($handle, 2000, "\t")) !== FALSE)

{

$cDATE_TIME="\"" . substr($data[0],0,19) . "\"";

$cMSGNO=$data[1];

$cSEVERITY="\"" . $data[2] . "\"";

$szTmp = str_replace("'","'",$data[3]);

$cMESSAGE="\"" . $szTmp . "\"";

$cORIGINATOR="\"" . $data[4] . "\"";

$cNODENAME="\"" . $data[5] . "\"";

$cOWNERNAME="\"" . $data[6] . "\"";

$cSCHEDNAME="\"" . $data[7] . "\"";

$cDOMAINNAME="\"" . $data[8] . "\"";

$cSESSID=$data[9];

$cSERVERNAME="\"" . $data[10] . "\"";

$cSESSION=$data[11];

$cPROCESS=$data[12];

$cUpdate_Date="\"" . $sqlInsertDate . "\"";

$cTSM_Instance="\"" . strtoupper($TsmServerName) . "\"";

$SQLHEADER="INSERT INTO [TsmStageDb].[dbo].[t_DSA_Actlog] ([DATE_TIME],[MSGNO],[sEVERITY],[MESSAGE],[ORIGINATOR],[NODENAME],[OWNERNAME],[sCHEDNAME],[DOMAINNAME],[sESSID],[sERVERNAME],[sESSION],[PROCESS],[update_Date],[TSM_Instance],[batchTimeStamp]) ";

$SQLVALUES="VALUES ($cDATE_TIME,$cMSGNO,$cSEVERITY,$cMESSAGE,$cORIGINATOR,$cNODENAME,$cOWNERNAME,$cSCHEDNAME,$cDOMAINNAME,$cSESSID,$cSERVERNAME,$cSESSION,$cPROCESS,$cUpdate_Date,$cTSM_Instance,$SqlBatchTimeStamp)";

$SQLQUERY=$SQLHEADER . $SQLVALUES;

$a=array($cDATE_TIME,$cMSGNO,$cSEVERITY,$cMESSAGE,$cORIGINATOR,$cNODENAME,$cOWNERNAME,$cSCHEDNAME,$cDOMAINNAME,$cSESSID,$cSERVERNAME,$cSESSION,$cPROCESS,$cUpdate_Date,$cTSM_Instance,$SqlBatchTimeStamp);

print_r($a);

if(odbc_execute($stmt,$a)==false)

{

exit(10);

$NumErrs++;

}

else

{

$RowCount++;

}

fclose($handle);

}

The output from print_r gives me:

Array

(

[0] => "2013-06-28 13:56:23"

[1] => 2017

[2] => "I"

[3] => "ANR2017I Administrator XXXXXX issued command: select COUNT(DATE_TIME) FROM ACTLOG where (date_time> '2013-06-28 12:00:27' ) (SESSION: 3120)"

[4] => "SERVER"

[5] => ""

[6] => ""

[7] => ""

[8] => ""

[9] =>

[10] => ""

[11] => 3120

[12] =>

[13] => "2013-06-28"

[14] => "RS002A"

[15] => "2013-06-28 13:56:23"

)

And I get the error:

PHP Warning: odbc_execute(): SQL error: Failed to fetch error message, SQL state HY000 in SQLExecute in /home/bri/CsvToSql.php on line 329

Checking the freetds log, I see this:

dbc.c:5831:SQLGetInfo(0xd38f60, 8, 0x7fffffffbb38, 4, (nil))

odbc.c:5085:_SQLGetInfo(0xd38f60, 8, 0x7fffffffbb38, 4, (nil))

odbc.c:4445:SQLPrepare(0xd5e180, INSERT INTO TsmStageDb.dbo.t_DSA_Actlog (DATE_TIME,MSGNO,SEVERITY,MESSAGE,ORIGINATOR,NODENAME,OWNERNAME,SCHEDNAME,DOMAINNAME,SESSID,SERVERNAME,SESSION,PROCESS,Update_Date,TSM_Instance,BatchTimeStamp) VALU

ES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?), -3)

odbc.c:4490:Creating prepared statement

token.c:540:tds_process_tokens(0xd3a3d0, 0x7fffffffb99c, 0x7fffffffb998, 0x104)

token.c:543:tds_process_tokens() state is COMPLETED

odbc.c:1001:SQLNumParams(0xd5e180, 0xd0116a)

odbc.c:4420:SQLNumResultCols(0xd5e180, 0xd01168)

error.c:517:SQLGetDiagRec(3, 0xd5e180, 1, 0x7fffffffa4e0, 0x7fffffffa0bc, 0x7fffffffa2e0, 512, 0x7fffffffa0b8)

odbc.c:1434:SQLBindParameter(0xd5e180, 1, 1, 1, 0, 465654014, 0, 0xd014e0, 0, 0xcf8ed0)

odbc.c:1335:_SQLBindParameter(0xd5e180, 1, 1, 1, 0, 465654014, 0, 0xd014e0, 0, 0xcf8ed0)

error.c:412:odbc_errs_add: "Invalid data type"

error.c:517:SQLGetDiagRec(3, 0xd5e180, 1, 0x7fffffffa8b0, 0x7fffffffa48c, 0x7fffffffa6b0, 512, 0x7fffffffa488)

error.c:566:SQLGetDiagRec: "[FreeTDS]

Invalid data type"

error.c:517:SQLGetDiagRec(3, 0xd5e180, 2, 0x7fffffffa8b0, 0x7fffffffa48c, 0x7fffffffa6b0, 512, 0x7fffffffa488)

error.c:517:SQLGetDiagRec(3, 0xd5e180, 1, 0x7fffffffa4e0, 0x7fffffffa0bc, 0x7fffffffa2e0, 512, 0x7fffffffa0b8)