|
|
|
I have a sensor network where the various sensors can either return a string or numeric measurement, I want to store these values along with a timestamp. I see two possible ways to handle this situation:
1. I have a general sensor data table with value column being an nvarchar of reasonable length. I can convert the numerics to strings and store them in the table as text. Pros: simplier design, Cons: wasted memory, loss of mathematical operations (min, max, etc) on the dataset.
2. Have two tables: one for text measurements, one for numeric measurements. Pros: retain ability for math on records, no wasted memory, Cons: extra step to determine table to log/retrieve from, more difficult to do aggregate queries.
Is there a third option? Any opinions on how to design this?
Thanks
modified 12-Jul-20 21:01pm.
|
|
|
|
|
How many measurements do you expect per minute?
Harddisks and memory are cheap enough to stop worrying about wasting a byte or two. The second option sounds right to me, perhaps from it's own thread.
I are troll 
|
|
|
|
|
Visual C++ and Microsoft Excel 97 ~ 2003
I am working on a project at the moment and have an issue with reading MS Excel 97 ~ 2003 spreadsheets.
My problem relates to the Excel field format of any given field in the spreadsheet, if a field is formatted in Excel to anything other than Text, then I cannot read the data from that field. for example if column A1 is formatted in excel to Text (good data returned) if A2 is formated to a number (a null is returned) if A3 is text (good data is returned), and so on.
The problem seems to be getting my code to understand the format of any particular field, Here is a snipped of my code;
1) I am using the CRecordset class to open and read the Excel spreadsheet. The spreadsheet has
defined name ranges and generally all works just fine (Provided the fields are text formatted).
2)If i use this line of code to first attempt to read what type of data is in a NUMBER field
"recset.GetODBCFieldInfo( m_csaColumnNames.GetAt(i), fieldinfo );"
I get a positive response when i try the different data types
"if(fieldinfo.m_nSQLType == SQL_VARCHAR){AfxMessageBox("SQL_VARCHAR");}"
suggesting its a data type SQL_VARCHAR data type
3) If i then call GetFieldValue to get the info from a field with a switch command
CDBVariant variant;
recset.GetFieldValue( m_csaColumnNames.GetAt(i), variant);
switch(variant.m_dwType)
{
case DBVT_SHORT:{ szValue.Format("%d", variant.m_iVal);
break;
}
case DBVT_LONG:{ szValue.Format("%d", variant.m_lVal);
break;
}
case DBVT_SINGLE:{ if ( variant.m_fltVal == 0.0 )
szValue = "Verify";
else
szValue.Format("%.1f", variant.m_fltVal);
break;
}
case DBVT_DOUBLE:{ if ( variant.m_dblVal == 0.0 )
szValue = "Verify";
else
szValue.Format("%.1f", variant.m_dblVal);
break;
}
case DBVT_DATE:{
szValue.Format("%B %d, %Y",variant.m_pdate );
break;
}
case DBVT_STRING:{ szValue = *variant.m_pstring; //szValue = V_BSTRT( &varValue );//convert BSTR to CString
break;
}
case DBVT_BOOL:{ if(variant.m_boolVal)
szValue = "TRUE";
else
szValue = "FALSE";
break;
}
case DBVT_UCHAR:{ szValue = (char*)variant.m_chVal;
break;
}
case DBVT_NULL:{
szValue = "Error Null"; AfxMessageBox(szValue);
break;
}
default:{
szValue = "\0";
break;
}
}//switch
RESULTS
All fields which are text, return a valid value which is exactly as per the Excel Spreadsheet, all fields which contains numbers or I even tried date formats in excel return DBVT_NULL which is totally useless because the cell has a number in it or a date.
SUMMARY
I have tried to read the data using GetODBCFieldInfo only and then calling the m_ member depending on the type returned which i know to be in the field, but that does not give me anything other than an unsigned short with random values that bear no resemblance to what is in the actual field.
Any help that anyone can offer in regards to solving this problem would be much appreciated and I I can get the entire class working properly I have happy to post it here for all to use.
Thanks in advance for any assistance.
|
|
|
|
|
Hello,
I have a little Stored Procedure (SQL Server 2005), which returns some tables. Now, I'm getting those tables in the DataSet in my code. The problem is that I'm not getting TableName of none of the tables. All I get is a default naming convention "Table", Table1", Table2" ....
What is the way to get the TableName from SP? How can I basically know which table represents which select in the SP?
Thanks,
Maxim
|
|
|
|
|
i think you can view code of the dataset and search for tag name <tables> and you find inside it all tables data from database
|
|
|
|
|
CREATE PROCEDURE MyProc AS
BEGIN
Select 'FirstTableName' AS TableName, Column1, Column2
FROM FirstTable
Select 'SecondTableName' AS TableName, Column1, Column2
FROM SecondTable
Select 'ThirdTableName' AS TableName, *
FROM ThirdTable
END
In the returning DataSet first column for all DataTables will have the TableName you have specified in the SP. You can identify the specific table using these names and assign an appropriate name to your DataTables.
|
|
|
|
|
Good idea, but unfortunatelly doesn't work. It seems like the name of the table (select) never comes from Stored Procedure to the DataSet.Table. This is very sad, because any time the stored procedure is changed, the code has to be recompiled. So what I did, is an additional SELECT with the names of the tables in the order it goes in the SP. This way I know how many tables I get and the names and the order
|
|
|
|
|
Hi,
I have two tables, I need records of first table that is not in the result of innerJion of these two tables.
How could I obtain that?
Best wishes
|
|
|
|
|
are you try left join?
or something like that
select * from table1 where id not in (select table1.id from table1 inner join table2 on table1.id=table2.id)
|
|
|
|
|
Unfortunately I dont have a unique field in table1 niether table2.(as id)
I know it's terrible, but it's not my fault.
Best wishes
|
|
|
|
|
mehrdadc48 wrote: Unfortunately I dont have a unique field in table1 niether table2.(as id)
than how you applied Inner Join between these two tables?
|
|
|
|
|
without unique field that may identify the values, the inner join will produce cross product... 
|
|
|
|
|
Actually, these two tables have same fields.
One is filled with old data, another has new data.
I made join on all of the fields, because they are same.
Best wishes
|
|
|
|
|
Hi All,,,
I want To Get the Constraint Of a veiw in sql server 2005
Please give me this query??
thanks
|
|
|
|
|
Please Please Before you Post you must First Understand what you want
Try again and tell us exactly what you want
Vuyiswa Maseko,
Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswa@its.co.za
www.ITS.co.za
|
|
|
|
|
Hi All,,
I Want To Get All Foreign Key From A Table And the Referenced Table Of This Foreign Key,,
How I Can DO That???
Thanks All;;;;
|
|
|
|
|
Look up INFORMATION_SCHEMA.TABLE_CONSTRAINTS in BOL.
Someone's gotta be the last to know, but why is it always me?
|
|
|
|
|
SELECT K_Table = FK.TABLE_NAME,FK_Column = CU.COLUMN_NAME,PK_Table = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,Constraint_Name = C.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
SELECT i1.TABLE_NAME, i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT ON PT.TABLE_NAME = PK.TABLE_NAME
ORDER BY
1,2,3,4
|
|
|
|
|
Hi i am getting following error in error log files of iis.can anybody tell me about this.
The client was unable to reuse a session with SPID 628, which had been reset for connection pooling. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.
|
|
|
|
|
Hi,
Is it possible to have the following in sql?
IF @VarParam > 0 Exec(SELECT * FROM #TEMP1)
ELSE
Exec
(SELECT * FROM #TEMP1
UNION
SELECT * FROM #TEMP2)
Thank you
----------------------------------------------------------
Lorem ipsum dolor sit amet.
|
|
|
|
|
Hmmm... dunno, try:
EXEC (SELECT * FROM #TEMP1
UNION
SELECT * FROM #TEMP2 WHERE @VarParam > 0)
|
|
|
|
|
how to set sqlserver 2000 for accept remote connections ?
H.R
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
