August 7, 2007 at 10:57 am
The following is production SQL. I can find nothing in MSDN or the Web to explain the question mark in the WHERE clause here: SELECT DEPARTMENT.ID, DEPARTMENT.NAME, DEPARTMENT.FUNDCODE, DEPARTMENT.DEPTCODE, DEPARTMENT.DEFAULTCONTACT, CONTACT.LNAME AS DEFAULTCONTACTLASTNAME, CONTACT.FNAME AS DEFAULTCONTACTFIRSTNAME, CONTACT.POSITION AS DEFAULTCONTACTPOSITION, CONTACT.PHONE AS DEFAULTCONTACTPHONE, CONTACT.FAX AS DEFAULTCONTACTFAX, CONTACT.EMAIL AS DEFAULTCONTACTEMAIL, DEPARTMENT.ISACTIVE, DEPARTMENT.ISACTIVECOMMENTS, DEPARTMENT.POSTUSERID, DEPARTMENT.POSTDATETIME FROM TDEPARTMENT AS DEPARTMENT LEFT JOIN TCONTACT AS CONTACT ON DEPARTMENT.DEFAULTCONTACT = CONTACT.ID WHERE DEPARTMENT.ID = ? ORDER BY DEPARTMENT.NAME ASC I have never seen a question mark in the WHERE clause like this before. If I run such a query in Query Analyzer I get: [Microsoft][ODBC SQL Server Driver]COUNT field incorrect or syntax error Can anyone explain: "DEPARTMENT.ID = ?" ? |
August 7, 2007 at 11:29 am
Is this from a DTS Package? If so, it's a place holder for a parameter.
HTH
Dave J
August 7, 2007 at 11:38 am
No DTS.
This SQL, contained in var 'query', is getting stuffed into ADO
and executed as in . . . (see last line for Execute):
60020 'Processing - Add the sort order clause to the query
query = query & orderBy
60100 'Processing - Open the database connection
If Not DatabaseConnectionIsConnected Then
Call ConnectToDatabase
End If
60200 'Processing - Instantiate an ADODB.Command object to handle the query
Set adoQuery = New ADODB.Command
Set adoParameter = New ADODB.Parameter
Set adoQuery.ActiveConnection = m_adoConnection
60201 'Processing - Send the query text to the debug console
Select Case vFetchTypeEnum
'START MOD Query was generating error on ADODB.Command object's Parameter.Refresh call
Case SB90Data.FetchTypeEnum.TEST_CLAIM_HISTORY_ALL
60210 'Processing - Set the command type & command text
adoQuery.CommandText = query
'END MOD
Case SB90Data.FetchTypeEnum.REIMBURSEMENT_CLAIM_BY_KEY
60250 'Processing - Set the command type & command text
adoQuery.CommandText = query
Case Else
60900 'Processing - Set the command type & command text
adoQuery.CommandType = adCmdText
adoQuery.CommandText = query
60910 'Processing - Set the ADODB.Command object to prepare the query before execution
adoQuery.Prepared = True
69920 'Processing - Refresh Command object's parameters
adoQuery.Parameters.Refresh
69930 'Processing - Set the Command object's parameters
queryParameterCount = 0
For Each adoParameter In adoQuery.Parameters
queryParameterCount = queryParameterCount + 1
adoParameter.Value = vWhereValues.Items(queryParameterCount - 1)
Next adoParameter
End Select
69980 'Processing - Execute the query
Set adoRecordset = adoQuery.Execute()
August 7, 2007 at 11:45 am
I don't know ADO but I'd bet its a place holder. Look at this line
adoParameter.Value = vWhereValues.Items(queryParameterCount - 1)
That sets the value of the ? with whatever vWhereValues.Items(queryParameterCount - 1) resolves to at run time?
Dave J
August 7, 2007 at 11:54 am
Books Online mentions Parameter Markers and this sounds like it is what you are looking for.
From BOL for SQL Server 2000:
Parameter markers are supported by the ADO, OLE DB, and ODBC-based database APIs. A parameter marker is a question mark (?) placed in the location of an input or output expression in a Transact-SQL statement. Parameter markers allow an application to optimize the case where the same Transact-SQL statement is executed several times with different values for the input and output expressions.
For example, a user may have given an application five different stock symbols and the application has to call a stored procedure that gets the current data for each stock. The application could:
EXEC GetQuoteProcedure @StockSymbolParameter = ?Parameter markers are not limited to being mapped to stored procedure parameters. They can be used anywhere an input expression is used:
UPDATE EmployeesSET Title = ?WHERE EmployeeID = ?Parameter markers can also be used to map stored procedure output parameters and return codes. When the application executes a stored procedure, the OLE DB provider or ODBC driver moves the data values from any output parameters or return codes into the variables bound to the parameter's markers. For example, an application can execute this procedure, which returns an integer return code and a character output parameter:
{? = CALL MyProc (?)}At this point, the Microsoft OLE DB Provider for SQL Server or SQL Server ODBC driver will have placed the return code and output parameter value in the bound variables. Microsoft® SQL Server™ returns output parameter and return code values in the last packet it returns to the client. Therefore, the application must process or cancel all result sets returned by the stored procedure before it has access to the return code and output parameter values.
The ADO API has a variation on this process for executing stored procedures. An ADO application:
Parameter markers are associated with a database object that has a specific data type. If the application binds a parameter marker to a variable whose data type differs from that of the associated database object, then the OLE DB provider or ODBC driver must convert the data. For example, if an application binds an integer return code to a character array, then the OLE DB provider or ODBC driver must convert the return code integer data to a character string. For information about the data type conversions that are supported, see the documentation for OLE DB Provider for SQL Server and SQL Server ODBC driver.
August 7, 2007 at 12:19 pm
Unh-hunh. Thought it might be . . . but couldn't find any doc, didn't
know what to call it. Thank you.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply