Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

ODBC and QA.

Posted on 2006-06-09
13
Medium Priority
?
1,064 Views
Last Modified: 2012-06-21
Hi.

I have the following SQL Statement ...

SELECT 'CONTRACTS' AS [Database], POH_ORDER_NUMBR, POH_CONTRACT, POH_ACCOUNT FROM CONTRACTS.DBO.POP_HEADER WHERE POH_MU_LOCK=1;

In Query Analyser this returns the correct results.

If I use this query via ODBC, the SQL Profiler reveals the following ...

declare @P1 int
declare @P3 int
declare @P4 int
declare @P5 int
set @P1=0
set @P3=2
set @P4=8193
set @P5=0
exec sp_cursoropen @P1 output, N'SELECT ''CONTRACTS'' AS [Database], POH_ORDER_NUMBR, POH_CONTRACT, POH_ACCOUNT FROM CONTRACTS.DBO.POP_HEADER WHERE POH_MU_LOCK=1;', @P3 output, @P4 output, @P5 output
select @P1, @P3, @P4, @P5

All looks OK.

But the application gets no results.

So.

If I place the above in QA, I get the following results ...

1,330,634,8      2.00      1.00      -1.00

Now the important one is the -1.00

This is the number of rows in the response. -1 means what?

I tried this in QA

declare @P1 int
declare @P3 int
declare @P4 int
declare @P5 int
set @P1=0
set @P3=2
set @P4=8193
set @P5=0
set nocount off
exec sp_cursoropen @P1 output, N'SELECT POH_ORDER_NUMBR, POH_CONTRACT, POH_ACCOUNT FROM CONTRACTS.DBO.POP_HEADER WHERE POH_MU_LOCK=1;', @P3 output, @P4 output, @P5 output
select @P1, @P3, @P4, @P5

exec sp_cursorfetch @P1, 2, 1, 1

and I get a valid row back.

What am I missing that is returning -1 for the number of rows?

Regards,

Richard Quadling.
0
Comment
Question by:Richard Quadling
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
13 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16869607
please try this:

SET NOCOUNT OFF
SELECT 'CONTRACTS' AS [Database], POH_ORDER_NUMBR, POH_CONTRACT, POH_ACCOUNT FROM CONTRACTS.DBO.POP_HEADER WHERE POH_MU_LOCK=1
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16869610
resp:

SET NOCOUNT ON
SELECT 'CONTRACTS' AS [Database], POH_ORDER_NUMBR, POH_CONTRACT, POH_ACCOUNT FROM CONTRACTS.DBO.POP_HEADER WHERE POH_MU_LOCK=1
0
 
LVL 40

Author Comment

by:Richard Quadling
ID: 16869635
In QA, they both return valid results.
In my ODBC both return NO results.


0
How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

 
LVL 40

Author Comment

by:Richard Quadling
ID: 16869677
What makes this really bizarre is ...

declare @P1 int
declare @P3 int
declare @P4 int
declare @P5 int
set @P1=0
set @P3=2
set @P4=8193
set @P5=0
set nocount off
exec sp_cursoropen @P1 output, N'SELECT DISTINCT SUSORT, COUNT(*) AS ServiceProviders FROM Contracts.dbo.PL_ACCOUNTS WHERE SU_ON_STOP <> 1 AND SUSORT > '''' GROUP BY SUSORT ORDER BY SUSORT', @P3 output, @P4 output, @P5 output
select @P1, @P3, @P4, @P5

exec sp_cursorfetch @P1, 2, 1, 1

returns ...

1,289,928,7      8.00      1.00      1.00


and a valid row.

Hmmm.

I wonder if my interpretation of that last column is correct.

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16869707
never worked that way, so the only thing I can suggest is to use OLEDB Provider instead of ODBC...
0
 
LVL 40

Author Comment

by:Richard Quadling
ID: 16869739
I'm just trying a snapshot build of the system.
0
 
LVL 40

Accepted Solution

by:
Richard Quadling earned 0 total points
ID: 16870135
ARGH!!!!

It seems that the ODBC mechanism won't return the number of rows unless there is an order by in the SQL statement.

I was testing 1 statement from a UNION and the UNION has its own ORDER BY clause.

Added the ORDER BY to my 1 SQL line and hey f*cking presto!

ARGH!!!

Thanks for, erm, well, um, listening?
0
 
LVL 40

Author Comment

by:Richard Quadling
ID: 16870246
Not sure what to do with this question now.

The answer had nothing to do with the problem (as such).
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16870397
you should request the q to be paqued with points refunded
0
 
LVL 40

Author Comment

by:Richard Quadling
ID: 16870897
Ok.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16934814
Note that, as I was the only one contributing, I reduced the waiting time for closing to 0 also :-)
CHeers
0
 
LVL 40

Author Comment

by:Richard Quadling
ID: 16940221
Thanks.
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question