|
Wow, I'm impressed. I had no idea it could be accomplished in so few of steps. Of course, I'm still trying to decode some of the "ADD CONSTRAINT" and REFERENCES language but I get the basic gist of what you're saying. I also had no idea that you could do the self referencing and thought, at minimum, it would require two tables. Very cool.
Thanks so much !!
|
|
|
|
|
I have a stored procedure defined thusly -- actual variable, database, and table names have been altered for NDA reasons:
CREATE PROCEDURE [dbo].[CountUniqueBirthdays]
@PersonID bigint
AS
BEGIN
SET NOCOUNT ON;
DECLARE @CalendarDaysToSearch int
SET @CalendarDaysToSearch = 90
SELECT COUNT(DISTINCT o.Birthdays)
FROM
[MYDATABASE].[dbo].[PersonInfo] n,
[MYDATABASE].[dbo].[PersonInfo] o
WHERE
n.PersonID = @PersonID AND
--n.PersonID <> o.PersonID AND
-- all leads with...
n.KeyPerson=o.KeyPersonAND
-- within X days of current lead
o.CalendarDate between dateadd(dd, (-1 * @CalendarDaysToSearch ), n.CalendarDate ) and dateadd(dd, 0, n.CalendarDate )
END
For the sake of argument, assume this stored procedure is written correctly and is known to work. Now, I opened up VS2005 (my boss wants me to use it) and in the Data Sources window, I did the whole Add New Data Source/Data Source Configuration Wizard yada yada and added a TableAdapter to my DataSet for PersonInfo (not the real table name again).
I configured the TableAdapterto do a SELECT PersonId FROM PersonInfo and then also to call the Stored Procedure, CountUniqueBirthdays, above. ok, so i write the code to grab the records containing only the PersonIDcolumn and then I iterate over the column, passing each ID into the stored procedure one by one to check if the number of distinct persons with birthdays in a certain 90-day period is bigger than 1 -- AND DON'T ASK ME WHY DID I NOT JUST USE A CURSOR, CURSORS ARE SLOW AND WE HATE THEM, as below.
You know how you can open .xsd files in the Dataset Designer in VS2005? Well, I did, right-clicked the PersonInfo TableAdapter, clicked Add > New Query... and in the Add New Query Wizard I picked Existing stored procedure, and then configured CountUniqueBirthdays as the stored procedure to use.
<br />
try<br />
{<br />
Console.WriteLine("Querying the MYDATABASE.dbo.PersonInfo table...Please wait.");<br />
MYDATABASEDataSetTableAdapters.PersonInfoTableAdapter adapter =<br />
new MYDATABASEDataSetTableAdapters.PersonInfoTableAdapter();<br />
MYDATABASEDataSet.PersonInfoDataTable table =<br />
adapter.GetData();<br />
Console.WriteLine("Finished getting data.");<br />
<br />
Console.WriteLine("Running the CountUniqueBirthdays stored procdure. Searching for return values > 1...");<br />
foreach (MYDATABASEDataSet.PersonInfoRow row in table.Rows)<br />
{<br />
long? personID = row.PersonId;<br />
if (!personID.HasValue)<br />
continue;<br />
<br />
Console.WriteLine("Checking output of CountUniqueBirthdays for PersonId = {0}...", leadID);<br />
<br />
long? value = adapter.CountUniqueBirthdays(leadID);<br />
if (value.HasValue) {<br />
if (value.Value > 1)<br />
{<br />
Console.WriteLine("Value > 1 found for return from LQSCountVendor190Days...for <br />
leadId = {0}",leadID);<br />
break;<br />
}<br />
}<br />
}<br />
}<br />
catch (Exception e)<br />
{<br />
Console.WriteLine(e.Message);<br />
return;<br />
}
The output from the console app is:
<br />
Querying the MYDATABASE.dbo.PersonInfo table...Please wait.<br />
Finished getting data.<br />
Running the CountUniqueBirthdays stored procdure. Searching for return values > 1...<br />
Checking output of CountUniqueBirthdays for PersonId = 8752702...<br />
Specified cast is not valid.
Why? I followed what the IntelliSense had me do when I was typing in the stored procedure call! I don't get it, can somebody please help a newbie at Database programming?
Brian
|
|
|
|
|
The return type for CountUniqueBirthdays may not be long (or long? for that matter). Have you tried [int]? Or just receive it into an [object] variable and use Watch to see what it contains? i.e.
object value = adapter.CountUniqueBirthdays(leadID);
|
|
|
|
|
Thank you for your help. Yes, actually the IntelliSense was off and doing a Build Solution fixed it so that the IntelliSense told me the return type was object, and I was off and running! 
Brian
Sincerely Yours,
Brian Hart
|
|
|
|
|
I have changed the data type of the currencyID and countryID from NUMBER to INTEGER as number is not supported by dotnet.
When I run this query the data_type is still number.
SELECT C.Table_Name ,
C.Column_ID ,
C.Column_Name ,
C.Data_Type,
C.char_length
FROM ALL_TAB_COLUMNS C
WHERE C.TABLE_NAME = 'CURRENCY'
ORDER BY C.column_ID
Any idea how to refresh the ALL_TAB_COLUMNS view or can there be another reason for the change not showing.
Also - any suggestions as to the best Oracle support site. No it's not CP, we are very MS oriented.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
In Oracle, Integer is simply Number(38). So if you create a column as an Integer it's converted to number(38)
"When did ignorance become a point of view" - Dilbert
|
|
|
|
|
When I look at the table in Schema view (toad) I see integer, if I script the table out I get integer, I'm betting if I used a parameter with a data type it would be integer as well. I have now found that if I create a new table the columns do not show up in the ALL_TAB_COLUMN view, seems it might be a permissions thing.
As NUMBER is not supported by dot net this seems very strange! Do all the orm tools convert number to integer/decimal.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I haven't worked in Toad so I can't say.
But an implicit conversion in dotnet is generally making a widening conversion to decimal unless the scale of the number is specifically 0 then it converts to integer.
If you want a different conversion you have to specify it in the parameters of the dataadapter/datareader.
Here's [^]more info on types.
And here http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1619552483055 is some info on the subject from Tom Kyte, who is an institution in the Oracle World
<edit> fixed link, kind of</edit>
"When did ignorance become a point of view" - Dilbert
modified on Monday, November 8, 2010 5:07 AM
|
|
|
|
|
Thanks for the links Jorgen, it looks like the orm will need to do some dancing around the number conversion in dotnet. Tom's link seems to have died but that could be b/c of my net nazi, I'm at work.
It seems like anything with no scale is an integer, precision seems to range from null to 22 for no discernable reason with both null and 22 being valid for an integer. Numbers in between have a scale and therefore decimal I guess.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
It seems to me that the link to Tom's article is destroyed by the hamsters, so here it is as inline code:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1619552483055
"When did ignorance become a point of view" - Dilbert
|
|
|
|
|
i want to insert values (into database table) which are calculated from an expression and is stored in a variable, in INSERT INTO statement, how can i do this?
i m doing this in VC++6 MFC using ODBC
constant values goes perfectally in database<
|
|
|
|
|
What you have tried about to insert value you have no mention whatever,
You can easily insert value into your database table by using
Insert into tablename values(your value depend upon table column data type)
|
|
|
|
|
|
for example here is some part of my code:
string str="Birthday";
i have to insert this str variable using:
INTERT into mytable VALUES(str) ;
how can i insert this???
n i dont know how to use parameters...plz tell me some very easy way to do this...
|
|
|
|
|
It partly depends on what language you host your SQL in, also on which database system you use.
INSERT INTO table (field0, field1 ...) VALUES (@Param0, @Param1 ... )
Then create parameters.
Then, as you loop through the data to insert, set the parameters and execute the statement.
|
|
|
|
|
I let you from the beginning step by step to insert data into table
Create a connection by using SqlConnection class as
SqlConnection con = new SqlConnection("connectionString")
then, you need to create Command object by SqlCommand Class as
string str = INTERT into mytable VALUES('"+str+"');
SqlCommand cmd = new SqlCommand(str,con);
then you need to execute query by using ExecuteNonQuery Method as
cmd.ExecuteNonQuery();
This is easiest way to insert data into table. Hope it will work for you.. 
|
|
|
|
|
thanks a lot RaviRanjankr! for ur concern...but i have tried this is a simple one...actually + sign is not recognized in the query, it gives syntex error...but n e ways thanks a lot my problem has been solved now,thanks
|
|
|
|
|
if you don;t want to use parameter then simple make Connction and Command and Execute your command it will save your data.
|
|
|
|
|
Please don't give people bad advice like this. You've basically just advised the poster that they should leave themselves wide open to a SQL Injection attack, and that's not cool.
|
|
|
|
|
|
|
i can't understand your comment? can you describe it?
|
|
|
|
|
Two Words.
First word, Homer.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
please don't advertise your wares the forums please..
As barmey as a sack of badgers
Dude, if I knew what I was doing in life, I'd be rich, retired, dating a supermodel and laughing at the rest of you from the sidelines.
|
|
|
|
|
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
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
