CodeProject Latest postings for Database

HOW TO ANSWER A QUESTION

Chris Maunder — Thu, 16 Jul 2009 13:10:00 GMT

Apologies for the shouting but this is important.

When answering a question please:

Read the question carefully
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar
If a question is poorly phrased then either ask for clarification, ignore it, or mark it down. Insults are not welcome
If the question is inappropriate then click the 'vote to remove message' button

Insults, slap-downs and sarcasm aren't welcome. Let's work to help developers, not make them feel stupid.

cheers,
Chris Maunder

The Code Project Co-founder
Microsoft C++ MVP

How to get an answer to your question

Chris Maunder — Fri, 11 Nov 2005 03:30:00 GMT

For those new to message boards please try to follow a few simple rules when posting your question.

Choose the correct forum for your message. Posting a VB.NET question in the C++ forum will end in tears.
Be specific! Don't ask "can someone send me the code to create an application that does 'X'. Pinpoint exactly what it is you need help with.
Keep the subject line brief, but descriptive. eg "File Serialization problem"
Keep the question as brief as possible. If you have to include code, include the smallest snippet of code you can.
Be careful when including code that you haven't made a typo. Typing mistakes can become the focal point instead of the actual question you asked.
Do not remove or empty a message if others have replied. Keep the thread intact and available for others to search and read. If your problem was answered then edit your message and add "[Solved]" to the subject line of the original post, and cast an approval vote to the one or several answers that really helped you.
If you are posting source code with your question, place it inside <pre></pre> tags. We advise you also check the "Encode "<" (and other HTML) characters when pasting" checkbox before pasting anything inside the PRE block, and make sure "Use HTML in this post" check box is checked.
Be courteous and DON'T SHOUT. Everyone here helps because they enjoy helping others, not because it's their job.
Please do not post links to your question into an unrelated forum such as the lounge. It will be deleted. Likewise, do not post the same question in more than one forum.
Do not be abusive, offensive, inappropriate or harass anyone on the boards. Doing so will get you kicked off and banned. Play nice.
If you have a school or university assignment, assume that your teacher or lecturer is also reading these forums.
No advertising or soliciting.
We reserve the right to move your posts to a more appropriate forum or to delete anything deemed inappropriate or illegal.

cheers,
Chris Maunder

The Code Project Co-founder

Microsoft C++ MVP

Vb6 and sqlserver 2000

ahmed ali Apr2021 — Wed, 08 May 2024 03:31:00 GMT

Insufficient key column information for updating or refreshing problems is displayed

SQLite and Firefox Browser 68.0.2 (note: current FFB is now 72.0.2) and what I stand to lose if I update.

RedDk — Mon, 06 May 2024 18:56:00 GMT

I'm doing some development in SQLite and I have data, specifically databases a la Mozilla (the Firefox front office complex) but in order to clear my browser history, something that I think will happen automatically if I dare upgrade/update, my cache of "data" will be destroyed and all my bookmarks will be lost as well as my extensive history.

Whether I'm right about the loss happening when I do anything to the current browser or not, does anyone know if an update will fix the SQLite functionality used by the mozilla implementation, specifically whether the "unixepoch" date/time function which was introduced around 2022 will be at my disposal?

[EDIT]
"DB Browser for SQLite" tells my it's using SQLite v.3.27.2 and "unixepoch" is not there yet.
[END EDIT]

Looking for a Better Database Solution for Trading Systems

Rene Rose 2021 — Sat, 20 Apr 2024 15:53:00 GMT

I develop trading systems for stocks and other securities. To manage price data from various sources, I use a custom program written in VB.Net. This program maintains a LiteDB database for each underlying, resulting in several dozen database files.

I've found that LiteDB is very slow when inserting large amounts of data, and since I import a significant amount of data, I'm looking for a more efficient solution. Currently, I'm considering switching to a relational DB system like SQLite or MS SQL Server.

My data model includes a class for price data from CSV files, which contains the following fields:

"Identifier" (text)
"TimeStamp" (date/time)
"Open", "High", "Low", "Close" (single precision floating point numbers)
"Size" (integer)

The issue is that the identifier is identical for several hundred thousand data points, and the timestamps and price values often repeat. Since this data is distributed across various instruments, duplicate entries occur.

One possible solution would be to distribute this data class across multiple tables to avoid duplicate entries. However, I'm concerned that this may cause performance issues when inserting several hundred thousand data points.

Has anyone had similar experiences or advice to share?

Thanks in advance!

Run an external program C# from within the Oracle Developer

Member 16235420 — Tue, 02 Apr 2024 20:37:00 GMT

Run an external program C# from within the Oracle Developer and receive the return values from the external program

Display records based on selected date range in months within same year?

samflex — Sun, 21 Jan 2024 20:28:00 GMT

Greetings,

How do I ensure that the following code only queries records that are submitted between March and May 2024?

If a record has been submitted between the above date range, display 1. Otherwise, display 0.

Currently, we have a code that does this but for entire year.

