|
I don't think there is a way that is so generic that it could handle all that. Each database vendor uses a slightly different flavour of SQL. For example SQL Server 2000 is based roughly on ANSI SQL-92, whereas SQL Server 2005 is roughly based on ANSI SQL-99. Oracle will have its on proprietary features and ways of doing things, as will DB2 and so on.
You could try looking at an open source product called NHibernate[^] which has its own SQL varient which is then translated in to the specific flavour of SQL required by the back end database.
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
|
Frank Kerrigan wrote: MS Enterprise Libraries offer a Db access layer support across different databases manufactures;
True, but not to the same extent as Hibernate, unless I missed something in the Enterprise Library documentation. Hibernate (and NHibernate) offers the ability to translate SQL into the various flavours. For example. In SQL Server you might have
SELECT TOP 10 * FROM MyTable , while in Oracle the equivalent (if I remember) is something like
SELECT * FROM MyTable LIMIT 10,0 NHibernate permits the developer to develop in their varient which is then translated to any of the supported vendors SQL Language varient with not much more effort that a change in a config file.
As far as I can see the Enterprise Library still requires that the developer write SQL against the varient used by the back end database system - so there will still be a lot of work to do to get it to work nicely with various database systems.
Frank Kerrigan wrote: Look where you want to go not where you don't want to crash.
Excellent advice.  I remember from a skid driving course I did a few years ago something similar.
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
Hi, I want to list all tables names in combobox. Can somebody write a code example. Thank you
|
|
|
|
|
select * from information_schema.tables where table_type='Base table'
-
बुरा जो देखण मै चला, बुरा न िमलया कोय,
जो मन खोजा आपणा तो मुझसे बुरा न कोय।
Translation
|
|
|
|
|
thank You
|
|
|
|
|
I want to write genric select statement in which i want to get the top 10 people fom the tabel based on their total marks column.
I am getting the result when i give
"select top 10 percent fom studentmarks"
But it is not working when i making the statement generic.
"select top @p persent from stiudentmarks"
I am using SQL server 2000
I want to run it on the same (i have already tried it on server 2005 it is working fine)
Please any body help me how to make it work on SQlserver 2000
Varmag
|
|
|
|
|
varmag wrote: I am using SQL server 2000
This feature is not supported on SQL Server 2000. It is a new feature introduced in SQL Server 2005.
varmag wrote: Please any body help me how to make it work on SQlserver 2000
You could use dynamic SQL and construct a string like this:
DECLARE @sql VARCHAR(1000);
SET @sql = 'select top '+CAST(@p AS VARCHAR(10))+' percent fom studentmarks';
EXEC(@sql)
However, be careful as dynamic SQL can be a source of SQL Injection Attacks. If @p is already an integer type then the code should be fine.
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
-- modified at 11:16 Thursday 13th April, 2006
|
|
|
|
|
Colin Angus Mackay wrote: However, be careful as dynamic SQL can be a source of SQL Injection Attacks
What is SqlInjection attack?
"Aim to go where U have never been B4 and Strive to achieve it"
http://groups.yahoo.com/subscribe/dotnetforfreshers
http://himabinduvejella.blogspot.com
|
|
|
|
|
HimaBindu Vejella wrote: What is Sql Injection attack?
See here[^] or here[^]
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
Hi,
In a project I'm working on, we are using the DataAdapter.Fill(table) to get the data from the database. The table we are filling is created before we call the fill. When the DataTable contains a column with a System.Char type I get a FillError.
Here is some code to make it clear.
The field status is a CHAR in the database to.
<br />
' Create a table<br />
Dim table As New System.Data.DataTable("Customer")<br />
<br />
With table .Columns()<br />
.Add("id", GetType(System.Int32));<br />
.Add("name", GetType(System.String));<br />
.Add("status", GetType(System.Char));<br />
End With<br />
<br />
' Other code to create and open the connection / dataadapter<br />
...<br />
<br />
' Create connection / command / dataadapter<br />
Dim cn As New SqlConnection("connectionstring")<br />
cn.Open()<br />
<br />
Dim cm As New SqlCommand("SELECT id, name, status FROM Customer", cn)<br />
cm.CommandType = CommandType.Text<br />
<br />
Dim da As New SqlDataAdapter(cm)<br />
<br />
' I add a handler to catch my exception<br />
AddHandler da.FillError, New FillErrorEventHandler(AddressOf Helper.FillError)<br />
<br />
table.BeginLoadData()<br />
da.Fill(table)<br />
table.EndLoadData()<br />
Because the predefined DataTable contains a column with a Char type I always get the FillError. I catch the error and try to solve it, see the next code snippit.
<br />
Public Shared Sub FillError(ByVal sender As Object, ByVal args As FillErrorEventArgs)<br />
Dim table As System.Data.DataTable<br />
Dim row As System.Data.DataRow<br />
Dim values As Object()<br />
<br />
Dim valueType As System.Type<br />
Dim tableType As System.Type<br />
<br />
' Try to solve exception<br />
If args.Errors.GetType() Is Type.GetType("System.ArgumentException") Then<br />
' There was an error during the fill<br />
table = args.DataTable<br />
values = args.Values<br />
<br />
If args.Values.Length = table.Columns.Count Then<br />
row = table.NewRow<br />
<br />
' Try not to get here, not good for the performance<br />
For i As Integer = 0 To table.Columns.Count - 1<br />
valueType = values(i).GetType<br />
tableType = table.Columns(i).DataType<br />
<br />
If valueType.Equals(tableType) OrElse values(i) Is System.DBNull.Value Then<br />
row(i) = values(i)<br />
Else<br />
row(i) = Convert.ChangeType(values(i), tableType)<br />
End If<br />
Next<br />
<br />
table.Rows.Add(row)<br />
<br />
' Continue without error<br />
args.[Continue] = True<br />
Else<br />
' Throw error<br />
args.[Continue] = False<br />
End If<br />
End If<br />
End Sub<br />
Now you wonder why I post this code. Because it seems I have a solution. But the solution is slow and not correct all the time. That's why I have the following questions.
Is there a better solution? One without catching the FillError event.
Is there a reason why the Fill doesn't work with a Char?
I hope somebody can help or someone else is helped with the code I provided.
Jochen
|
|
|
|
|
I Have Windows XP 2003 on my System( Pentium4 Pc )!
and i give this Error when i want to install Ms Sql Server 2005
***************************************
- SQL Compatibility With Operating System (Error)
* SQL Compatibility With Operating System
Messages
* This SQL Server edition is not supported on the operating system currently running on this computer.
- Minimum Hardware Requirement (Warning)
* Minimum Hardware Requirement
* The current system does not meet the minimum hardware requirements for this SQL Server release.
****************************************
what O.S is require for SQL Server ?
and What HardWare Requirement is Need for SQL Server?
---------------------
Areff Bahrami(KAVEH)
Areff.HB@Gmail.com
---------------------
|
|
|
|
|
First, there's no such thing as Windows XP 2003. It's either Windows XP or Windows Server 2003. If your using XP, you have to have SP2 installed. Windows Server 2003 needs at least SP1.
Hardware requires at least 512MB of RAM, 1GB is recommended.
You can find the system requirements for each SQL Server edition here[^].
Dave Kreskowiak
Microsoft MVP - Visual Basic
|
|
|
|
|
It probably depends on the edition of SQL Server you are trying to install. With SQL Server 2000, I remember the standard edition could not be installed on XP, where you needed to install the personal edition.
Windows XP is one thing (client operating system) and Windows Server 2003 is another (server operating system.) Maybe you need the server.
Luis Alonso Ramos
Intelectix
Chihuahua, Mexico Not much here: My CP Blog!
|
|
|
|
|
Hi,
I am trying to implement a data processing extension for reporting services 2000 because our company have many custmers with the same schema and we don't want to build the reports for everycompany this reports are accessible throug url as well as SOAP. I have read the msdn as well as many fourms but there doesn'seem to be enough for me to understan how I can go about it cause the samples that i have seen are either for file or something else. I am now very sure how i can make this work especially how to implement the IdbCommand and Idatareader interfaces. My reports are already built for one company and all the reports access stored procedures and some commands are in text to fill up parameters in a drop down list format.
any thoughts are welcome
Thank you so much  
jemi
|
|
|
|
|
I want to know whether i can use ado.net in vc++.net(MFC)!I have just used ado.net in vb.net.
thanks!
|
|
|
|
|
http://www.codeproject.com/managedcpp/DataGridVCNET.asp?df=100&forumid=135647&exp=0&select=1383413#xx1383413xx
|
|
|
|
|
Hi!
Can someone please assist me? I want to capture the "Created Date" and an "Update Date" on entries in my DB. I am running SQL 2000.
This would be greatly apriciated
Illegal Operation
Making Computer Software Talk
|
|
|
|
|
SQL Server does not record the timestamps when a record is created or modified. If you want to do this yourself, you'll need to add columns for this, and ensure you update the columns appropriately when you make a modification.
You could create triggers to perform these operations automatically on an INSERT or UPDATE.
You should be aware that the GETDATE() feature is only as accurate as your computer's clock, which typically has a resolution of 15ms, although this can vary depending on the exact hardware. If you're trying to decide whether a given row has changed since you last read it, you may find that you miss updates if multiple updates occur within one clock period. If that's your goal, you might consider using the ROWVERSION column type, which is automatically updated by SQL Server whenever a modification is made to a row. Despite the name, the version is not a sequential number, it's a GUID, and therefore you can't rely on ordering between different version 'numbers'.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Hi,
Can anyone help me with a query i need to run. So far my SQL is as follows:
SELECT * FROM tbl_Orders WHERE orderPaid = 1 AND (DATE BETWEEN 'date1' AND 'date2')
However i need to group the records together by the person placing the order via the combined fields orderTableID and orderUserID, so that i can get a SUM total of the price of the cumulative order orderPrice.
Additional to this i need to be able to get the sub-totals of the different product types eg Drinks, Meal, Deserts. In total their are five different sub totals i need - Bebidas, Vinos, Paellas, Postres, Tapas, Deserts. These are identified by the column orderCategory.
I know this is a lot to ask, but thats why i need help, from someone who's a genius in SQL.
So THANKS in advance for any help!!!
munklefish
:-> <--hoping some charm might persuade!
 <--going for the sympathy vote
