| 9/5/2006 3:21:15 PM |
The SQLServer database has the FETCH Transact.
FETCH
Retrieves a specific row from a Transact-SQL server cursor.
I am using the FETCH Transact in some Stored Procedures, like :
(...)
DECLARE max_pageid CURSOR FOR SELECT MAX(PageId) FROM page_Content
OPEN max_pageid
FETCH NEXT FROM max_pageid INTO @PageId
IF @@FETCH_Status=0
SET @PageId=@PageId+1
ELSE
SET @PageId=1
(...)
The main idea of using the ORMapper is to make my aplication work with several databases like SQLServer, MySQL and others.
Is there any alterantive or option to do that in the ObjectQuery or OpathQuery ?
Thanks |
| 9/5/2006 6:26:06 PM |
Cursors are something that you should always avoid if possible, in any database. So if there is a way to use a regular sql statement then its going to be far better. In your example, it appears that the cursor isn't even necessary since the sql SELECT MAX() will always just return a single row. Maybe there's more you've left out, but if that is it then its not necessary to use a cursor at all in this case, although maybe you need to check for the case where there is no recordset at all. Anyhow, if you must use a cursor, or any stored proc, then the ORMapper does give you ways to work with them, typically with the SelectProcedure object instead of the ObjectQuery object, although you can also use ExecuteCommand if its not going to return anything. But keep in mind that once you go down that path then you're not going to be able to seamlessly support multiple databases, so once again straight sql is better, although for a different reason this time.
Thanks, Paul Wilson
|
| 9/5/2006 8:10:19 PM |
Hi Paul,
You are right and thanks for your good support. The main idea is to migrate the only two Stored Procedures that use cursors but I have some dificulties to migrate them.
[First SP]
There is in the page_Content table several pages with the same pageId... the only difference is in each PageVersion they have (we can have several pages with same pageID with diferents PageVersion). The idea here is to get the LastVersion of that page (pageId) but I need to know the MAX(PageVersion) of that particulary page (pageID).
(.....)
DECLARE @maxversion int
DECLARE page_Content CURSOR FOR SELECT MAX(PageVersion) FROM page_Content WHERE PageId=@PageId
OPEN page_Content
FETCH NEXT FROM page_Content INTO @maxversion
IF @@FETCH_STATUS=0
SELECT * FROM page_Content
WHERE PageId=@PageId AND PageVersion=@maxversion
ELSE
SELECT * FROM page_Content
WHERE PageId=-1
GO
[Second SP]
Here when I INSERT one new page, I need to get the last pageId and add 1 value (PageId+1).
(...)
DECLARE max_pageid CURSOR FOR SELECT MAX(PageId) FROM page_Content
OPEN max_pageid
FETCH NEXT FROM max_pageid INTO @PageId
IF @@FETCH_Status=0
SET @PageId=@PageId+1
ELSE
SET @PageId=1
INSERT INTO page_Content (PageId, PageVersion, bla.. bla..bla
) VALUES (@PageId, @PageVersion, bla.. bla..bla
I will avoid all stored procedures because like I have said the main idea of using the ORMapper is to make my aplication work with several databases like SQLServer, MySQL and others. I will also avoid using Stored Procedures :)
Perhaups they are easy to migrate (I am not a DBA)... can you give me some tips to migrate them to simple SQL Statements ?
After migrate them I will delete (all SP) and migrate to ORMapper using ObjectQuery or OpathQuery :P
Thanks again for your support and for your greate tool !! |
| 9/6/2006 8:20:36 AM |
After some tests I think I have found the soluction ( using your initial tip) it was easy to migrate.
I get this :
[First SP]
DECLARE @maxversion int
SET @maxversion = (SELECT MAX(PageVersion) FROM page_Content WHERE PageId=@PageId)
SELECT * FROM page_Content
WHERE PageId=@PageId AND PageVersion=@maxversion
[Second SP]
(...)
DECLARE @maxPageId int
SET @maxPageId = (SELECT MAX(PageId) FROM page_Content) +1
INSERT INTO page_Content (PageId, PageVersion, bla.. bla..bla
) VALUES (@maxPageId, @PageVersion, bla.. bla..bla
(...)
Question:
Base in your own experience, can you point me the better solution to migrate this to WilsonORMapper ? Using ObjectQuery or OpathQuery ?
For example the first SP, I have to do two separated queries or there is any “tip” to join all in one ?
|
| 9/6/2006 8:56:58 AM |
[First approached] :
DECLARE @maxversion int
SET @maxversion = (SELECT MAX(PageVersion) FROM page_Content WHERE PageId=@PageId)
SELECT * FROM page_Content
WHERE PageId=@PageId AND PageVersion=@maxversion
For example, I can also do some simplification like this :
[Second approached] :
SELECT * FROM page_Content
WHERE PageId=@PageId AND PageVersion=(SELECT MAX(PageVersion) FROM page_Content WHERE PageId=@PageId)
Like I have said I don’t want to use any Stored Procedure !! The main idea of using the ORMapper is to make my aplication work with several databases.
If I made the [First approached] I think I have to build two queries in the ORMapper. One to get the (@maxversion) value and other to pass that result to the SELECT statement.
If I made the [Second approached], and I think is the better one, I don’t know how to reproduce this in the WilsonORMapper. Can I do this using OpathQuery ? I don’t have any idea ?!?!
Thanks and Sorry so many replys |
| 9/6/2006 1:05:56 PM |
Hi Paul,
Yeap, that sql puzzle works just fine :P
Thanks a lot !!
|