Jump to content

bipsen

Members
  • Posts

    4
  • Joined

  • Last visited

bipsen's Achievements

Newbie

Newbie (1/5)

0

Reputation

  1. 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....
  2. 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]
  3. 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....
  4. 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
×
×
  • 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.