|
I have a sample table you can see here. This self-referencing table is used for creating a tree structure in an Angular project. I want to write a SQL query to create a JSON result with a parent-child structure. Please help me.
|
|
|
|
|
You can't use FOR JSON to generate recursive JSON documents. You'll need to create a recursive user-defined function instead.
CREATE OR ALTER FUNCTION dbo.fn_OrganizationJson(@ParentId int)
RETURNS nvarchar(max)
As
BEGIN
DECLARE @json nvarchar(max);
If @ParentId Is Null
BEGIN
SET @json = (SELECT Id, Name, dbo.fn_OrganizationJson(Id) As Children FROM dbo.Organizations WHERE ParentId Is Null FOR JSON AUTO);
END
Else
BEGIN
SET @json = (SELECT Id, Name, dbo.fn_OrganizationJson(Id) As Children FROM dbo.Organizations WHERE ParentId = @ParentId FOR JSON AUTO);
END
Return @json;
END
GO SQL Fiddle[^]
NB: In some versions of SQL, you can't create a function that refers to itself if the function doesn't already exists. You may need to CREATE FUNCTION first with a dummy body, and then ALTER FUNCTION to add the implementation.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Hi,
I want to write a program whose input is the names of the companies and when adding the name of each company, it will take various information from that company of different types,
1- TextBox (daily production rate),
2- CheckBox (select product features),
3- OptionButton (the gender of the company owner),
4- Date (Product delivery time, yy/mm/dd), ... in a form and store all that information in the profile of that company.
What programming language should I use for this task so that I can design a beautiful appearance (GUI) for entering information and this information can be entered easily by the user?
Is there a pre-written program that is close to my goal so that I can improve it to suit my needs?
Thanks in advance.
|
|
|
|
|
niksirat2030 wrote: What programming language should I use for this task so that I can design a beautiful appearance (GUI) for entering information and this information can be entered easily by the user?
You should use the language you know the best.
|
|
|
|
|
niksirat2030 wrote: What programming language should I use for this task so that I can design a beautiful appearance (GUI) for entering information and this information can be entered easily by the user?
You are doing this back to front. You need to start with the business logic, and the data access. The front end can be beautied later, and if you want it to be really beautiful then employ a graphic designer.
|
|
|
|
|
niksirat2030 wrote: 3- OptionButton (the gender of the company owner),
Homework.
Do it.
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.
|
|
|
|
|
The language you use does not matter.
Your app "description" smells of homework. You're not going to find a "pre-written" program to do this. If you did, and you used it in your own app, and turned it in, you would get a failing grade.
|
|
|
|
|
I`m trying to understand how a data base file is different than an usual file (how data stored in a DB file is different than data saved in an usual file). 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)?
|
|
|
|
|
Calin Cali wrote: I`m trying to understand how a data base file is different than an usual file
At the physical level it isn't. A file is just a collection of bytes stored (usually) on a physical device. The difference is how the software accesses the different parts of the file to enable it to be processed. A text file is just a single stream of characters using line end markers to separate the records. A database is a collection of tables that are structured by the database engine and accessed by various lookup methods. For the operating system the data remains just a collection of bytes that the DB engine accesses by logical addresses.
|
|
|
|
|
|
Not sure if it was paradox, but there was a database that stored each table as a file in a directory. It acted like a database, so no tree.
If I had to write a database-server, I'd start with reserving space, say 2Gb. The first Mb is used to name tables and find their location in that space. That's how lots of database-drivers and servers work; a server, like SQL Server would also reserve space for indexes and stored procedures.
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.
|
|
|
|
|
Quote: It acted like a database, so no tree
Well yeah. The inner workings of the first (DB) is one thing the inner workings of the second (Directory tree) is something else, a DB doesn`t have to store data like a tree, or probably never stores data in a tree type structure. I assume the tables in your example were saved in the same folder and there was no creation of new folders and folder trees.
modified 21-Aug-22 9:07am.
|
|
|
|
|
You don't need to; even if the database file is a flat file, you can still define trees in the database.
SQL would not work nicely if tables could be structured like trees. You can still define your data as a tree in the db, no problem.
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.
|
|
|
|
|
>you can still define trees in a simple database file
I`ll take your word for that. It was the DB creating multiple files that me wondering how far it goes with that approach.
|
|
|
|
|
Calin Cali wrote: I`ll take your word for that. It was the DB creating multiple files that me wondering how far it goes with that approach.
It doesn't; it abstracts away the idea of files. You could say it is a data abstraction layer 
If you want a tree in a database, you create a database-relation between columns. SQL can account for that, not for files.
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.
|
|
|
|
|
Quote: It doesn't
it doesn`t what? (no explicit question in my post)
Quote: it abstracts away the idea of files
If you ask me it`s not a DB job to abstract the idea of files. A DB usually is concerned with how the data is kept within a (single) file. Although interesting you DB marginal example bears no relevance to how stuff usually works in a DB. Thanks for the discussion.
[edit]
does saving data to a DB resemble with saving data do a file. to some extent yes. but a DB is much more than that
modified 21-Aug-22 15:30pm.
|
|
|
|
|
Calin Cali wrote: it doesn`t what? (no explicit question in my post)
Use the hierarchy that the OS provides. Databases are not files and folders, but lists of stuff called tables.
Calin Cali wrote: If you ask me it`s not a DB job to abstract the idea of files
I did not ask, I merely explained it. Any DB has tables and abstracts you away from the FileSystem.
Calin Cali wrote: A DB usually is concerned with how the data is kept within a (single) file.
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.
Calin Cali wrote: Although interesting you DB marginal example bears no relevance to how stuff usually works in a DB. Thanks for the discussion.
This is the basic for every database, from SQLite to Oracle 
Calin Cali wrote: does saving data to a DB resemble with saving data do a file. to some extent yes. but a DB is much more than that
A database file IS a file. You are comparing it in your mind with a text file; that has to be rewritten every time the size changes - that's not efficient enough!
Imagine a text file, a list of names for example. First, you add unused space. If the user now adds a record, you overwrite that space and no need to change the size (and cause fragmentation on disk).
If I'm a db, I store your list of names in alphabetical order. But you have place-names in that list too. So I make another file, where I save those placenames in alphabetical order with their primary key. That allows for quicker lookups. We call those indexes.
Now, please, explain me how "stuff" usually works in a db?
--edit
My bad, I should have explained it more clearly. It's a good question that helps you understand the difference between storing on the OS and in the DB. Most store where they're told to
Feel free to ask; maybe I can learn to respond without feeling attacked 
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.
|
|
|
|
|
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.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
