|
Hi,
I have a Oracle DB in which all the information is stored and this information is been consumed by different teams and below is the two options i have to decide on :
1. Write a Stored procedure and give the stored procedure to the respective teams and they can call the stored procedure.
2. Write a stored procedure wrap it as an API and expose the API, different team will call the API exposed and API will in turn hit the SP and return the response to the team.
Like to know what is the PRON and CRONS with these options and what is the best possible solution to go with.
Thanks
|
|
|
|
|
|
We are working on separate computers in different locations so we are not connected to a network.
We are using SQL Server 2016 Express to develop a C# application in Visual Studio (college project).
My group mate sent me the (.bak) and (.mdf) files of the database he was working on so I can view it and make the necessary updates. I saw the data in the files after restoring and attaching them respectively. Some columns that were in (.bak)file were not in the (.mdf) file, and I need the (.mdf) file to be updated/contain the same data with the .bak file so I can add a datasource to the application for easy retrieval of data.
What could possibly have been the problem?
|
|
|
|
|
kmllev wrote: What could possibly have been the problem?
Could have been any one of a million things. Without much more information it is impossible to guess.
|
|
|
|
|
kmllev wrote: What could possibly have been the problem?
Are you sure the columns are in the .bak file?
Restore it to a new database, and make sure you have rights to write to the database; post any errors you see here 
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
|
How to speedup the execution of a query in Sql Server?
|
|
|
|
|
In SSMS under tool the first entry is SQL Server Profiler - use that as your first step.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
You need to present a bit more detail so that some suggestions can be given.
Generally, are you linking tables together ? How many? How many rows are in each table?
How many rows are being returned in your query?
With the SQL profiler you will be able to tell whether the server is scanning the table or seeking data via an index.
Your question is too vague to get a valuable answer.
|
|
|
|
|
Hi David Mujica,
Thanks for your reply,but I need just rough idea or tips.
|
|
|
|
|
My response was a tip, without more information we can't really be more specific. Statements like check you indexes (profiler helps) and check your syntax are going to be basically all we can do.
Tuning a DB and queries is almost an art so you need to give us some subject matter. What you have given us is like asking why your painting is rubbish!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
After checking your keys and indexes, you could try to omit as much functions in the query as possible, limit the case-when switches and remove any unused tables and/or columns.
MSDN also has a few pages on the subject
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Hi Eddy Vluggen,
It helps me a lot. 
|
|
|
|
|
Hello there. I am trying to get data from 3 different tables based on simple join. One of the tables can have multiple values against one primary key. Here are the table designs
Table 1 - EmployeeDetails
EmployeeId INT, FirstName VARCHAR, SurName VARCHAR, SexId INT
Table 2 - EmployeeSex
SexId INT, Sex VARCHAR
Table 3 - EmployeeContacts
EmployeeId INT, Contact VARCHAR
I am using following query
SELECT ED.EmployeeId, ED.FirstName, ED.SurName, GROUP_CONCAT(EC.Contact)
FROM EmployeeDetails ED, EmployeeSex ES, EmployeeContacts EC
WHERE ED.SexId = ES.SexId AND ED.EmployeeId = EC.EmployeeId AND ED.EmployeeId = 'emp_password';
Now this query works fine if we have at least one contact number. But if there are not contacts, then this results in empty set. What is wrong with this query ? How can I improve so that it works in all scenarios (regardless of number of contacts in EmployeeContacts table). Thanks for any input.
|
|
|
|
|
Use joins, not a where condition
SELECT ED.EmployeeId, ED.FirstName, ED.SurName, GROUP_CONCAT(EC.Contact)
FROM EmployeeDetails ED
INNER JOIN EmployeeSex ES on ED.SexId = ES.SexId
LEFT JOIN EmployeeContacts EC on ED.EmployeeId = EC.EmployeeId
WHERE ED.EmployeeId = 'emp_password';
If there are instance where an employee does not have an assigned sex, change the INNER JOIN to a LEFT JOIN
=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================
|
|
|
|
|
Hi.
I have to choose between MySQL and PostgreSQL. I like use MS SQL, but it expensive for the customer.
Database have about 100 tables with max. 200 thousands rows.
Front application is .NET (winform) ,dataset or entity framework.
It's critical application, reliability is required.
Please advice me.
Karol
|
|
|
|
|
Member 9076609 wrote: It's critical application, reliability is required.
Sounds like Oracle or SQL Server are the only two choices to consider.
|
|
|
|
|
If it isn't too big, consider SQLServer Express.
You may need to get creative in access, but that can be done.
Don't forget to build some database maintenance, backup, etc.
|
|
|
|
|
Member 9076609 wrote: Please advice me.
Leave the choice to the customer.
Program against the IDb-interfaces and use ANSI-92 SQL
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Well, there's a lot of comparisons out there to read if you want to.
But the tl;dr is:
Performance and scalability -> MySql
Reliability, data integrity and standard compliance -> PostGreSQL.
While MySQL has gotten a lot better with the last versions it's still lacking a lot of functionality. And while it also can be (a lot) faster, it's only in certain scenarios.
If you do a lot of paging, PostGres is the one outperforming everyone else.
My personal choice would be PostGres, but mostly because it's more familiar as I have mostly used Oracle and SQLServer.
|
|
|
|
|
|
Hi,
I have below stored procedure..
it only works when I pass single value like "US" but not when passing multiple values like "AE", "BH", "US", "FR"
when passing multiple values I get this error:
Procedure execution failed
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '")' at line 1
here is the stored procedure:
CREATE DEFINER=<code>root</code>@<code>127.0.0.1</code> PROCEDURE <code>sp_populate_memo_country_companies</code>(IN param_country varchar(255))
BEGIN
SELECT locations.location_id, companies.company_name, locations.location_name, first_payroll, last_payroll
FROM locations
JOIN country ON country.country_code_alpha2 = locations.country_code
JOIN companies ON companies.company_id = locations.company_id
LEFT JOIN payroll ON payroll.location_id = locations.location_id
WHERE locations.country_code IN (param_country) AND payroll_active = TRUE
GROUP BY locations.location_id
ORDER BY companies.company_name;
END
Kindly help...
Thanks,
Jassim[^]
Technology News @ www.JassimRahma.com
|
|
|
|
|
Same answer as last week! You need to split the string into individual values.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
it is indiviual values. For example, If I try:
WHERE IN("BH", "US", "FR")
it will work but when I try:
IN(param_country)
then pass "BH", "US", "FR" it won't work.
Technology News @ www.JassimRahma.com
|
|
|
|
|
What is the exact content of param_country when you try to execute this procedure? The error message is clearly telling you that some part of it is not valid syntax for this version of MySQL.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
