|
For other readers, especially our younger ones;
Eddy Vluggen wrote: A single entity; SQL Server allows for multiple files on multiple drives, but still represents your DB as a single file. You do not even have to care on what drive your record gets stored, that's a problem for the database.
Instead of querying the FS and reading files, you suddenly need not care.
All you need to care about is the SQL language to manage your data. That was small revolution!! And yes, it is a Data Abstraction Layer. It abstracts away the storing of data from the OS to a uniform platform that can be queried with the SQL language.
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.
|
|
|
|
|
Calin Negru wrote: If you ask me it`s not a DB job to abstract the idea of files.
Abstraction is literally what it is all about whether we are talking about databases, file systems, or even, say, cooking. A cake recipe, for example, is an abstraction of the process of converting a particular set of ingredients into a particular cake. The recipe is not the cake itself, the ingredients from which the cake is made, or the physical process of making the cake. It is metadata for an abstraction of all three. Databases and file systems are abstractions of magnetic charges on a disk platter filtered through many layers of other abstractions rising to their 'visible' representations. Databases and file systems are different tools for different purposes, so it is not surprising that their representations of those magnetic charges differ.
A database is not a file system. While it might store its contents in files, it needn't. Until version 19, for example, Oracle allowed for 'raw' storage so that no file system was even involved. While a database can store its data in files that can be 'seen' in the file system, that is only a handy method of organization so that they can be managed, as files, by file system tools such as Windows Explorer or Linux commands such as ls, cp, and del. Internally, those database files are not file systems, and they cannot be abstracted into a file system-like representation. Because most databases are relational and not hierarchical, there is not even a plausible way in which to represent them as if they were file systems.
Software that represents logical database structure hierarchically such as SQL Server Management Studio (for Microsoft) and SQL Developer (for Oracle) are not representing the physical structures within the data files hierarchically but rather the logical organization of tables according to the 'recipe' provided by the data dictionary. Internally, database file contents are not files and folders tucked away inside the files that appear in the file system. SSMS and SQL Developer are only representing the logical organization of data tables, indexes, tablespaces and what not in a hierarchical way. But though this may give the impression of a file system inside the database files, there is no such thing within them.
Going back to the original question:
Quote: Does the difference reside in the fact that in a data base file the data is saved in a fashion that reminds the method in which the OS is saving a HDD directory/folder tree (and that is by using tags)?
No. It is nothing like that. Databases are not file systems 'inside' their files. (My expertise is with Oracle.) Oracle databases have logical structures (segments, extents, and blocks) within data files where the 'location' of a particular row of data is an ephemeral 'address' in terms of those structures, called a ROWID, that looks something like this: 'AAAABUAABAAAAQRAAA'. The data block is the fundamental unit of storage in an Oracle database. Blocks are organized into extents which are combined into segments. A segment is one table or index, and there can be many segments in a data file. A table or index is merely a logical abstraction of the physical segments containing their data, and to be understood, those segments, extents, and blocks must be perceived in context of the data dictionary where the abstractions give meaning to the physical structures.
No doubt SQL Server has something similar. Each database will have its own internals.
Cheers,
Russ
Oracle DBA: 23 years
UNIX SSA: 32 years
|
|
|
|
|
Cannot initialize the data source object of OLE DB provider "Microsoft.Ace.OLEDB.12.0" for linked server "(null)"
|
|
|
|
|
The clue in that message is "(null)". So use your debugger to find out what null item you are passing and why.
|
|
|
|
|
|
We use a legacy application (vb6 / access). The UI uses DAO to connect to the local front-end MDB file. Local tables are linked to the back-end mdb file which is located on the server.
So far so well.
The problem is that the back-end MDB is now 1,8 Gb and it will be 2 Gb pretty soon, which is fatal in MS-Access world.
We're thinking of replacing the back-end db with sql server express, so we'll have to make minimal changes to the front-end databases (just the connection string).
Is it a good idea? What is the correct procedure to import the back-end MDB to SQL server express? Which version (LocalDB etc) should we use?
Thanks in advance.
PS
There is no option of making/buying a new application. We are trying to solve our problem with minimum effort.
modified 9-Aug-22 4:10am.
|
|
|
|
|
There seems to be plenty of resources about moving data from Access to SQL Lite, I would be more worried about the queries you have supporting your application, Access sql is not the same as TSQL. You will need to assess whether the queries will work (not a trivial proposition)!
I would be more concerned that the UI is in VB6, and changes to support Sql Lite are going to have no support outside your people.
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
Actually I meant SQL Server (Microsoft), not SQL Lite.
|
|
|
|
|
|
Well, I installed the SQL server and made the migration too.
However, there are some problems with my source code: To open recordsets with DAO, I use
Set rs = currentdb.OpenRecordset("test", dbOpenDynaset)
which seems not to be ok with SQL server, since rs.FindFirst can't find anything.
After some googling, I found out that I should change to:
Set rs = currentdb.OpenRecordset("test", dbOpenDynaset, dbSeeChanges)
so now i'm able to use rs.FindFirst, but i'm facing a new problem:
Can't use rs.AddNew
Any ideas?
PS there are hundreds of thousands of code lines, so changing the way my software works is not an option. Like I said, I'm just trying to make things work as is.
|
|
|
|
|
Any details you can provide about why you can't use the rs.AddNew? (Permission / Not Found / ? )
|
|
|
|
|
To buy yourself some time why not split the backend database into multiple linked databases?
|
|
|
|
|
Well, having multiple back-ends would do the job for some months more, but like you said, this is not a permanent solution.
|
|
|
|
|
Hello all,
Some time ago I created a PHP web page that works against a MySQL database to keep track of all my worked hours as a freelance.
That works well, and I use it extensively.
The biggest issue I have while using it is when I am at a customer company without a reliable internet connection, and I can't remotely connect my office server to use it.
I've been thinking of installing XAMPP in my laptop and copying the server database there to be able to work while being offline.
Then I could backup the database as soon as I have internet connection or use a backup program to keep it safe.
BUT:
I am not the only one working with the database at the same time and this makes things much more interesting.
How would you approach this situation?
Of course, I can keep doing what I am doing nowadays: write everything in any other offline program or editor and do it when I get into a hotel or the office again.
Thank you all!
|
|
|
|
|
|
Thanks for the link, Gerry.
Seeing all that I think I'll keep connecting remotely to reduce the issues + cost.
|
|
|
|
|
Assuming your timesheet entry is flat and there is no conflict between in house and of site work I would store it locally in a copy of the server DB. Then simply bulk copy from the local DB to the server.
Or It should be a simple exercise to write an off site storage and entry app (excel would do) that you can then load into the server DB.
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
I have the following query in my own photo database:
select distinct left(Nr, 8) as Date, Ort from Fotos where Typ='D' order by left(Nr, 8) desc, Ort
Where Nr is a unique string with date + a hyphen + a sequential number in the form yyyymmdd-xxx. Ort is a string, the location of the photo.
This works fine so far and I get a list like this:
20220717 Lindabrunn, Symposion
20220717 Feistritzsattel
20220717 Pernitz, Schärfthal
20220715 Mannswörth
20220715 Wienerbergteich
20220715 Zentralfriedhof
20220712 V
20220710 Himberg
But what I really want is the first Nr for a date and a location like this:
20220717-001 Lindabrunn, Symposion
20220717-045 Feistritzsattel
20220717-103 Pernitz, Schärfthal
20220715-001 Mannswörth
20220715-009 Wienerbergteich
20220715-033 Zentralfriedhof
20220712-001 V
20220710-001 Himberg
Can someone help? Thanks
|
|
|
|
|
Assuming SQL Server, try something like this:
WITH cteOrderedData As
(
SELECT
Nr,
Ort,
ROW_NUMBER() OVER (PARTITION BY Left(Nr, 8), Ort ORDER BY Nr) As RN
FROM
Fotos
WHERE
Type = 'D'
)
SELECT
Nr,
Ort
FROM
cteOrderedData
WHERE
RN = 1
;
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Do you really need something as complex as a CTE? Couldn't you simply do the following (off the top of my head, not tested; so caveat emptor):
SELECT TOP (100) PERCENT
Nbr,
Ort
FROM
Fotos
WHERE
Type = 'D'
ORDER BY
SUBSTRING(Nbr, 1, 8) DESC,
Ort
|
|
|
|
|
The problem is that the OP wants to extract the first record for each unique 8-character prefix, whereas your code will return all records for each prefix.
For example, given the input data:
| Nbr | Ort |
|--------------|-----|
| 20220717-001 | A |
| 20220717-002 | B |
| 20220717-003 | C | Your code would return all three, whereas the OP only wants the first one.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Thanks. I hadn't realised that. OP's sample output has multiple 20220717 dates (minus the suffixes) in it, so I had read it as 'sort all by date + Ort, ignoring the sequence no after the date'/
|
|
|
|
|
Received a solution that has built in .sql script files to create tables and even a stored procedure....
I have built plenty of DataTable structures and used StreamReader to fill the table with csv data. I even use XML to build schemas and import data into DataTables. but, I have never used scripting to build a data structure before.
The solution has Dapper as a dependency but, again, I am not a big time EF developer... Any suggestions how to get the .sql into a DataSet or a DataTable or both would be greatly appreciated.
I have scoured all sorts of sources to give me a concrete example to no avail. When I view the .sql in VS it displays the designer similar to Access.
|
|
|
|
|
An .SQL file is just a text file with SQL statements in it.
You can execute them in C# just like you do for any other query. Just read the file and load the text into a variable and pass that as your statement to an SqlCommand.
|
|
|
|
|
I have a table with the following schema:
DECLARE @images TABLE
(
[Id] BIGINT NOT NULL,
[UserId] NVARCHAR(450) NOT NULL,
[VehID] BIGINT NOT NULL,
[VehImage] VARBINARY(MAX) NULL
);There are no exactly 10 rows per user. Currently, all VehImage columns are null (intentionally).
Here is my sql query (@images is a table variable that I pre-populate with the desired records):
SELECT UserId,VehID
,[Image1],[Image2],[Image3],[Image4],[Image5]
,[Image6],[Image7],[Image8],[Image9],[Image10]
FROM (
SELECT UserId
,VehID
,VehImage
FROM @images
) AS SourceTable
PIVOT (
Count(VehImage)
FOR VehImage IN ([Image1],[Image2],[Image3]
,[Image4],[Image5],[Image6]
,[Image7],[Image8],[Image9]
,[Image10])
) AS PivotTable;I have the pivot working, in that I get exactly one row back with the expected UserId, and VehId, and 10 image columns. My problem is that the Image columns all have the value 0 instead of the expected null.
What am I doing that would cause that?
EDIT ----------------------------------------
I changed the aggregate function from count to max, and all of the images are now showing null as expected. I don't know yet if that's the ultimate solution.
EDIT #2 -------------------------------------
Nope, I plugged a fake value into the first image record, and all of the image columns are still coming back as null...
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013
modified 19-Jun-22 8:13am.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
