bipsen

June 28, 2013

I just tried testing some of the data using PDO - and here it seems to work ... It would just be nice to know why it doesn't work with the "native" odbc calls....

June 28, 2013

Using code like this:

        $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);

And having the input array like this:

Array
(
    [0] => 2013-06-28 12:00:28
    [1] => 407
    [2] => I
    [3] => ANR0407I Session 2741 started for administrator XXXXX (Linux86) (Tcp/Ip 127.0.0.1(43088)). (SESSION: 2741)
    [4] => SERVER
    [5] =>
    [6] =>
    [7] =>
    [8] =>
    [9] =>
    [10] =>
    [11] => 2741
    [12] =>
    [13] => 2013-06-28
    [14] => RS002A
    [15] => 2013-06-28 19:16:52
)

Gives me a result with:

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

and checking the freetds debug log gives me:

odbc.c:4445:SQLPrepare(0xd599f0, 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) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?), -3)
odbc.c:4490:Creating prepared statement
token.c:540:tds_process_tokens(0xd35c40, 0x7fffffffb99c, 0x7fffffffb998, 0x104)
token.c:543:tds_process_tokens() state is COMPLETED
odbc.c:1001:SQLNumParams(0xd599f0, 0xcfd1da)
odbc.c:4420:SQLNumResultCols(0xd599f0, 0xcfd1d8)
error.c:517:SQLGetDiagRec(3, 0xd599f0, 1, 0x7fffffffa4e0, 0x7fffffffa0bc, 0x7fffffffa2e0, 512, 0x7fffffffa0b8)
odbc.c:1434:SQLBindParameter(0xd599f0, 1, 1, 1, 0, 465654014, 0, 0xcfd4a8, 0, 0xcf4e80)
odbc.c:1335:_SQLBindParameter(0xd599f0, 1, 1, 1, 0, 465654014, 0, 0xcfd4a8, 0, 0xcf4e80)
error.c:412:odbc_errs_add: "Invalid data type"
error.c:517:SQLGetDiagRec(3, 0xd599f0, 1, 0x7fffffffa8b0, 0x7fffffffa48c, 0x7fffffffa6b0, 512, 0x7fffffffa488)
error.c:566:SQLGetDiagRec: "[FreeTDS][SQL Server]Invalid data type"
error.c:517:SQLGetDiagRec(3, 0xd599f0, 2, 0x7fffffffa8b0, 0x7fffffffa48c, 0x7fffffffa6b0, 512, 0x7fffffffa488)
error.c:517:SQLGetDiagRec(3, 0xd599f0, 1, 0x7fffffffa4e0, 0x7fffffffa0bc, 0x7fffffffa2e0, 512, 0x7fffffffa0b8)
error.c:566:SQLGetDiagRec: "[FreeTDS][SQL Server]Invalid data type"
error.c:517:SQLGetDiagRec(3, 0xd599f0, 2, 0x7fffffffa4e0, 0x7fffffffa0bc, 0x7fffffffa2e0, 512, 0x7fffffffa0b8)
odbc.c:1434:SQLBindParameter(0xd599f0, 2, 1, 1, 0, 465654014, 0, 0xd005c0, 0, 0xcf4e90)
odbc.c:1335:_SQLBindParameter(0xd599f0, 2, 1, 1, 0, 465654014, 0, 0xd005c0, 0, 0xcf4e90)
error.c:412:odbc_errs_add: "Invalid data type"
error.c:517:SQLGetDiagRec(3, 0xd599f0, 1, 0x7fffffffa8b0, 0x7fffffffa48c, 0x7fffffffa6b0, 512, 0x7fffffffa488)
error.c:566:SQLGetDiagRec: "[FreeTDS][SQL Server]Invalid data type"

The table structure:

CREATE TABLE [dbo].[t_DSA_Actlog](
	[DATE_TIME] [datetime] NULL,
	[MSGNO] [int] NULL,
	[SEVERITY] [varchar](2) NULL,
	[MESSAGE] [varchar](2001) NULL,
	[ORIGINATOR] [varchar](20) NULL,
	[NODENAME] [varchar](64) NULL,
	[OWNERNAME] [varchar](64) NULL,
	[SCHEDNAME] [varchar](30) NULL,
	[DOMAINNAME] [varchar](30) NULL,
	[SESSID] [int] NULL,
	[SERVERNAME] [varchar](64) NULL,
	[SESSION] [int] NULL,
	[PROCESS] [int] NULL,
	[Update_Date] [char](10) NOT NULL,
	[TSM_Instance] [varchar](20) NOT NULL,
	[BatchTimeStamp] [datetime] NOT NULL
) ON [PRIMARY]

June 28, 2013

Changed code to:

        $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 ('?','?','?','?','?','?','?','?','?','?','?','?','?','?','?','?')";

And now I get:

odbc_execute(): SQL error: [unixODBC][FreeTDS]

Conversion failed when converting date and/or time from character string., SQL state 22008 in SQLExecute

My array contains:

Array
(
    [0] =>
    [1] => 407
    [2] => I
    [3] => ANR0407I Session 2741 started for administrator XXXXXXX (Linux86) (Tcp/Ip 127.0.0.1(43088)). (SESSION: 2741)
    [4] => SERVER
    [5] =>
    [6] =>
    [7] =>
    [8] =>
    [9] =>
    [10] =>
    [11] => 2741
    [12] =>
    [13] => 2013-06-28
    [14] => RS002A
    [15] => 2013-06-28 15:43:11
)

And I know, that the date/time format i element 15 is the exact same format as the one being used in the SQL server....

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)