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:
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.
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.
There is no option of making/buying a new application. We are trying to solve our problem with minimum effort.
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 -
I'm old. I know stuff - JSOP
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
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.
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.
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.
"Before entering on an understanding, I have meditated for a long time, and have foreseen what might happen. It is not genius which reveals to me suddenly, secretly, what I have to say or to do in a circumstance unexpected by other people; it is reflection, it is meditation." - Napoleon I
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 -
I'm old. I know stuff - JSOP
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.
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):
) AS SourceTable
FOR VehImage IN ([Image1],[Image2],[Image3]
) 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?
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