|
Richard Deeming wrote: It's a bad idea.
I , the last project I was asked to advise on was managed by a PM who was adamant that this was the "modern" way to go (I was accused of being an old fart who was stuck in the 90's). Started off as a 2 column table and the last I saw of it was a 5 column, 2 table structure.
Luckily I walked away from anything to do with the project.
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
I take this point, I restructured my schema to just have 1 table per type for the time being, I was just trying to have less tables and then ran into this anti-pattern. Thanks for the input.
|
|
|
|
|
I need help resetting the values of each users in a row at once with random numbers by either button click or loading a page.
Just like a reset page or button to assign random numbers to each users without repeating at once.
|
|
|
|
|
You most likely need to use a set of UPDATE statements in order to use different random values.
|
|
|
|
|
I need a real-time database for inserting at least 500fields per second. (I have been selected TimeScaleDB (Postgresql Extension) for my purpose, currently). So, the matrix of the table will be 500(Fields)x86400(Rows).
This means that for a 500 double-precision field it will be around 500*8bytes=4000bytes which for 86400rows it will be 345,600,000bytes (345.6M).
I know a software which it is recording the same structure of data and the final daily volume of its database is only 84M~85M. Its Extension is *.tag so, i think it is a DataFlex database. It is not a free database and i also never used it.
Question:
1- Which free database is the best to use for real-time data storing and querying (at the moment of storing) based on your experience or knowledge?
2- Is there any database to have such a volume or even less for this size of data?
Thank you for your Help.
|
|
|
|
|
|
But Its Volume Will Be The Same As PostgreSQL!
|
|
|
|
|
Why are you concerned about sizes? Disk space is cheap.
|
|
|
|
|
For one year the size will be 120GB for Postgresql but for the mentioned DBS will be only 30GB. it is a great difference (one fourth).
|
|
|
|
|
That would mean the other database is storing the data compressed.
That's obviously something you can do in many databases, but it might add performance issues.
Wrong is evil and must be defeated. - Jeff Ello
Never stop dreaming - Freddie Kruger
|
|
|
|
|
|
What is wrong with MySQL[^]
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
|
There are various ways to "compress" (and structure) redundant database data. "Size" doesn't tell much of a story.
And you don't generally "query" real-time data; plot it, maybe; record it, certainly; usually to a binary file that later gets translated and loaded (to a database) for information purposes.
It was only in wine that he laid down no limit for himself, but he did not allow himself to be confused by it.
― Confucian Analects: Rules of Confucius about his food
|
|
|
|
|
Member 13325846 wrote: 1- Which free database is the best to use for real-time data storing and querying (at the moment of storing) based on your experience or knowledge?
SQLite, in memory database storage would be my choice. Stores db in memory, instead of disk.
SQL Server has the same option.
..but both not real time, just really, really fast. Anything on Windows is not guaranteed real time, by technolgy. If you need real time, reseach QNX or the likes.
Bastard Programmer from Hell
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
Hi,
when writing in Access database using ADO and record binding, I run into an exception after some time.
I wrote a small console program to demonstrate the problem :
#include <Windows.h>
#include <iostream>
#import "c:\Program Files\Common Files\System\ADO\msado15.dll" no_namespace rename("EOF", "EndOfFile")
#include "icrsint.h"
_COM_SMARTPTR_TYPEDEF(IADORecordBinding, __uuidof(IADORecordBinding));
void OpenDatabase();
void WriteToDatabase(int i);
void CloseDatabase();
_ConnectionPtr m_pConnectionPtr = 0;
DWORD64 cnt_recordset;
int main()
{
HRESULT hr = CoInitializeEx(NULL, COINIT_MULTITHREADED);
m_pConnectionPtr = NULL;
m_pConnectionPtr.CreateInstance(__uuidof(Connection));
int cnt = 0;
cnt_recordset = 0;
OpenDatabase();
while (1)
{
std::cout << cnt++ << " : " << cnt_recordset << std::endl;
for (int i = 0; i < 1000; i++)
WriteToDatabase(i);
}
CloseDatabase();
CoUninitialize();
}
void OpenDatabase()
{
bstr_t strCnn("Provider=MSDASQL;DSN=TestDB;User ID=sa;");
m_pConnectionPtr->Open(strCnn, "", "", NULL);
}
void CloseDatabase()
{
if ((m_pConnectionPtr->State == adStateOpen))
m_pConnectionPtr->Close();
}
void WriteToDatabase(int i)
{
cnt_recordset++;
class CMyRecordSet : public CADORecordBinding
{
BEGIN_ADO_BINDING(CMyRecordSet)
ADO_VARIABLE_LENGTH_ENTRY2(1, adInteger, m_nID, sizeof(m_nID), m_IDStatus, FALSE)
ADO_FIXED_LENGTH_ENTRY(2, adInteger, m_value, m_valueStatus, TRUE)
END_ADO_BINDING()
public:
int m_nID;
int m_value;
ULONG m_IDStatus;
ULONG m_valueStatus;
};
HRESULT hr = true;
_RecordsetPtr pRs("ADODB.Recordset");
CMyRecordSet rs;
IADORecordBindingPtr picRs(pRs);
hr = pRs->Open("TTagData",
_variant_t((IDispatch*)m_pConnectionPtr, true),
adOpenKeyset, adLockOptimistic, adCmdTable);
hr = picRs->BindToRecordset(&rs);
rs.m_value = i;
picRs->AddNew(&rs);
pRs->Close();
}
The exception occurs on closing the recordset after the AddNew :
inline HRESULT Recordset15::Close ( ) {
HRESULT _hr = raw_Close();
if (FAILED(_hr)) _com_issue_errorex(_hr, this, __uuidof(this));
return _hr;
}
Exception number is 0x800a0c93
This happens after a couple of hundreds of thousends writes.
Am I missing something?
Any help is appreciated very much!
|
|
|
|
|
From ErrorValueEnum - SQL Server | Microsoft Docs[^]
adErrIllegalOperation 3219 -2146825069 0x800A0C93 Operation is not allowed in this context.
Which, like so many error codes, does not tell you much. You could try one of the Microsoft forums.
|
|
|
|
|
Quote: This happens after a couple of hundreds of thousends writes. Access is probably not the best database of choice for that many records to be honest.
How big (size on disk) is your .accdb file? There is an absolute maximum size for an Access database of 2GB - you may need to consider linked tables to bring the size down
|
|
|
|
|
Database is only 126MB big at that stage so this could not be the cause.
I have rewritten the code without datarecord binding :
void WriteToDatabase2(int i)
{
_RecordsetPtr pRs("ADODB.Recordset");
HRESULT hr;
char sql[128];
sprintf(sql, "insert into TTagData([Value]) values (%d);", i);
hr=pRs->Open(_variant_t(sql),
_variant_t((IDispatch*)m_pConnectionPtr, true),
adOpenStatic, adLockReadOnly, adCmdUnknown);
}
Have written a couple of million of records and now I don't get the exception!
Is there something wrong with the datarecord binding or the way I use it??
|
|
|
|
|
I have rewritten the code without datarecord binding :
void WriteToDatabase2(int i)
{
_RecordsetPtr pRs("ADODB.Recordset");
HRESULT hr;
char sql[128];
sprintf(sql, "insert into TTagData([Value]) values (%d);", i);
hr=pRs->Open(_variant_t(sql),
_variant_t((IDispatch*)m_pConnectionPtr, true),
adOpenStatic, adLockReadOnly, adCmdUnknown);
} Have now written a couple of million of records and now I don't get the exception!
Is there something wrong with the datarecord binding or the way I use it??
|
|
|
|
|
According to Open Method (ADO Recordset) - SQL Server | Microsoft Docs:
It is not a good idea to use the Source argument of the Open method to perform an action query that does not return records because there is no easy way to determine whether the call succeeded. The Recordset returned by such a query will be closed. To perform a query that does not return records, such as a SQL INSERT statement, call the Execute method of a Command object or the Execute method of a Connection object instead.
|
|
|
|
|
OK, so I changed my code to something like this :
_CommandPtr pCmd;
pCmd.CreateInstance(__uuidof(Command));
pCmd->ActiveConnection = pConn;
pCmd->CommandText = "Insert into [table] ([value]) values (1)";
pCmd->Execute();
Now in the table is an autoincrement ID which I need for use in a linked table.
Does the pCmd->Execute() return a recordset and if so what's in it?
Or is there another way to get the data of the just added record?
I find very few information on this topic.
|
|
|
|
|
I usually divided the INSERT into DB and then ReQuery for the opened recordset.
However, did you check the returned recordset from
pCmd->Execute();
|
|
|
|
|
I refer to the use case as per Swagger documentation
Swagger UI[^]
I need help to know the following:
1) whether the table pets, table users and table orders below is correct
Create table pets (
petId INTEGER IDENTITY PRIMARY KEY,
NAME VARCHAR(30) NOT NULL,
PHOTOURL VARCHAR(30) NOT NULL,
User userId,
TAGS VARACHR(20) NOT NULL,
STATUS ENUM(20)
PRIMARY KEY (petId),
FOREIGN KEY (userId) REFERENCES Users(userId)
);
CREATE TABLE Orders (
OrderID int PRIIMARY KEY,
OrderNumber int NOT NULL,
petId int,
quantity int NOT NULL,
shipDate DATE NOT NULL,
STATUS VARCHAR(30) NOT NULL,
PRIMARY KEY (OrderID),
FOREIGN KEY (petId) REFERENCES Pets(petId)
Not sure to include User based on the Swagger document
);
CREATE TABLE Users(
UserID int PRIMARY KEY,
UserName VARCHAR(30) NOT NULL,
FirstName VARCHAR(30) NOT NULL,
LastName VARCHAR(30) NOT NULL,
EMAIL VARCHAR(30) NOT NULL,
PASSWORD VARCHAR(30) NOT NULL,
PHONE VARCHAR(30) NOT NULL,
UserStatus VARCHAR(30) NOT NULL,
2) whether store entity is needed ?
Please help me on above as I am not good with DB and have problem in understanding swagger documentation.
3) If I were to use JPA data in Spring, do I use Custom query for querying the status ?
Tks
|
|
|
|
|
Hi guys,
I need to build a windows app for documents archiving based on SQL server. I'm not sure if it better to store the files inside the database or as regular files. There will be thousands of files, and each file can reach upto 10MB so the total size could be around couple of TB's.
|
|
|
|
|