WinBatch Tech Support Home

Database Search

If you can't find the information using the categories below, post a question over in our WinBatch Tech Support Forum.

TechHome

ODBC
plus

Can't find the information you are looking for here? Then leave a message over on our WinBatch Tech Support Forum.

SQL Server SELECT SUM Statement


Question:

I have used the sample files from ODBC Tutorial Extender to execute a select command as seen below. I have a script successfully displaying the rows and writing the results to a text file.

This executes fine if I only need to display the rows.

SQL = StrCat("select * from sysop.ae_dt1")

The goal is: I would like the sum of the numobjects table as opposed to all of the rows to display.

Both commands below fail to give me the sum of numobjects. No error codes, just seems to ignore my script. Also, both scripts below work fine in SQL Analyzer. Sample 1: Just runs never returning an error or the expected file or display.

SQL = StrCat("select numobjects from sysop.ae_dt1 COMPUTE SUM(numobjects)" )

Sample 2: Does not return an error, it just gives me only the rows.

SQL = "SELECT SUM(numobjects) FROM sysop.ae_dt1" 
Also, Is there an alternate way for me to add the contents of my text file to display a total count. The file only has the one table and has a LF for each entry. This file will average around a million rows of data to add. I am looking for the sum of the data not a row count.

Answer:

Are you capturing the return value of the qExecDirect statement. If not, you should be.

Also lets see what the driver says. The qError function can be used to capture the error information returned by the SQL server ODBC driver.

The code might look something like this:

SQL = "Your SQL statement goes here"
retcode = qExecDirect(hstmt, SQL)

If (retcode != @qSuccess) && (retcode != @qSuccessInfo)
  err=qerror(hstmt,2)
  message("qExecDirect failed: SQL server ODBC driver returned",err)
  Exit
Endif
Also you might want to run your SQL statement throught the qNativeSql( hdbc, SQL-string) function.

User Reply:

Here is the resulting DebugTrace output.
************************************************************

*** Debug Initialized ***

==============================
Thu 4/1/2004 2:55:54 PM
WinBatch 32 2004A
WIL DLL 4.4add
C:\Documents and Settings\Administrator\Desktop\programs\Programming Examples\Extenders\ODBC\experiments\17.wbt
Windows platform: NT, version: 5.0, build: 2195 (Service Pack 2)
==============================

----- Extender loaded: C:\Program Files\WinBatch\system\wwodb34I.dll (file version: 10013,0,0,0)

AddExtender("wwodb34I.dll")
(0) VALUE=> 1

DSN = "YD"
(0) VALUE=> "YD"

henv = qAllocEnv()
(15) VALUE=> 21435040

If henv == -1
(15) END OPERATOR

hdbc = qAllocConnect(henv)
(15) VALUE=> 0

If hdbc == -1
(15) END OPERATOR

retcode = qConnect(hdbc, DSN , "sysop", "****")
(46) VALUE=> 1

If (retcode != @qSuccess) && (retcode != @qSuccessInfo)
(46) END OPERATOR

hstmt = qAllocStmt(hdbc)
(62) VALUE=> 0

If hstmt == -1
(62) END OPERATOR

SQL = StrCat("SELECT SUM(numobjects)FROM sysop.ae_dt1")
(62) VALUE=> "SELECT SUM(numobjects)FROM sysop.ae_dt1"

retcode = qExecDirect(hstmt, SQL)
(62) VALUE=> 0

If (retcode != @qSuccess) && (retcode != @qSuccessInfo)
(78) END OPERATOR

columnstr = ""
(78) VALUE=> ""

While @TRUE
(78) WHILE==>TRUE

retcode = qFetch(hstmt)
(78) VALUE=> 0

If retcode == @qNoData Then Break
(78) ==>FALSE=> (skipped)

If (retcode != @qSuccess) && (retcode != @qSuccessInfo)
(93) END OPERATOR

column = 8
(93) VALUE=> 8

retcode = qGetData(hstmt, column, "numobjects", 80)
(93) VALUE=> -1

If (retcode != @qSuccess) && (retcode != @qSuccessInfo)
(93) IF DO==>TRUE

Exit
(93) VALUE=> 0

--- Normal termination ---

;;;END OF JOB;;;

Answer:

Looks like the code is running up to the qGetData statement, and it is unsuccessful so it exits the script. Add the following code just after the qGetData statement:
If (retcode != @qSuccess) && (retcode != @qSuccessInfo)
      Reterror=qError( hstmt,2)
      Message("qGetData failed", Reterror)
      exit
Endif
IMPORTANT!!!! I noticed you are specifying column = 8, I think you might want to change that to column = 1

User Reply:

Thanks for your help! You were right on ,with the line:
qGetData(hstmt, 1, "numobjects", 80)
Once I replaced the "column" to "1" in the above line, all is well..

Article ID:   W16343
File Created: 2005:02:18:12:19:56
Last Updated: 2005:02:18:12:19:56