Error writing to database

[BigABigB]

Just tried installing this and it is correctly identifying my three temp sensors and OW Server, but I am having trouble with the writing to database. I get the following error:

InsertIntoDB() Reports Error: [System.Data.SqlClient.SqlException: Arithmetic overflow error converting expression to data type datetime.The statement has been terminated. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlExcep tion exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(Tds ParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(Strin g methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbA syncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at HSPI_ULTRA1WIRE2.Database.InsertIntoDB(String strDBTable, DateTime timeStamp, String deviceCode, Single deviceValue)], Failed on SQL: INSERT INTO tblTemperature ([ts], [code], [value]) VALUES (1355102744,'%3', 82.175).

I am trying to use Microsoft SQL Express 2012. I created the tblTemperature with the following (adapted from UJ's post here):

CREATE TABLE [dbo].[tblTemperature] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[ts] [datetime] NOT NULL ,
[code] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[value] [numeric](10, 0) NOT NULL
) ON [PRIMARY]
GO

My database connection settings are as follows:
Type: Microsoft SQL 2008
Provider: SQLClient provider
Connection string: Data Source=localhost\X;Initial Catalog=Y;Trusted_Connection=yes

Any thoughts?

Thanks,

B

[Ultrajones]

Hello. Change the datetime field to an integer.

Regards,
Ultrajones

[Ultrajones]

Here is the current database schema for SQLite:

sqlite> .schema
CREATE TABLE tblCounter(id INTEGER PRIMARY KEY,ts INTEGER,code varchar(3) NOT NULL,value INTEGER NOT NULL);
CREATE TABLE tblHumidity(id INTEGER PRIMARY KEY,ts INTEGER,code varchar(3) NOT NULL,value DOUBLE NOT NULL);
CREATE TABLE tblPressure(id INTEGER PRIMARY KEY,ts INTEGER,code varchar(3) NOT NULL,value DOUBLE NOT NULL);
CREATE TABLE tblSwitch(id INTEGER PRIMARY KEY,ts INTEGER,code varchar(3) NOT NULL,value INTEGER NOT NULL);
CREATE TABLE tblTemperature(id INTEGER PRIMARY KEY,ts INTEGER,code varchar(3) NOT NULL,value DOUBLE NOT NULL);

CREATE INDEX idxCODE1 ON tblTemperature (code);
CREATE INDEX idxCODE2 ON tblHumidity (code);
CREATE INDEX idxCODE3 ON tblPressure (code);
CREATE INDEX idxCODE4 ON tblCounter (code);
CREATE INDEX idxCODE5 ON tblSwitch (code);
CREATE INDEX idxTS1 ON tblTemperature (ts);
CREATE INDEX idxTS2 ON tblHumidity (ts);
CREATE INDEX idxTS3 ON tblPressure (ts);
CREATE INDEX idxTS4 ON tblCounter (ts);
CREATE INDEX idxTS5 ON tblSwitch (ts);

Regards,
Ultrajones

[BigABigB]

Thanks. It seems to work now and I don't get any errors but no records are showing up in SQL Express... Not sure why.

[BigABigB]

Also, thanks so much for your quick reply!

[BigABigB]

Never mind. It does work now, but charts don't. Is that because I'm using a non-standard database? I get the following errors:

12/9/2012 9:34:01 PM Ultra1Wire2 Error An unexpected error occured in the GetSensorChart() function/subroutine: [System.IndexOutOfRangeException: Cannot find table 0. at System.Data.DataTableCollection.get_Item(Int32 index) at HSPI_ULTRA1WIRE2.HSPI.GetSensorChart(Hashtable& PluginDevices, Hashtable& DeviceColors, String ChartType, ArrayList TempSensorList, DateTime dEndDateTime, String Interval)]
12/9/2012 9:34:01 PM Ultra1Wire2 Error An unexpected error occured in the QueryDatabase() function/subroutine: [System.Data.SqlClient.SqlException: Invalid column name 'ts_epoch'. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlExcep tion exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(Tds ParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.ConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlData Reader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(Command Behavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBeh avior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBeh avior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavi or behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(Command Behavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteR eader(CommandBehavior behavior) at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) at HSPI_ULTRA1WIRE2.Database.QueryDatabase(String strSQL)]

[BigABigB]

On a separate note. In case it's helpful for someone else. Here is the SQL query to create the tables in MS SQL Express 2012:

USE [DatabaseName]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[tblTemperature] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[ts] [int] NOT NULL ,
[code] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[value] [numeric](10, 0) NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblCounter] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[ts] [int] NOT NULL ,
[code] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[value] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblHumidity] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[ts] [int] NOT NULL ,
[code] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[value] [numeric](10, 0) NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblPressure] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[ts] [int] NOT NULL ,
[code] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[value] [numeric](10, 0) NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblSwitch] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[ts] [int] NOT NULL ,
[code] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[value] [int] NOT NULL
) ON [PRIMARY]
GO


SET ANSI_PADDING OFF
GO

[phil1155]

Error writing to mysql database

Hi,
I installed the module and it worked nicely. Willing to move to mysql, i selected it in the options page, created the mysql db to match the fields of the db3 one.

My log shows that the Db is ok, the plugin is well connected to it and get errors when it tries to insert a new row.
The error is here.
I used mysql 5.5. I cannot identify where would this syntax pb be. Is insertintoDB a command that the 5.5 version recognizes?
Thank to give me your thoughts.
Phil


I moved to a mysql database
InsertIntoDB() Reports Error: [MySql.Data.MySqlClient.MySqlException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[ts], [code], [value]) VALUES (1358185765,'\1', 20.5)' at line 1 at MySql.Data.MySqlClient.MySqlStream.ReadPacket() at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int32& insertedId) at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int32& insertedId) at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force) at MySql.Data.MySqlClient.MySqlDataReader.NextResult() at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBeh avior behavior) at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery() at HSPI_ULTRA1WIRE2.Database.InsertIntoDB(String strDBTable, DateTime timeStamp, String deviceCode, Single deviceValue)], Failed on SQL: INSERT INTO tblTemperature ([ts], [code], [value]) VALUES (1358185765,'\1', 20.5).