I was looking for a way to query SQL Server via ODBC from
MapBasic and get the number of rows produced by the query.
Apparently the only ways to do this with MapBasic are:
1.) Run the query twice; once to return count(*), and a second
time to retrieve the data.
2.) Download the table into MapInfo and use TableInfo()
Neither of these solutions are great because they involve either
working the server twice as much or incurring the overhead of a
download (which is slow via ODBC.) Also count(*) with 'Group By'
doesn't work in SQL server the way it does in MapInfo. What I
really wanted was the total number of unique items, not a count
of how many of each there are. So I need to use the select
distinct... clause (which doesn't allow count(*)).
The following should illustrate the problem. This fictional SQL
Server table has a column called 'Color_Code' which can contain
values like 'Red', 'Green', 'Blue', etc. Records can be assigned
any string denoting a color. What I want is a count of how many
unique color_codes are in the database.)
This query does the job in SQL Server's Query Analyzer:
Select distinct Color_Code from MyTable
--And this (suggested by David Cautley) gives me the count:
Select @@ROWCOUNT
However, from MapInfo I can't get the count. (Following this is a
sql script to build table in question) Can I get a row count in
one SQL query? Here's a MapBasic example, but I don't know how to
access that row count:
Dim h, hdbc, nCount, nStat As Integer
Dim sColor_code As String
'Connect to Database "Test"
hdbc = SERVER_CONNECT("ODBC", "DLG=1")
'Execute SQL query
h = SERVER_EXECUTE (hdbc,
"Select Distinct Color_Code From MyTable; Select @@ROWCOUNT")
Server h Bind Column 1 To sColor_code, nStat
Server h Fetch First
Do While NOT SERVER_EOT(h)
Print sColor_Code
Server h Fetch Next
Loop
Server h Close
Server hdbc Disconnect
This will build the Test database in SQL Server, produce the list
I want with a row count:
Create Database Test
Go
Use test
Create Table MyTable (
ID Integer NOT Null PRIMARY KEY,
Color_Code Varchar(25))
Insert Into MyTable (ID, Color_Code) Values (1, 'Red')
Insert Into MyTable (ID, Color_Code) Values (2, 'Red')
Insert Into MyTable (ID, Color_Code) Values (3, 'Blue')
Insert Into MyTable (ID, Color_Code) Values (4, 'Red')
Insert Into MyTable (ID, Color_Code) Values (5, 'Blue')
Insert Into MyTable (ID, Color_Code) Values (6, 'Green')
Select Distinct Color_Code From MyTable; Select @@ROWCOUNT
-- end script
If anyone knows how to this, I'd sure like to know, but thanks
for the help so far!
--
- Bill Thoen
------------------------------------------------------------
GISnet, 1401 Walnut St., Suite C, Boulder, CO 80302
tel: 303-786-9961, fax: 303-443-4856
mailto:[EMAIL PROTECTED], http://www.gisnet.com/
------------------------------------------------------------
_______________________________________________________________________
List hosting provided by Directions Magazine | www.directionsmag.com |
To unsubscribe, send e-mail to [EMAIL PROTECTED] and
put "unsubscribe MapInfo-L" in the message body.
