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)error.c:566:SQLGetDiagRec: "[FreeTDS][sql Server]Invalid data type"error.c:517:SQLGetDiagRec(3, 0xd5e180, 2, 0x7fffffffa4e0, 0x7fffffffa0bc, 0x7fffffffa2e0, 512, 0x7fffffffa0b8)odbc.c:1434:SQLBindParameter(0xd5e180, 2, 1, 1, 0, 465654014, 0, 0xd04550, 0, 0xcf8ee0)odbc.c:1335:_SQLBindParameter(0xd5e180, 2, 1, 1, 0, 465654014, 0, 0xd04550, 0, 0xcf8ee0)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][sql Server]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)error.c:566:SQLGetDiagRec: "[FreeTDS][sql Server]Invalid data type"error.c:517:SQLGetDiagRec(3, 0xd5e180, 2, 0x7fffffffa4e0, 0x7fffffffa0bc, 0x7fffffffa2e0, 512, 0x7fffffffa0b8)odbc.c:1434:SQLBindParameter(0xd5e180, 3, 1, 1, 0, 465654014, 0, 0xd04680, 0, 0xcf8ef0)odbc.c:1335:_SQLBindParameter(0xd5e180, 3, 1, 1, 0, 465654014, 0, 0xd04680, 0, 0xcf8ef0)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][sql Server]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)error.c:566:SQLGetDiagRec: "[FreeTDS][sql Server]Invalid data type"error.c:517:SQLGetDiagRec(3, 0xd5e180, 2, 0x7fffffffa4e0, 0x7fffffffa0bc, 0x7fffffffa2e0, 512, 0x7fffffffa0b8)odbc.c:1434:SQLBindParameter(0xd5e180, 4, 1, 1, 0, 465654014, 0, 0xd048b0, 0, 0xcf8f00)odbc.c:1335:_SQLBindParameter(0xd5e180, 4, 1, 1, 0, 465654014, 0, 0xd048b0, 0, 0xcf8f00)error.c:412:odbc_errs_add: "Invalid data type" I am wondering a bit about the "Invalid data type" - but I am not sure whether this is the exact problem.... Can anyone help ? /Brian