|
This is inappropriate, you should be writing an article for CP rather than peppering links to your web/blog.
The only reason it is still here is that it is borderline.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
|
Hello,
I use Microsoft's Oracle client provider.
I have 2 Oracle stored procedures that do the same thing and have only different names:
SP1:
create or replace procedure GetScalar(last_name IN PERSON.LastName%TYPE, row_count OUT INTEGER)
as
begin
SELECT COUNT(*) INTO row_count FROM PERSON
WHERE LASTNAME = last_name;
end GetScalar;
SP2:
create or replace procedure Get_Bool(last_name IN PERSON.LastName%TYPE, r_count OUT INTEGER)
as
begin
SELECT COUNT(*) INTO r_count FROM PERSON
WHERE LASTNAME = last_name;
end Get_Bool;
I use the same C# code for both.
When I use stored procedure SP1 after this code was called:
OracleCommandBuilder.DeriveParameters(objCmd);
int n = objCmd.Parameters.Count;
n is 2, which is ok.
But, when I use stored procedure SP2:
n is 0, which is not ok.
What should I do yo make it work?
When I call both store procedures inside Oracle enviroment both work fine.
|
|
|
|
|
Would help if code tags were used but it appears that the stored proc signature, excluding identifiers, is the same.
If that is the case then you have a faulty assumption.
You are assuming that a difference in identifies makes a difference. Excluding key word usage (which I do not see any) that is not possible.
Since it isn't possible it suggests that you need to look for a difference source of the problem.
So possible reasons are the following. This is not a complete list.
- The proc in your C# code is not named correctly.
- The proc does not exist in the database, or it is an older version with no parameters.
- You are pointing at a different database.
|
|
|
|
|
Roll your own -- don't depend on automagic tools.
|
|
|
|
|
random question retrival from database
|
|
|
|
|
You could always try asking a question.
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum
me, me, me
|
|
|
|
|
the OP does not have a question, he wants to get a question, a random one.
|
|
|
|
|
I sets them up, he knocks them down...
Hey: fix cp vanity: I'm sure you can't have anything better to do
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum
me, me, me
|
|
|
|
|
|
How was your mushrooms yesterday?
"When did ignorance become a point of view" - Dilbert
|
|
|
|
|
I think you just answered his question. As this was a very random question  .
|
|
|
|
|
What can ORDER BY NewID() do?
Wait, this isnt Jeopardy? Darn.
|
|
|
|
|
Why are you getting it in back-end move for front-end it will works.
|
|
|
|
|
I don't completely agree with you, pending on the size and design structure of the question system. If you have multiple categories each containing a couple of thousand questions. Totalling in the millions then it would be less then optimal to do it in the application. Or a scenario where the same logic would have to reside in multiple applications.
So doing it database or application side really depends on the setup of the entire application.
|
|
|
|
|
Yes This is your point of view and I 100 % agree with you.
|
|
|
|
|
jeyaranjani wrote: random question retrival from database
Don't try to post like this always post with fact question
Make a connection and get all data in your dataset and then use Random keyword and get all data from dataset in randomly way.
|
|
|
|
|
Hi
I am trying to convert an Oracle query to SQL. But i am totally confused thinking how to proceed. For some of the oracle packages , am not able to find an equivalent SQL function
Here is my Oracle query
cursor c_fields(c_xml XMLTYPE) is
select DBMS_XMLGEN.convert(extract(value(x), '//field/@name').getstringval(),1) FLD_NAME,
extract(value(x), '//field/@resultCol').getstringval() RESULT_COL,
extract(value(x), '//field/fieldConfiguration/@isAttachmentAllowed').getnumberval() AllOW_ATTACHMENT,
extract(value(x), '//field/@key').getnumberval() FLD_ID
from TABLE(XMLSEQUENCE(EXTRACT(c_xml, '//table/field'))) x;
I need to convert this to SQL query. In SQL we dont have parameterized cursor concepts. However to some extent we can achieve the functionality by using "WHERE" condition. But in this query, am not able to trace out how can i write the equivalent SQL functionality since "FROM" part itself is using the parameterized cursor.
Please help.
Thanks
Arudya
|
|
|
|
|
My first choice would be to refactor the database unless that code is touched very seldom. That sort of meta data solution is very inefficient.
But after that I would use dynamic sql. You create a varchar with equivalent sql in it, specifically the extracted table name. I would probably extract the rest as well.
Then you use 'execute' to execute the varchar.
|
|
|
|
|
Hi, I am wondering if there is a way of proofing a stored procedure in terms of the datetime localisation. For example, in the C# we may use the web.config or machine.config to set the application's localised date settings:
globalization uiCulture="en" culture="en-GB">
This helps when we do a date comparison in a static class of ours to return a certain rating factor.
Is there an equivalent way of us checking whether or not the date we pass into the stored procedure is in e.g. UK format and will be compared against our dates in the UK format too? We have 2 datetime columns, DateEffectiveFrom and DateEffectiveTo in our table and we use those dates to compare with the date passed in to return the relevant data.
Thanks
|
|
|
|
|
Just to clarify one thing: if your columns are datetime columns, why do you need to worry about date formats? You can just compare one datetime with another.
Is the parameter to the stored proc being passed as a string/varchar and you need to figure out what format it is in order to do the comparison?
|
|
|
|
|
Hi David, the parameter will be a datetime. I need to find which date range this falls in.
For example, I pass in the date 11/11/2010 and the columns are:
ID StartEffDate EndEffDate Rate
1 01/01/2000 31/12/2003 1.05
2 01/01/2004 31/03/2011 1.15
3 01/04/2011 01/01/2012 1.25
I need to return the appropriate Rate, e.g. in this case 1.15
|
|
|
|
|
date formatting is a human thing, it does not relate to databases at all, unless you make a mistake. What you should do is define datetime fields, and pass DateTime SQLParameters, that avoids all datetime formatting issues on the DB side; obviously you still have to deal with the datetime formatting in the GUI, however that is the only place it should matter (and there you should follow the system's regional settings).
|
|
|
|
|
Luc's answer is still valid as the correct way of doing things, but if you want to pursue the hard way you can start looking here[^] for SQL Server.
"When did ignorance become a point of view" - Dilbert
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
