|
Amr Muhammed wrote: I hope you understand what I want
Please do the math...You present the user with 10 million rows. They spend 0.1 second looking at each row. How long will it take them to get to the last row?
This should demonstrate to you and even to the user that there must be some other solution.
Usually that solution is that user does in fact know what they are looking for in that 10 million rows. And if you asked for that and then used that information then you could give the user 1000 rows, or even 10 rows.
|
|
|
|
|
I have two tabs shown below:
<ul>
<li><a href="#tab-1">This is Tab 1</a></li>
<li><a href="#tab-2">Tab Two</a></li>
</ul>
tab one contains summary records, like userid, firstname, lastname, etc
Userid is hyperlinked like:
return " " + oObj.aData["RequestID"] + " ";
when a user clicks any of these links, we would like detailed records associated with to display on tab2.
Any ideas how to do this?
|
|
|
|
|
It will be hard to get answer here - as the question is totally unrelated to DB...
Move it to other forum, like web development...
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
|
|
|
|
|
Thanks for letting me know.
Silly of me to have posted such a question on db forum.
|
|
|
|
|
Hi,
I'm looking for a way to delete a row and it's related rows in other tables(aka linked by foreign keys), but without using cascade. I want to prohibit the user from doing a cascade delete, but want to support it so the admin can remove things that don't belong there such as test data etc.
I was looking for something in the direction of a script that searches the system databases for foreign keys so I can find out what is related, as I got too many tables to do it manually. Anyone who can send me in the right direction towards such a script? Using MS SQL Server 2012.
Many thanks in advance.
|
|
|
|
|
Is there any reason why you can't allow the admins cascade delete (never used it myself). I almost always find there are unrelated tables and orphaned data towards the end of a development especially if foreign keys are not implemented, I often leave this till the end of the development. I find the diagram extremely useful in the clean up phase before UAT.
I think there are a number of dependency discovery tools out there including Red-Gate.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Never heard of foreign keys that were dependent on user role. Afaik, foreign keys are independent of user roles. I always implement foreign keys when I create the database structure.
|
|
|
|
|
Nico Haegens wrote: Never heard of foreign keys that were dependent on user role
Wat - where did you get that from? I assume cascade delete is application initiated so controlling who can use it is trivial.
Nico Haegens wrote: I always implement foreign keys when I create the database structure
I wish - I usually have to start the development before the specs are even half done, the data structure ALWAYS changes as the users find out what they want and what they can have so FKs tend to be done when they have stopped running with the goal posts, this usually happens 2 weeks AFTER the app goes to UAT.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I thought delete cascade was only possible at foreign key constraint level. Afaik, Foreign key constraints don't care who is using it, be it a sql admin or an application. Could you explain a bit more, what you mean by saying cascade delete is application initiated? We might be talking about 2 different things.
|
|
|
|
|
A couple of points - I stated I never use cascade delete.
Yes I know that FKs are required for it to be implemented.
You said you wanted to allow admin to use it but prevent users from doing so (or find an alternative). I assume when you say users they are accessing the DB via an application with no direct access to the database(note ASS U ME) which would make it a simple design issue to manage the delete functions within the app.
If your users have direct access to the database then you may be able to manage it via permissions.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I have set my foreign key constraints to disallow cascade effects so there are no accidents in what gets deleted.
Here is how my application works:
- users can't delete, they can only archive, meaning I set a boolean field to true and the item in the database won't appear in anything in the application to the normal user.
- admins can delete, but the problem is that my client doesn't have any IT minded people, so I want to prevent them to accidentily delete a whole bunch of data. Your signature suggests to me, that you are familiar with people who aren't IT minded.
So what I basically want to do is:
- show the admin user what the object is connected to(the list of data connected to the object) so the admin knows exactly what he is deleting
- then let the admin delete the data if he is sure the data can be deleted from the database. Do you know a good solution for my problem?
|
|
|
|
|
I would build a dedicated admin delete view where the admin can see a list of items flagged for delete, possibly a treeview if they are deep enough and allow the admin to confirm delete. Chasing down the dependency tree will be a PITA unless you know the structure intimately, the diagram should help.
It does seem a lot of work to get rid of junk when you already filter it out with a flag, I presume your flag already does the cascade thing (unless you cheated and only did the top levels). Why not change the flag to a datetime and delete anything flagged older than n months/years in a purge function.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi. So I know I've done this before but I can't get it to work despite every example on the internet telling me it should... I'm trying to call a procedure from code. I keep getting an "Invalid SQL statement" exception. Any ideas why?
create procedure DoSomething
as
begin
end DoSomething;
I try to execute from code like so:
Using conn = New OracleConnection(MyConnectionString)
Using cmd = conn.CreateCommand()
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "DoSomething"
conn.Open()
cmd.ExecuteNonQuery()
conn.Close()
End Using
End Using
At which point I get the aforementioned exception.
What am I doing wrong?
Sam.
|
|
|
|
|
Does calling your SP through Oracle directly work? Can you narrow it down to whether the error is in the Oracle syntax or in the C# calling it?
There are only 10 types of people in the world, those who understand binary and those who don't.
|
|
|
|
|
Yes, it works fine if I call it from an oracle client. This is only a problem trying to execute it from code.
|
|
|
|
|
When I googled the error and C# I saw some posts where people had to add some settings to their commands. I suggest googling until you find the right thing.
There are only 10 types of people in the world, those who understand binary and those who don't.
|
|
|
|
|
You're getting error due to the statement(s) of stored procedure. Always share the relevant code/script in your question for qucik answer. Check this
ORA-00900: invalid SQL statement tips[^]
|
|
|
|
|
No, the procedure works correctly when I execute it from SQLLite.
|
|
|
|
|
Still you didn't share the script of stored procedure.
Alright, possibly the issue is related to parameters then. Have you passed values for stored procedure parameters correctly? You should care datatime parameters particularly, you should pass correctly using date/time values instead of string value. Also the format. Did you check the link in my answer?
|
|
|
|
|
I didn't share the scrip because it works when it's execute on the server. The code I provided has identical signatures. There are no parameters passed into the procedure. Perhaps that is a problem - that since I didn't pass any parameters the Oracle library doesn't want to execute the command even though there are no parameters for the procedure.
|
|
|
|
|
Here is the actual code, though I can't imagine why this would help:
create or replace
procedure UpdateUnitType as
cnt number(10,0):=0;
begin
for u in (select * from unit where type_cd = 1)
loop
cnt := 0;
select count(billet_id)
into cnt
from billet
where fiscal_year = u.fiscal_year
and uic = u.uic;
if cnt > 0 then
update unit
set type_cd = 2
where unit_id = u.unit_id;
end if;
end loop;
end updateunittype;
|
|
|
|
|
It's been long time ....Oracle, try different syntax like below
cmd.CommandText = "{CALL StoredprocedureName()}"OR
cmd.CommandText = "BEGIN StoredprocedureName(); END;"
And are you using oracle "package" by any chance?
Because the stored procedure missing the declare keyword for cnt. Possbily you have declared the cnt inside the package & executed the stored procedure to see the output. It would work.
So if you have the stored procedure inside the package, you should mention(prefix) the package before the stored procedure in your code like below.
cmd.CommandText = "PackageName.ProcedureName"
•I was using a older JDBC driver so after fixing the above error I was getting
org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute query; SQL [{ call PROC_GET_NEW_CHECK_NUMBER(?,?,?) }]; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query
at com.ibm.ejs.jms.listener.ServerSessionDispatcher.dispatch(ServerSessionDispatcher.java:44)
at com.ibm.ejs.container.MDBWrapper.onMessage(MDBWrapper.java:100)
java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement
To fix this I upgraded my JDBC driver to the correct version that matches the Oracle installation. So in my case I used ojdbc6_11g_11_2_0_1.jar since my Oracle instance :Oracle 11.2.0.1.0
So you should use/reference the proper driver(dll) in your project. Verify both versions of assemblies
Oracle/PLSQL: ORA-00900[^]
|
|
|
|
|
hopefully I am missing something but are you sure you are using SQLite? as this link shows that it doesn't support stored procedures.
http://www.sqlite.org/whentouse.html[^]
Quote: n order to achieve simplicity, SQLite has had to sacrifice other characteristics that some people find useful, such as high concurrency, fine-grained access control, a rich set of built-in functions, stored procedures, esoteric SQL language features, XML and/or Java extensions, tera- or peta-byte scalability, and so forth.
Every day, thousands of innocent plants are killed by vegetarians.
Help end the violence EAT BACON
|
|
|
|
|
You're right. Actually, I'm using Oracle's "SQL Developer". It was just easier to say SQLLite.
|
|
|
|
|
Well, what the ORA-00900 says is that there is something wrong with the CommandText, I'm afraid you will have to share it if you want any useful feedback
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
