|
Eddy Vluggen wrote: Also for large documents. A MongoDB cache can span several computers, making it ideal for static blobs that are to be served
Where "large" means what exactly? I was working with a Mongodb instance that would serving up to 100 mb documents although normal ones probably ranged from 100k-2mb.
I didnt' do much to specifically manage that.
At least at one point I was managing documents on a SQL database (Oracle I believe) where the documents were stored in the file system and the database only kept a URL. That of course is exactly what I am doing now with AWS cloud and S3 storage.
|
|
|
|
|
jschell wrote: Where "large" means what exactly?
No specific threshold; means that it is optimized for blobs, nothing else.
jschell wrote: At least at one point I was managing documents on a SQL database (Oracle I believe) where the documents were stored in the file system and the database only kept a URL.
MongoDB can also be spread over several computers. If it fits or works, use it 
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
If you do not know SQL and do not want to know SQL then it works.
In large business application domains it will not be adequate. But those often use disparate and not complementary technologies anyways.
NoSQL **DOES NOT** eliminate the need to manage the database. Never sure where people come up with this claim. Probably because they only use it for toy applications. ANY persisted data store will, over time, require maintenance. Things change over time and that means the data store(s) must be modified to fit those changes. And nothing fixes that.
Ignoring the need of future modifications only makes the need when it arises more difficult.
Getting the data model right is a problem unless you have experience in the business domain. Correcting the data model with NoSQL falls into the problem that I discussed above and certainly seems harder to me in NoSQL. But then I have been using SQL for a very long time so creating such solutions via SQL are going to be easier for me.
NoSQL has no advantage over normal business entities. Things like users, accounts, etc. Again that is more based on longer term business needs and because there are many, many tools that exist to use SQL and thus use such entities. For example to make money account might want to query the customers in the database - via SQL. Since NoSQL has no standards for API the usage of such tools for NoSQL is not as complete. But Mongodb has the advantage that it is the leader so such tools might support it.
Constraint violation is severe problem in NoSQL. It is a severe problem in SQL as well, unless you add the constraints in the first place. And doing that right can be complicated. There are no transactions either. Which means you need to deal with rollbacks if there is an end to end problem. Not an insurmountable problem unless you don't plan for it in the first place. But incorrect usage of transactions can lead to weird and very hard to deal with problems in SQL.
In one Mongodb application I considered that the only way to fix the constraint and transaction problem was to write a server that wrapped it and presented a standard API. It would manage transactions, rollbacks and constraint checking. Of course at that point what I would have had was in fact a relational database (so pointless to have NoSQL.)
I like SQL Stored procedures. Can't speak to any NoSQL solutions except Mongodb but their solution I do not considered a swap in replacement for stored procs. It is more just a way to moved normal business processing to the Mongodb server.
|
|
|
|
|
I've been using SQL for over a decade, and NoSQL is sort of a head scratcher to me.
I'm just using it for training on my personal website to see what it's all about. See why it's on so many job postings. I currently use SQLite on my personal website on .Net Core 2.1 and it works great.
But MongoDB seems lightweight and can be used by any project that has access to it, which means I can use it from Docker Containers. Not sure how to use SQLite from Docker Containers.
Currently, I'm just storing contact messages from contact us. was going to try reviews and portfolios which would be more complex and your right, I would have to get the class model right in order for portfolios to work. I may give up on it and go back to SQLite.
Overall, this Angular 6 project wrapped in .Net Core using MongoDB has been a pain in the butt. 
Now I see why there are so many job postings for the .Net Core Angular 6 person with MongoDB a plus. It may not be achievable on an industrial scale.
I can see storing Countries, States, Notes, reasons, selections on it to take the load off a SQL server.
If it ain't broke don't fix it
Discover my world at jkirkerx.com
|
|
|
|
|
I love NoSQL approaches, but you need to understand the nature and character of the data store that you're using.
I don't know about you, but when I plan the persistence models for domain objects that will be stored in a SQL store, I make a point of trying to generate relationships wherever practical. I know that component parts of a data structure may or may not be independently modified and I just generally strive for de-duplication. The end result, though, is a persisted model that does not remotely resemble my in-memory data structure.
What Mongo does, specifically, is to store a BSON (or Binary JavaScript Object Notation) representation of your data structure in a format called a document. It comes much closer to representing the domain model in the database as your application does in memory, albeit as dynamic types until de-serialized. By and large, if you're familiar with JSON and how your language of choice formats to it, you'll understand instantly how to work with data in a Mongo database.
Moreover the stored data works pretty much just like standard OOP objects: they can contain value types or reverence types. In this case, though, the reference types point to a Mongo store for a different data type with a UUID rather than a memory location. This means that you need to have a solid plan about which objects need to be persisted to maintain a cohesive database.
Now I'm not going to really disagree with Eddy, Mongo IS intended to store BLOBs, but the BLOBs in question are these BSON objects, generated from your classes/structures and squirreled away; not necessarily just images or videos.
As a programmer (and not a DBA), this approach makes more sense to me and is infinitely easier to work with, but is absolutely not appropriate for all applications. For instance, this approach makes sharing data between applications considerably more difficult, and sharing data between languages next to impossible if the serialization strategies are at all different. If you need granular control over edits to specific fields or sets of fields, that must be strictly relegated to code, rather than being able to assign permissions at the database level. While data can be related, but is not relational in nature, it's not fantastic for fine control over update cascades; it's not what I would pick for a strictly CRUD app.
Anyway, have fun with it and see how it fits you. IMO it's a great tool for the box.
"Never attribute to malice that which can be explained by stupidity."
- Hanlon's Razor
|
|
|
|
|
Nathan Minier wrote:
Now I'm not going to really disagree with Eddy, Mongo IS intended to store BLOBs, but the BLOBs in question are these BSON objects, generated from your classes/structures and squirreled away; not necessarily just images or videos.
Just the first thing that came to mind when thinking of an example of a blob.
Nathan Minier wrote: but is absolutely not appropriate for all applications
A NoSQL db will not be optimized to run (complex) queries. There's little in terms of constraints to keep the data consistent or correct.
Nathan Minier wrote: IMO it's a great tool for the box.
Exactly; as long as you don't silver bullet it and start using it everywhere.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
These are arguments that I see a lot and largely consider to be straw men, based on the difference between the overall approach to using the different systems. Yes, NoSQL doesn't work like SQL; it's in the name.
Eddy Vluggen wrote: A NoSQL db will not be optimized to run (complex) queries.
Largely a NoSQL DB will not require queries nearly as complex as a SQL server, due to the non-relational nature of the data, making JOINS fundamentally inappropriate. Because the data is bundled similarly to an in-memory object, you end up treating it as a collection selection process rather than a database query. This is one of the things that I like about it, as I don't need to start "thinking in SQL" when I need to hydrate objects. This is also a minus, because you don't have the flexibility to establish new relationships without a change at the application level; so for someone that works on things at both the application and database layers this makes ad hoc querying difficult at best.
Eddy Vluggen wrote: There's little in terms of constraints to keep the data consistent or correct.
Since you will not be using an object from different languages, i.e. an object in a store will need to be derived from the same library or have an adapter to be read in the first place, pushing constraints into the database is not appropriate. Your applications (or shared class library) should be handling that. I would argue that relying on constraints for consistency in a SQL system is exceptionally bad practice as well; your application should have some understanding of what an acceptable value is.
The real difference to me is that SQL is great when you don't know what will be producing or consuming the data, or how they will be doing it. It is very powerful in that regard, and when you're a DBA and have no idea what crazy app dev will be using your data, all these controls are appropriate and necessary.
If, however, you're an app dev making a purpose-built system that you do not intend to expose data for across several platforms, NoSQL brings a hell of a lot to the table. For one: you don't need to play these SQL translation games, and your DAL doesn't need to be written in a completely different language.
"Never attribute to malice that which can be explained by stupidity."
- Hanlon's Razor
|
|
|
|
|
Nathan Minier wrote:
Since you will not be using an object from different languages, i.e. an object in a store will need to be derived from the same library or have an adapter to be read in the first place, pushing constraints into the database is not appropriate. Your applications (or shared class library) should be handling that. I would argue that relying on constraints for consistency in a SQL system is exceptionally bad practice as well; your application should have some understanding of what an acceptable value is.
While I agree with most, here we differ very much; if it is relational data, then I will be expecting constraints at the database-level. You are allowed to duplicate the checks in your application ofc (I no care), but the database is responsible for the correctness of its contents, regardless how the programmer implemented the UI.
Unless it is a local data-cache, your db is going to be serving multiple UI's. Without a decent db, one of those UI's will f*** the complete db, as Murphy's law predicts.
Nathan Minier wrote: The real difference to me is that SQL is great when you don't know what will be producing or consuming the data, or how they will be doing it. It is very powerful in that regard, and when you're a DBA and have no idea what crazy app dev will be using your data, all these controls are appropriate and necessary.
Nah, depending on the nature of the data that might be done as well by NoSQL.
Nathan Minier wrote:
If, however, you're an app dev making a purpose-built system that you do not intend to expose data for across several platforms, NoSQL brings a hell of a lot to the table. For one: you don't need to play these SQL translation games, and your DAL doesn't need to be written in a completely different language.
The db is my DAL. I speak to it in a generilzed abstract query language.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
Eddy Vluggen wrote: Unless it is a local data-cache, your db is going to be serving multiple UI's. Without a decent db, one of those UI's will f*** the complete db, as Murphy's law predicts.
Actually, we're completely agreeing here, which is what I tried to address in the next section you quoted; I suppose I could have been more clear about that. I just don't assume the technology that mucks with the data will be a UI
"Never attribute to malice that which can be explained by stupidity."
- Hanlon's Razor
|
|
|
|
|
Nathan Minier wrote: I just don't assume the technology that mucks with the data will be a UI
"Any kind of client" then
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
i have three tables:
Table_provider
Name_Provider
Table_purchase
ID_Purchase
Date_Purchase
Name_Provider
Amount
Table_payment
ID_payment
Date_payment
Name_Provider
Amount
I want to get this resutl:
Name_Provider Sum(Amount purchase) sum(Amount payement)
thanks in advance:
|
|
|
|
|
Member 10283191 wrote: I want to get this resutl:
Try SQL. You're welcome.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
Im developping vb.net application with access database thats why i want this query
|
|
|
|
|
I would love to help if you have a question, but you are ordering code.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
Identify the primary/foreign keys, make the joins and create the query.
If name_provider is your FK then you are screwed as that is an editable field. Get a book on SQL and that will explain the errors in that structure.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
As others have pointed out, that is a bad table schema. If you insist on having separate tables for purchase and payment then it should look like this:
Table_provider
ID_Provider
Name_Provider
Table_purchase
ID_Purchase
Date_Purchase
ID_Provider
Amount
Table_payment
ID_payment
Date_payment
ID_Provider
Amount Personally I would have single transaction table with a transaction type column but each to their own.
Whether you follow my advice or not, you will need to JOIN the tables based on the column that is common to all of them ID_Provider or Name_Provider if you leave things as they are. Here is an article that tells you how to do that Joining Tables in SQL[^]
Note we are referring to "SQL" in the sense of T-SQL - the "language" and not SQL Server the database. As you are connecting from VB.NET to Access I presume you are using ADO or OLEDB - both of which will require SQL statements.
Once you have worked out how to join your tables you already have the SELECT clause essentially written...
SELECT Provider.Name_Provider. Sum(purchase.Amount), sum(Payment.Amount)
Give it a go, but if you still get stuck reply to this message with the code that isn't working and we will try to help.
|
|
|
|
|
CHill60 wrote: Personally I would have single transaction table with a transaction type column but each to their own
This only works for a retail POS system where there is no credit supplied. As an invoicing exercise the link between purchase and payment is never designed as 1-1.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Quote: As an invoicing exercise the link between purchase and payment is never designed as 1-1.
Good point, although I wasn't trying to suggest that sort of link between an invoice and a payment, just that in its simplest terms a transaction is money either positive or negative. Similar to Accounting Systems Model[^]
Of course the OP doesn't have a column to indicate a credit transaction on the purchase table anyway. Or an Order table, or Invoice, or product .... 
|
|
|
|
|
I suspect the OP is either doing a training excercise or, Ghu forbid, writing something for a friend in need, one does not know retail and the other does not know software, fun times.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi all,
I have a stored procedure below:
ALTER PROCEDURE [dbo].[Usp_Add_ProgramType] (@Type varchar(15), @Description varchar(15), @IsValid bit
, @CreatedBy varchar(500), @ModifiedBy varchar(500))
AS
BEGIN
if not exists(select top 1 1 from [dbo].[ProgramTypeLKP] where [Type]=@Type)
begin
INSERT INTO [dbo].[ProgramTypeLKP] ([Type], [Description], IsValid, CreatedDate, CreatedBy, ModifiedDate, ModifiedBy)
values(@Type, @Description, @IsValid, getdate(), @CreatedBy, GETDATE(), @ModifiedBy)
end
--else
--begin
----Want to return some thing here that makes EF funnction to return 0 or -1 or some value so that I can show user a message that its a duplicate record.
--end
END
Even if the Condition fails the EF method is returning value as 1, but I want to get a value on which I will show the end user a message that he is trying to attempt to insert a duplicate record.
Like in the below script, I am checking for the result or errors, I have to get error in the response, or at least in the result some where so that I can show the user a message, I am using Kendo Grid, Create and Update with Popup (just a foot-note):
function onRequestEnd(e)
{
//Check request type
if (e.type == "create")
{
if ((e.response != null) && ((e.response.Errors != null) || (e.response.data["0"].Result <= 1)))
{
//Set some label value in red only when to say attempt to insert duplicate record
}
}
else if (e.type == "update")
{
if ((e.response != null) && ((e.response.Errors != null) || (e.response.data["0"].Result <= 1)))
{
//Set some label value in red only when to say attempt to insert duplicate record
}
}
}
Can anybody please help me in this regards, thanks in advance.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
ProgramType - I would have assumed that the client app already has a list of prexisting records to check against.
Assuming ProgramType has an identity field to generate the ID I would return the Select @@Scope_Identity after the insert or select -1 in the else segment
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
ALTER PROCEDURE [dbo].[Usp_Add_LookupRecord] (@LookupTableId int,
@Type varchar(100), @Description varchar(500), @ForeignKeyId int, @CreatedBy varchar(500), @ModifiedBy varchar(500), @IsValid bit<br />
)
AS
BEGIN
declare @LookupTableName varchar(100)=(select top 1 LookupTableName FROM [dbo].[ListOfLookupTables] WHERE PkListOfLookupTablesId=@LookupTableId)
declare @InsertedId int=0, @FKProgramTypeLKPId int = (select top 1 PKProgramTypeLKPId from ProgramTypeLKP where Type='Mental Health')
IF (@LookupTableName='ProgramTypeLKP')
BEGIN
IF NOT EXISTS(SELECT Top 1 1 FROM dbo.ProgramTypeLKP WHERE [Type]=@Type AND ISNULL(IsValid, 1)=1)
BEGIN
INSERT INTO dbo.ProgramTypeLKP ([Type], [Description], CreatedDate, CreatedBy, ModifiedDate, ModifiedBy, IsValid)
VALUES(LEFT(@Type, 15), LEFT(@Description, 500), GETDATE(), LEFT(@CreatedBy, 500), GETDATE(), LEFT(@ModifiedBy, 500), @IsValid)
SET @InsertedId=SCOPE_IDENTITY()
END
ELSE
BEGIN
RETURN 0
END
END
ELSE IF (@LookupTableName='AddressTypeLKP')
BEGIN
IF NOT EXISTS(SELECT Top 1 1 FROM dbo.AddressTypeLKP WHERE [Description]=@Type AND ISNULL(IsValid, 1)=1)
BEGIN
INSERT INTO dbo.AddressTypeLKP ([Description], FKProgramTypeLKPId, CreatedDate, CreatedBy, ModifiedDate, ModifiedBy, IsValid)
VALUES(LEFT(@Description, 20), @FKProgramTypeLKPId, GETDATE(), LEFT(@CreatedBy, 30), GETDATE(), LEFT(@ModifiedBy, 30), @IsValid)
SET @InsertedId=SCOPE_IDENTITY()
END
ELSE
BEGIN
RETURN 0
END
END
RETURN @InsertedId
END
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
That is one nasty design! I would have a seperate procedure for each table!
indian143 wrote: IF NOT EXISTS(SELECT Top 1 1 FROM dbo.ProgramTypeLKP WHERE [Type]=@Type AND ISNULL(IsValid, 1)=1)
Test the result of that query - I'll bet it never finds a record.
The following does not seem to make sense outside of an IF statement.
indian143 wrote: declare @LookupTableName varchar(100)=(select top 1 LookupTableName FROM [dbo].[ListOfLookupTables] WHERE PkListOfLookupTablesId=@LookupTableId)
declare @InsertedId int=0, @FKProgramTypeLKPId int = (select top 1 PKProgramTypeLKPId from ProgramTypeLKP where Type='Mental Health')
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Yes I can understand that, but there are 25 tables with same Columns Code and Description, one table is selected with Dropdown to add, update or delete, so I have written just one stored procedure instead of writing multiple ones. That's why I used LookupTableId, Because I have listed all those tables into one LookupTable and the LookupTableId comes from that table.
I think it better suited for this scenario, but yes in general its not a good practice.
About Program Type, its always the same Program Type that's why I just took the id and used it.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
How are you calling the stored procedure? The ExecuteSqlCommand method returns the number of rows affected, not the return value of the stored procedure.
There are two ways to get the return value:
var returnValue = new SqlParameter()
{
ParameterName = "@ReturnValue",
SqlDbType = SqlDbType.Int,
Direction = System.Data.ParameterDirection.Output
};
context.Database.ExecuteSqlCommand("exec dbo.Usp_Add_LookupRecord", returnValue, ...other parameters here...);
int result = (int)returnValue.Value; Or:
int result = context.Database.SqlQuery<int>("exec dbo.Usp_Add_LookupRecord", ... parameters here ...).First();
sql server - EF ExecuteStoredCommand with ReturnValue parameter - Stack Overflow[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
