Jump to content

odbc_prepare/odbc_execute problems


bipsen

Recommended Posts

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

 

Link to comment
Share on other sites

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....

Edited by bipsen
Link to comment
Share on other sites

You should not be quoting the ?'s in the query. Doing so causes them to be treated as a literal '?' not a placeholder for your parameters. You also should not be adding quotes to your values in the array, they are not necessary and would end up getting included in your column's value.

 

What are the data-types of your columns? In particular, are any of the fields that show as empty (ie, 5, 6, 7...) an INT value? If so, then those fields in your array need to either be NULL (and the column needs to accept NULL's) or they need to be an integer such as 0. An empty string will not get implicitly converted to a 0.

Link to comment
Share on other sites

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]
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.