Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Limiting rows returned by SQLExec( )

Status
Not open for further replies.

foxdev

Programmer
Feb 11, 2000
1,995
US
Scenario: I've established a connection to a Sybase 11.5 database using SQLStringConnect(). Sybase 11.5 does not support the TOP clause. The connection is synchronous.

Goal: I want to specify the maximum number of records to be returned by SQLExec().

Attempts: I've tried adding [tt]set rowcount 5[/tt] in the first SQLExec call, then executing the SQLExec statement that SELECTs the records, with no affect (SET ROWCOUNT probably is batch-specific). I've tried various ways of concatenating the two statements together into a single statement, but the statement fails.

SQLSetProp() does not have a row limit setting, nor does it appear that ODBC has either.

Has anyone solved this?
Robert Bradley
Sr. DBA, some big company
cheap prints and oil paintings:
 
I also tried sending each statement separately using SQLPrepare(), but while the second statement (SELECT) worked, its result set was not limited by the first statement sent (SET ROWCOUNT). Robert Bradley
Sr. DBA, some big company
cheap prints and oil paintings:
 
Hi foxdev,
Try to execute like this,
?SQLEXEC(myHandle,'SET ROWCOUNT 5;SELECT * FROM myTable;SET ROWCOUNT 0')


 
Tried it; no luck...Sybase returns a syntax error. I've also tried inserting GO between the statements, with syntax errors being returned.

This is not a mission-critical need. I'm developing a little tool for viewing Sybase tables, table structures, and data (since Sybase Central does not let you right-click and view data, unlike MS's Enterprise Manager). It would be nice to user-configure the max rows returned so that you don't inadvertantly grab 400,000 rows when you only want a data sample. Robert Bradley
Support operation moo!
Visit the OpCow page to help in this cause
 
1. I presume you've discounted the use of a remote view with the maxrecords property set to 5. And the use of ADO, which also supports a maxrecords property for limiting the amount of rows returned.

2. Bare in mind I have NO experience with Sybase, but have you considered a stored procedure with two input parameters: the sql and the maximum records returned? Jon Hawkins
 
Both your ideas are excellent, Jon, but not appropriate for this particular situation. The tool could be used with any number of unknown, unknowable servers, databases, and tables - the developer would simply establish to whatever server and database they choose, and this is not knowable at design-time. So, the pretty much eliminates using a remote view (unless I built it on the fly, but RVs typically require DBCs, which further complicates things) and stored procedures.

Again, great analysis and solutions, but they won't be appropriate for this particular wide-open application. Robert Bradley
Support operation moo!
Visit the OpCow page to help in this cause
 
What about SELECT TOP 5 ...? I know it is supported by many database systems. Anyway, you can try to do following:
#DEFINE CRLF chr(13)+chr(10)
if SQLEXEC(nH,'SET ROWCOUNT 5'+CRLF+'SELECT ...' + CRLF + 'SET ROWCOUNT 0')<0
if SQLEXEC(nH,'SELECT TOP 5...')<0
&& get the entire rowset - no luck
lnResult = SQLEXEC(nH,'SELECT ...')<0
endif
endif
Vlad Grynchyshyn
vgryn@softserve.lviv.ua
The professional level of programmer could be determined by level of stupidity of his/her bugs
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top