|
|
|
|
|
In which case it sounds like you need two queries:
SELECT orderTableID, orderUserID, SUM(orderPrice) AS TotalPrice
FROM tbl_Orders
WHERE orderPaid = 1 AND DATE BETWEEN 'date1' AND 'date2'
GROUP BY orderTableID, orderUserID
SELECT orderCategory, SUM(orderPrice) AS CategoryPrice
FROM tbl_Orders
WHERE orderPaid = 1 AND DATE BETWEEN 'date1' AND 'date2'
GROUP BY orderCategory If you need your second query broken down by table and user as well, put orderTableID and orderUserID in the GROUP BY clause.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Mike,
Thanks for that mate! Sorry i dint reply sooner, but im working on a big project and only just got chance to try them out.
They are both excellent, and need, but not quite what i was after. My fault for getting myself confused and giving the (slightly) wrong info.
Using the code you gave above, but more specfic to my example data in my DB i have:
SELECT orderCategory, SUM(orderPrice) AS CategoryPrice FROM tbl_Orders WHERE orderPaid = true AND (orderTime BETWEEN '2006/04/12 02:35:08' AND '2006/04/12 06:31:29') AND orderTableID = '22' AND orderUserID = 'dong' GROUP BY orderCategory <br /> This shows the subtotals for each of the item types ordered by Mr Dong on Table 22.
However, what i didnt explain very well was that i need to group and display the items by name and total price also. So for example imageine the following:
orderTableID | orderUserID | orderMenuItem | orderCategory | orderPrice
22 Dong Chips Tapas 2.50
22 Dong Chips Tapas 2.50
22 Dong Beer Bebidas 3.00
22 Dong Wine Bebidas 7.00
So the output from the SQl should look like
2 Chips Tapas 5.00
1 Beer Bebidas 3.00
1 Wine Bebidas 7.00
Etc. Hopefully this makes sense. If you could sort me out with SQL for this mate, i would be truly grateful, and post you a pack of M&M's as a thank you!
:-> :->
 <---if only i were but clever!
