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

WIL Extenders
ODBC
plus
Sample Code

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

Retrieving data after a SQL JOIN

Keywords: 	 Retrieving data after a SQL JOIN

Question:

I have used qExecDirect to select data across two tables using a JOIN. For example, I may have 

SELECT * FROM Bookings,Guests WHERE Bookings.Booking_ID = Guests.Booking_ID AND
Date_Booked = '20000717'

I can use qGetData (after a qFetch) to retrieve data from the first table which has 50 columns but when I go beyond this (to get data from the JOINed table), I get WIL Extender Error 507: Invalid column.

How do I access JOINed tables?

I get the error on the qGetData statement where I try to access a column number greater than the number of columns in the first table. 

AddExtender("wwodb34I.dll")
;INITIALIZE VARIABLES
MyDataSource="RESQL"
MyTableName="Bookings,External_Guests"
;MyTableName="Bookings"

;THIS ALLOCATES A SQL ENVIRONMENT HANDLE
henv = qAllocEnv()

If henv == -1
  ;THIS RETURNS LAST CODE SET BY LAST SQL FUNCTION
  retcode = qLastCode()  
  Message("qAllocEnv failed", retcode)
  Exit
Endif

;ALLOCATES A SQL CONNECTION HANDLE - MAXIMUM 
;OF 10 OPEN CONNECTION HANDLES
hdbc = qAllocConnect(henv) 

If hdbc == -1
  retcode = qLastCode()
  Message("qAllocConnect failed", retcode)
  Exit
Endif

;CONNECTS TO THE "SAMPLE" DATA SOURCE (with the data source name 'ContactDSN')
retcode = qConnect(hdbc, MyDataSource, "sa", "") 

If (retcode != @qSuccess) && (retcode != @qSuccessInfo)
  Message("qConnect failed", retcode)
  Exit
Endif

;THIS ALLOCATES A SQL STATEMENT HANDLE
hstmt = qAllocStmt(hdbc)  

;THE FOLLOWING EXECUTES AN SQL STATEMENT.
;THE COLUMNS BELOW ARE ACTUAL COLUMN NAMES IN YOUR DATA SOURCE, ;E.G.,"LASTNAME":
today    = StrSub(StrReplace(TimeYMDHMS(),":",""),1,8)
tomorrow = today + 1
thedayafter = tomorrow + 1
;retcode  = qExecDirect(hstmt, "SELECT * FROM %MyTableName% WHERE Date_Request > '%today%' AND Bookings.Date_Request < '%tomorrow%' AND Bookings.Booking_ID = External_Guests.Booking_ID")
retcode  = qExecDirect(hstmt, "SELECT * FROM %MyTableName% WHERE Date_Request > '20000717' AND Bookings.Booking_ID = External_Guests.Booking_ID")

If (retcode != @qSuccess) && (retcode != @qSuccessInfo)
  Message("qExecDirect failed", retcode)
  Exit
Endif

names = ""
While @TRUE
  ;FETCHES A ROW OF DATA FROM A RESULT SET
  retcode = qFetch(hstmt) 

  If retcode == @qNoData Then Break

  If (retcode != @qSuccess) && (retcode != @qSuccessInfo)
    Message("qFetch failed", retcode)
    Exit
  Endif

  ;RETRIEVES THE VALUE OF A COLUMN IN THE CURRENT ROW 
  ;OF A RESULT SET
  retcode = qGetData(hstmt, 2, "BookingID", 80)
  retcode = qGetData(hstmt, 5, "Room", 80)
  retcode = qGetData(hstmt, 54, "Firstname", 80)
  retcode = qGetData(hstmt, 55, "Surname", 80)

  If (retcode != @qSuccess) && (retcode != @qSuccessInfo)
    Message("qGetData failed", retcode)
    Exit
  Endif

  names = StrCat(names,BookingID,@tab,Room,@tab,Firstname," ",Surname,@LF)
EndWhile

;DISPLAY NAMES 
Message("Booking IDs and Rooms for today", names) 

;SQL_DROP  - RESETS OR FREES AN SQL STATEMENT HANDLE
qFreeStmt(hstmt, 1)
;CLOSES A CONNECTION TO A DATA SOURCE
qDisconnect(hdbc)
;FREES AN SQL CONNECTION HANDLE
qFreeConnect(hdbc)
;FREES AN SQL ENVIRONMENT HANDLE
qFreeEnv(henv)

Reply

If you have the same column name in both tables, the SELECT * will cause such an error. 

Article ID:   W14540

Filename:   Retrieving data after a SQL JOIN.txt
File Created: 2001:03:01:15:42:20
Last Updated: 2001:03:01:15:42:20