In the code below, I left the code that performs this check with alias of thisYear.

I left this line of code but commented it out just to show what we have that works except this time, we just want this check to be between March and May 2024.

The code:

CASE WHEN d.dateCreated BETWEEN DATEFROMPARTS(2024, 3, 1) AND DATEFROMPARTS(2024, 5 + 1, 1) THEN 1 ELSE 0 END as thisYear

appears to work sometimes but does not work other times. No errors but wrong results.

That code above, temporarily replaces this line of code below as described above.

--CASE WHEN YEAR(d.dateCreated) = YEAR(getdate()) THEN 1 ELSE 0 END as thisYear

Here is the entire code:

 SELECT e.Name, e.email, e.emptitle, d.dateCreated,
     CASE WHEN YEAR(d.dateSubmitted) < YEAR(getdate()) THEN 1 ELSE 0 END as previousYear,
     --CASE WHEN YEAR(d.dateSubmitted) = YEAR(getdate()) THEN 1 ELSE 0 END as thisYear
 CASE WHEN d.dateSubmitted BETWEEN DATEFROMPARTS(2024, 3, 1) AND DATEFROMPARTS(2024, 5 + 1, 1) THEN 1 ELSE 0 END as thisYear
FROM Employees e
INNER JOIN dateDetails d on e.employeeID = d.employeeID
WHERE e.employeeID = someID

I have also tried this:

CASE WHEN d.dateSubmitted >= DATEFROMPARTS(2024, 3, 1) AND d.dateSubmitted <  DATEFROMPARTS(2024,5 + 1, 1)

Same inconsistent result.

I guess my question is why does this work perfectly:

CASE WHEN YEAR(d.dateSubmitted) = YEAR(getdate()) THEN 1 ELSE 0 END as thisYear

but the date range does not work well?

Thanks in advance for your help.

time-consuming insert operation

mike7411 — Fri, 19 Jan 2024 03:25:00 GMT

Let's say you have a table with an index on the field "username".

Is it true that eventually you'll have some really time-consuming inserts when the hash table has to resize itself?

Hash table resize is O(n), I think.

Thanks.

CREATE INDEX

mike7411 — Thu, 18 Jan 2024 04:22:00 GMT

I was looking at the documentation for CREATE INDEX in SQL:

https://www.w3schools.com/sql/sql_create_index.asp

SQL

CREATE INDEX index_name
ON table_name (column1, column2, ...);

I don't understand when you would create it for multiple columns. Let's say you did this:

SQL

CREATE INDEX idx_pname ON Persons (LastName, FirstName);

Does that create one index on two columns? Or, is that the same as creating two indexes?

For instance, does that statement create two hashtables? Or one hashtable?

Thank you.

SQL behind the scenes

mike7411 — Wed, 17 Jan 2024 04:02:00 GMT

Let's say you have a SQL database with a table called USERS.

Let's say it has 500,000 users in it.

Let's say you do this:

SQL

SELECT PASSWORD FROM USERS WHERE USER='bill.gates';

Let's say there's no indices on the table.

Does the database just do a linear search thru each user?

Thank you.

Sql Server Database goes into Recovery mode

Nilesh M. Prajapati — Sat, 18 Nov 2023 04:59:00 GMT

I have a window based application that creates new database every month programmatically and in that database i have single table which consists of 50 columns which also . But the problem is after crating database programmatically it falls in recovery mode. So my question is Why database falls in recovery mode.

SQLite Thread Safety

Richard Andrew x64 — Fri, 10 Nov 2023 23:23:00 GMT

The SQLite documentation says that SQLite is thread safe for reading but not for writing. Specifically, they say you can have multiple threads reading from the DB, but only one thread should write at one time.

Do you think this means that I should also avoid two threads accessing the DB at one time if one of the threads is reading and the other is writing?

IOW, does reading need to be serialized as well?

The difficult we do right away...
...the impossible takes slightly longer.

SQLite syntax error that I can't figure out - SOLVED

Richard Andrew x64 — Sun, 05 Nov 2023 00:09:00 GMT