|
|
|
|
|
Hi,
I installed Oracle Ex (express),created a password for the default system user etc. But now when I click on the "Go to database homepage" button in the start menu under "Oracle Database 10g Express Edition" it opens my browser with the following url: http://127.0.0.1:8080/apex. and this results in a page cannot be displayed error. It's suppost to bring up the management console! What am i doing wrong? there isn't any web file in the setup forder anyhow...
Lastly, when connecting manually in console mode, it doesn't want to accept my password input. Why?
Can anyone please help!
Regards,
Tintin
|
|
|
|
|
Hi,
Please help!
I need to retreive the supplier name from the suppliers table, number of devices sold by the particular supplier by using devices table, no. of failures to the particular supplier by using the failures table.
I can able to retreive the supplier name and the number of failures but i can't able to retreive the number of devices sold by the particular supplier.
I tried the following code
select suppliers.sname, count(*) i, (select count(*) from devices,suppliers where devices.suppid=suppliers.suppid) j from failures,devices,suppliers where devices.suppid=suppliers.suppid and failures.deviceid=devices.deviceid group by suppliers.sname
The problem is the j column will display total no of rows. I need to get the count of devices that are sold by the particular supplier. How can i get the suppliers.suppid value that is used outside?
Hope you understand the problem.
Thanks
|
|
|
|
|
how do i lock a Table in MS SQL SERVER?
krishna
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
