|
Generally speaking, a query does not return a table. More often, it returns a rowset*, which we tend to visualise as a table. You can quite happily use a join inside a query, and you'll get back one rowset.
On a stylistic note, I tend to prefer table.column1,table.column2 over table.*, since that way if someone modifies the schema by adding columns, existing code doesn't fall flat on it's backside.
Steve S
*Yes, I know that you can execute statements that don't return rowsets...
|
|
|
|
|
When I try:
INSERT INTO Product (ID, name, date) VALUES (123, NULL, NULL)
It works fine when I use NULL to 'name' and 'date' in the INSERT statement.
However, when I use a variable:
INSERT INTO Product (ID, name, date) VALUES (123, @myName, NULL)
And assign @myName.Value = null to the variable, I get an error message when I execute this statement.
(For example: myInsert.Parameters["@myName"].Value = null;)
My question is: If I use @myName in the INSERT statement, how I can assign NULL to it??
Thank you!!
|
|
|
|
|
You have to use DBNull.Value instead of null, e.g.
myInsert.Parameters["@myName"].Value = DBNull.Value
|
|
|
|
|
I use Win2000 Adv. Server for my server...
I want to configure SMTP for my custom send mail, so how do I do?;)
|
|
|
|
|
By default, IIS has an SMTP service, which can be configured by going to Internet Services Manager snapin and editing the PropertySheet for 'Default SMTP Virtual Server'
IIS will try to deliver the mails using its SMTP service by using its preconfigured DNS. You can override it by asking your IIS to relay all mails to SmartHost etc.
Did this help you?
Deepak Kumar Vasudevan
Personal Web: http://www24.brinkster.com/lavanyadeepak/default.asp
I Blog At: http://deepak.blogdrive.com/
|
|
|
|
|
Can you help me with exactly (will good better if it've a example;))? because I don't know how I do for exactly? 
|
|
|
|
|
|
Hi,
I have the following phenomenon: I have a database with several tables and created a union with a Like-Operator in the WHERE-Statement, like this:
<br />
SELECT Key, Value FROM MyTable WHERE Value Like 'RED*'<br />
UNION SELECT Key, Value FROM MySecondTable WHERE Value Like 'RED*';<br />
Now I'm trying to count the items in this view via ODBC, here are the main parts of my CRecordset-derived class doing it:
<br />
CHelperRecordset::CHelperRecordset(CDatabase* pdb)<br />
: CRecordset(pdb)<br />
{<br />
m_lFetchedCount = 0;<br />
m_nFields = 1;<br />
m_nDefaultType = forwardOnly;<br />
}<br />
<br />
void CHelperRecordset::DoFieldExchange(CFieldExchange* pFX)<br />
{<br />
pFX->SetFieldType(CFieldExchange::outputColumn);<br />
RFX_Long(pFX, _T("ZAEHLER"), m_lFetchedCount);<br />
}<br />
<br />
long CHelperRecordset::GetCount(const CString& strTableName, const CString& strIndexName) <br />
{<br />
CString strSQL = _T("");<br />
strSQL.Format("Select Count(%s) As ZAEHLER From %s", strIndexName, strTableName);<br />
Open(CRecordset::forwardOnly, strSQL);<br />
Close();<br />
return m_lFetchedCount;<br />
}<br />
If I'm executing this on my union I will get a count of 0, although all items are visible when I open the union in MS Access. There seems to be nothing wrong with the CHelperRecordset class, when the Like-operator is replaced by =, everythings works fine:
<br />
SELECT Key, Value FROM MyTable WHERE Value='RED'<br />
UNION SELECT Key, Value FROM MySecondTable WHERE Value='RED';<br />
And if I'm not using a union but a view only everything is fine as well, even with the Like-operator:
<br />
SELECT Key, Value FROM MyTable WHERE Value Like 'RED*';<br />
In these cases the correct count is retrieved.
On the PC of my colleague this problem doesn't occur. He is using WinNT4 SP6, Office 2000 (no SR), ODBC-Driver Version 3.520, Access-ODBC-Driver has the 4.00.5303.
I'm using WindowsXP SP1, Office 2000 (SR1, upgrading to SR3 didn't help), ODBC-Driver Version 3.525, Access-Driver is 4.00.6019.
Does anybody know whether there is some obscure option or setting I have to set somewhere or whether this is a MS Bug or whether I am doing something completely wrong? I could easily send the small test application and database with which I reproduced the problem along if anybody's interested.
As I'm no expert in databases, ODBC, SQL and stuff and our expert has left us I'm completely at loss. I wanted to have a look at some completely different problem of our application and cannot do it, because the application fails to see the database items on my pc in the first place.
Any hint or idea would be welcome. Thank you very much.
mexicanchili
|
|
|
|
|
Finally I found it in the MSDN knowledge base.
There are problems with the Jet engine version and MS Access, as stated in this article.
Like won't work with an asteriks '*': KB-Article
When using '%' instead of '*' my test gives the correct count via ODBC. BUT: when opening the view in Access, it is empty.
*sigh*
mexicanchili
|
|
|
|
|
hi all...
ok, ill get to the nuts and bolts of the problem...
i keep getting a C2259 error ('class' : cannot instantiate abstract class due to following members: )
now the class it is complaining about is one that instantiates IDataParameterCollection and ArrayList... specifically:
public __gc class NewSQLParameterCollection: public ArrayList, public IDataParameterCollection{};<br />
and the function it is complaining about is this one...
bool Contains(String *p)<br />
{<br />
if (this->IndexOf(p) >= 0)<br />
return true;<br />
else<br />
return false;<br />
}<br />
(umm, and before you say that i am missing the NewSQLParameterCollection:: indentifier, i should mention that i am inlining the call within the class header... )
now the problem occurs when in a subsequent class i call the following
function test::sometest()<br />
{<br />
NewSQLParameterCollection *p = new NewSQLParameterCollection(S"Name", Object);<br />
}<br />
it says that it can't find the function (as in bool Contains(...)) above, but as you can see, it's there...
any ideas?
this is for an open source C++.NET MySQL driver i am writing, so any help would be GREATLY appreciated...
cheers and thanks
nik
Nik Vogiatzis
PhD Candidate: University of South Australia
+++++++++++++++++++++++++++
Developing new generation Traffic Micro-simulation Tools for Traffic Engineers
em: nikolaos.vogiatzis@unisa.edu.au
|
|
|
|
|
|
Hey all, we've got a monster proc that does things like the following
-- create tmp table
-- for all fields in permanent table
-- for all parties involved
-- select somebig calculation
-- update tmp table
-- next party
-- update permanent table w/ tmp value
-- next field
It takes a little over 3000 lines of TSQL to get the whole thing done. What really sucks is that I need to call this from a web page (*.aspx) and allow our client to view the results of this procedure.
Eventually this will be a moot issue because it will run as a nightly job in Sql Server but while they test that it works I need to make sure that it runs smoothly from an aspx page.
Here are my questions:
1. What is the maximum timeout I can apply to both the SqlCommand and SqlConnection objects?
2. Can I run this in a separate process and do a callback to the aspx page to display results? Airline websites tend to do this quite a bit when you're looking for a flight. I'm thinking of using an asynchronous delegate but I'd have to guess how long it would take before redirecting the user to the results...
3. Any ideas on how I can get this to run smoothly and not worry about timeouts?
Thanks much,
*->>Always working on my game, teach me
*->>something new.
cout << "dav1d\n";
|
|
|
|
|
I know this is not what you have asked but:
Have you tried removing the need for a cursor? Cursors are "very" rarely needed. It has been my experience that changing cursors to properly indexed set operations removes any significant time-out issues.
|
|
|
|
|
I know that the TOP operator can't be within the where clause, but is there a way to achieve this behavior in a single select call? For example, if I want to select all books where publisher = 3 or 5; but I want to limit the results to only getting the top 5 for publisher 3 and the top 10 for publisher 5. Is this achievable on a single select call?
Something along the lines of:
select * from books
where (publisher = 3 and top 5) or (publisher = 5 and top 10)
Thanks in advance if anyone knows.
Ron Ward
|
|
|
|
|
You could use a union...
SELECT TOP 5 FROM books WHERE publisher = 3
UNION
SELECT TOP 5 FROM books WHERE publisher = 5
|
|
|
|
|
That's a really good idea. Thanks for the quick response.
|
|
|
|
|
hi all
just wonder if anyone have done any relatively complex project in oracle database. it seems hard to make an app for oracle database using ADO.NET dataset. in my app, I bind all the fields to all kind of controls and there are parent/child tables. so I create datarelations and all that for the datatable in a same dataset.
Q1. when inserting new parent/child records, oracle database doesn't support scop_indentity(),
how does one get the real id from the database and update the child records before they get inserted into the database?
Q2. when adding new child records for existing parent entry, does the datarelation automatically add the parent foreign key into the newly added child record? it seems that's not the case, since it doesn't work for me when I try to add new child record.
I posted a similar thread in the ms newsgroup, one of the ms guy showed me some of the sample how things are done in sql server 2000 and access, but not much with oracle. he also mentioned the use of sequence. get the some-sequence.currval to get the real id, but that's not connection oriented, because multiple users might insert record at the same time, how can one guarantee it is the correct id for the record.
anyone have any idea?
thx
kevin
|
|
|
|
|
Oracle doesn't have autonumeric fields. You can achive a similar behavior with sequences.
Sequences guarantee that the number generated is unique.
Add a trigger to your table that inserts the sequence value on the table.
Free your mind...
|
|
|
|
|
sorry, Gillermo
I am way pass that level, in fact select max(someColumn) + 1 will do. it seems like you misunderstand my question.
kevin;)
|
|
|
|
|
I hope you're doing some locking, otherwise you'll get bitten by concurrency bugs.
Example: Say the current maximum is n. Client A's connection performs SELECT MAX() + 1, then, before client A's connection performs an INSERT, client B's connection also does SELECT MAX() + 1. Both clients now think that their insert should be numbered n + 1. The second client to INSERT will either cause a unique constraint violation if you've defined that someColumn should be unique, or you'll get two values.
SELECT MAX() also causes more work for the database. Instead of looking up and updating a single value, it has to seek through the index every time.
(Speaking as someone who's used this technique in SQL Server.)
|
|
|
|
|
Hello,
I am trying to generate some statistics from my SQL Server 2000 database. I have two UDFs, one which determines which days have complete data for a range of dates, and the other generates the statistics for a particular day.
I am fustrated because SQL Server doesn't seem to want to allow me to use these functions as I would like.
I even dumped the contents of the dates function into a table variable so I do something like:
DECLARE @dates TABLE([date] smalldatetime)
INSERT INTO @dates
SELECT [date] FROM dbo.dates_with_complete_data(@start_date, @end_date)
The above works fine, but if I try an use that as input to the other function I get errors.
SELECT calc.*
FROM @dates as fd
INNER JOIN (dbo.calculation(fd.[date])) as calc
ON cc.[date] = fd.[date]
Can anyone shed some light on how I might tackle this? If I remain stuck I might have to do the unthinkable and create my first ever cursor! 
--Colin Mackay--
EuroCPian Spring 2004 Get Together[^]
"You can have everything in life you want if you will just help enough other people get what they want." --Zig Ziglar
|
|
|
|
|
I suspect the problem is in the inner join statement. Try this:
SELECT calc.*
FROM @dates as fd
INNER JOIN (
SELECT *
FROM (dbo.calculation(fd.[date]))) as calc
ON cc.[date] = fd.[date]
Barbara 
|
|
|
|
|
Thanks for your response, however that doesn't work either.
I think it may because of the circular nature of what I am trying to do. The calculation does not return any rows unless it has a date, the join doesn't know what dates match because the calculation has not returned any rows, so no dates can be plugged into the function.
The error message I get is "Incorrect syntax near 'fd'." giving the line number of where I call the UDF.
--Colin Mackay--
EuroCPian Spring 2004 Get Together[^]
"You can have everything in life you want if you will just help enough other people get what they want." --Zig Ziglar
|
|
|
|
|
oh - ok I see the problem. You can't use a variable in the FROM clause. You may need to put the function in place of @dates or use a derived set.
Barbara
|
|
|
|
|
The only advice I can give is to dump the dbo.calculation function, and compute the various parts in the SELECT clause instead, probably using separate functions.
I believe SQL Server is able to look inside a function when compiling a query plan, so it may be able to perform whatever operations you have specified in parallel anyway.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