I'm pulling my hair with this one:
(There's a unique index on the [DOMAIN] field.

INSERT INTO DNSLOG ([DATETIME], [DOMAIN], [BLOCKED]) VALUES (133436157350078746, 'optimizationguide-pa.googleapis.com.', 0) 
ON CONFLICT (DOMAIN) DO 
UPDATE DNSLOG 
SET HITCOUNT = HITCOUNT + 1 
WHERE DOMAIN = 'optimizationguide-pa.googleapis.com.'

The error is clearly in the ON CONFLICT clause because when I run only the INSERT clause, it does what it's supposed to do. And the UPDATE statement works when I run it by itself.

The actual error message is "SQL Logic error near "DNSLOG" syntax error".

Any idea what the syntax error could be?

****SOLUTION****

It's not supposed to have the table name in the "DO UPDATE" statement. IOW:

INSERT INTO DNSLOG ([DATETIME], [DOMAIN], [BLOCKED]) VALUES (133436157350078746, 'optimizationguide-pa.googleapis.com.', 0) 
ON CONFLICT (DOMAIN) DO 
UPDATE SET HITCOUNT = HITCOUNT + 1 
WHERE DOMAIN = 'optimizationguide-pa.googleapis.com.'

The difficult we do right away...
...the impossible takes slightly longer.

please

daw htikehtike — Sun, 22 Oct 2023 05:17:00 GMT

I would like to have a blank database already written. The list is for monthly entry and exit. Thank you all

Getting Last Record and First Record from a Record Set (SQL server DB)

Member 11843356 — Thu, 21 Sep 2023 18:07:00 GMT

I am pretty new using MS SQL. I perform a query over some SQL server table; it returns a record set; but I need to read the last record just to get the value of a certain column.

This is my procedure:

PHP

$connectionInfo = array( "Database"=>$myDB, "UID"=>$myUser , "PWD"=>$myPass);
$conn = sqlsrv_connect( $myServer, $connectionInfo);

$query = "SELECT * FROM( SELECT col0, col1, col2, col3 FROM t1 LEFT JOIN t2 ON t1.col1 = t2.col1 LEFT JOIN t3 ON t1.col2 = t3.col2) AS t ORDER BY t.col0; ";
$rec_set = sqlsrv_query($connection, $query, array(), array("Scrollable" => 'buffered'));

if( $rec_set === false ) die( print_r( sqlsrv_errors(), true));  

$connectionInfo = array( "Database"=>$myDB,  UID"=>$myUser , "PWD"=>$myPass);
$conn = sqlsrv_connect( $myServer, connectionInfo);

$query = "SELECT * FROM( SELECT col0, col1, col2, col3 FROM t1 LEFT JOIN t2 ON t1.col1 = t2.col1 LEFT JOIN t3 ON t1.col2 = t3.col2) AS t ORDER BY t.col0; ";

$rec_set = sqlsrv_query($connection, $query, array(),  array("Scrollable" => 'buffered'));
if( $rec_set === false ) die( print_r( sqlsrv_errors(), true));	
			 
$row_count = sqlsrv_num_rows($rec_set);
if ($row_count === false) die( print_r( sqlsrv_errors(), true));

$reg   = sqlsrv_fetch_array( $rec_set, SQLSRV_FETCH_ASSOC, 0);
$first = $reg['column_name'];

$reg   = sqlsrv_fetch_array( $rec_set, SQLSRV_FETCH_ASSOC, $row_count - 1);
$last  = $reg['column_name'];

It is clear that the sqlsrv_fetch_array DOES NOT work like that. Does anyone know how to achieve my goal, that is, getting last and first records from that record set without generating another SELECT?
Thank you in advance.

prior action plan to prevent sql database corruption

Kar_Malay — Sat, 02 Sep 2023 06:01:00 GMT

prior action plan to prevent sql database corruption
DISTINCT alternatives in sql query
conversion of query scan to seek

Fetch row and display in frontend column

Kesiena — Sat, 26 Aug 2023 13:14:00 GMT

I have 5 tables in my db, I used one form to saved into 4 of them at once. I want the 5th one to be used as a master where I can store the foreign key and be used to display to a table in the frontend.
How do I make all of them add one ID?
How can I display a table row as a column in the frontend?

Multi-user use the database at the same time

Siavash.BRY — Sat, 12 Aug 2023 09:49:00 GMT

When we have multiple users, What's the best way connect(logging, query on tables,...) to database?

Which is better and why?

1) Create users(DB User) in the SQL Server for each user(Windows App User).

2) Create a table which is fill with all App user and just connect to SQL Server with a user and query on that table.

Tough one

Richard Andrew x64 — Mon, 31 Jul 2023 14:59:00 GMT

I can't figure out the following:

I have order header and detail tables with UPC codes and the order numbers they belong to. There can be many different UPC codes for each order. The Order IDs are in the header table and the UPC codes are in the detail table.

I must find UPC codes that have been used for more than one order, in other words, where the same UPC code exists for two or more distinct orders.

I have tried:

SELECT OD.UPC, OH.OrderId, COUNT(DISTINCT OH.OrderId) [COUNT]
FROM OrderDetail OD
LEFT JOIN OrderHeader OH ON OH.OrderHeaderId = OD.OrderHeaderId
GROUP BY OD.UPC, OH.OrderId
HAVING COUNT(DISTINCT OH.OrderId) > 1

But this query returns nothing and I'm not sure if it's because there are no duplicate orders or because the query is wrong.

Any help will be tremendously appreciated. Thank you.

The difficult we do right away...
...the impossible takes slightly longer.

Why do some websites provide Demo for their premium html themes while the codes can be seen using inspection tool in chrome?

Alex Dunlop — Tue, 11 Jul 2023 16:30:00 GMT

There are some website that provide Demos for their premium Html/css/javascript themes while we can see and copy the source code without paying anything. Why?
Why don't they only provide a snapshot of the website theme to prevent any illegal copy?