July 16, 2012 at 7:26 am
I'm trying to use the BITWISE & in a SELECT statement, but the database won't accept it.
I'm not sure what version of SQLServer I'm using, but the Enterprise manager is V8.0
@@VERSION:
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
Windows XP SP3
For example
SELECT * FROM parameters where printer_type & 2
Error: Line 1: Incorrect syntax near '2'
I've tried creating a procedure so the code is embedded in the database
CREATE PROCEDURE GetPrintersByType
@PrinterType INT
AS
DECLARE @sqlquery NVARCHAR(500)
SET @sqlquery = 'SELECT * FROM parameters WHERE printer_type & @PrinterType'
EXEC sp_executesql @sqlquery,N'@PrinterType INT',@PrinterType
GO
Error: Incorrect syntax near printer_type
Am I just using a version that doesn't support the bitwise operators or something?
July 16, 2012 at 7:32 am
I figured out what I was doing wrong:
CREATE PROCEDURE [dbo].[GetPrintersByType]
@PrinterType INT
AS
DECLARE @sqlquery NVARCHAR(500)
SET @sqlquery = 'SELECT * FROM parameters WHERE printer_type & @PrinterType=@PrinterType'
EXEC sp_executesql @sqlquery,N'@PrinterType INT',@PrinterType
GO
I hadn't put the = bit in...
July 16, 2012 at 7:33 am
A where clause predicate has to compare some expression with some other expression.
Where Column1 > 4
Where Column1 + Column2/Column3 = 0
What you have there is
Where Column1
A single expression, no comparison. (SQL's not C++ where you can say IF (1), in SQL it would have to be IF (1 = 1), etc)
You need something to compare that expression to.
Probably
SELECT * FROM parameters WHERE printer_type & 2 = 2
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 16, 2012 at 7:38 am
To start with the suggestions, you can always use:
SELECT @@VERSION
to define the version of SQL Server you're working with.
In the other hand, bitwise operators will return an integer (the result of the operation e.g. 0, 1, 2, 8).
Your code could work if you change your code like this.
CREATE PROCEDURE GetPrintersByType
@PrinterType INT
AS
DECLARE @sqlquery NVARCHAR(500)
SET @sqlquery = 'SELECT * FROM parameters WHERE (printer_type & @PrinterType) = @PrinterType'
EXEC sp_executesql @sqlquery,N'@PrinterType INT',@PrinterType
GO
July 16, 2012 at 7:43 am
Thanks for the suggestions.
July 16, 2012 at 8:34 am
Now I've "fixed" the SQL. I've got a problem using it.
If I execute the SQL in Query Analyzer, I get 13 records
select * from parameters where printer_type & 8 = 8
If I execute the same SQL statement from VB, either as a call to a stored procedure or as a direct SQL statement (ADODB) I get no records returned.
Any ideas?
This is probably the wrong forum for this question...
July 16, 2012 at 8:37 am
Without seeing how you're calling from VB, no.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 16, 2012 at 8:41 am
OK, sorry...
CREATE PROCEDURE [dbo].[GetPrintersByType]
@PrinterType INT
AS
DECLARE @sqlquery NVARCHAR(500)
SET @sqlquery = 'SELECT * FROM parameters WHERE printer_type & @PrinterType=@PrinterType'
EXEC sp_executesql @sqlquery,N'@PrinterType INT',@PrinterType
GO
Public Function LoadPrintersByType(ByVal pType As ePrinterType) As ADODB.Recordset
' this should work, but doesn't yet
Dim sSQL As String
Dim co As ADODB.Command
Dim rs As ADODB.Recordset
If OpenPrinterDB Then
Set co = New ADODB.Command
co.ActiveConnection = PrinterDBPrivate
co.CommandType = adCmdStoredProc
co.CommandText = "GetPrintersByType"
co.Parameters.Refresh
co.Parameters("@PrinterType") = pType
Set rs = New ADODB.Recordset
rs.Open co
If rs.RecordCount < 1 Then
sSQL = "SELECT * FROM parameters WHERE printer_type & " & pType & " = " & pType & ""
Set rs = New ADODB.Recordset
rs.Open sSQL, PrinterDBPrivate
End If
If rs.RecordCount > 0 Then
Set LoadPrintersByType = rs
Else
Set LoadPrintersByType = Nothing
End If
Set rs = Nothing
Set co = Nothing
End If
End Function
dim rs as ADODB.Recordset
set rs = LoadPrintersByType(8)
July 16, 2012 at 9:15 am
Firstly let's fix your procedure. No need for dynamic SQL.
CREATE PROCEDURE [dbo].[GetPrintersByType] (@PrinterType INT)
AS
SELECT <column list here> FROM parameters WHERE printer_type & @PrinterType = @PrinterType
GO
Not familiar enough with VB to see a problem, other than a potential SQL Injection vulnerability, but call the proc above from VB, I tested the proc, it does work.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 16, 2012 at 9:27 am
OK, thanks for that. My procedure got "complicated" as I was trying to "fix" it...
I've tested the procedure in the Query Analyzer
use printing
go
exec GetPrintersByType 8
and got the expected 13 records.
I do the same from VB, and get nothing. The recordset.recordcount = -1
I'm feeling particularly stupid right now, I'm positive I'm missing something obvious :doze:
July 16, 2012 at 9:49 am
One tip I could give you is to use the debugger to check you have the correct value in the sSQL variable.
The other thing I noticed is you wrote the code for the query itself and not the call for the SP.
July 16, 2012 at 10:01 am
Also parameterise the call from VB, don't just concatenate a string together and execute it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 17, 2012 at 9:32 am
Hi,
Don't you need a Return stament on the VB function??
July 17, 2012 at 9:35 am
Public Function LoadPrintersByType(ByVal pType As ePrinterType) As ADODB.Recordset
' this should work, but doesn't yet
Dim sSQL As String
Dim co As ADODB.Command
Dim rs As ADODB.Recordset
If OpenPrinterDB Then
Set co = New ADODB.Command
co.ActiveConnection = PrinterDBPrivate
co.CommandType = adCmdStoredProc
co.CommandText = "GetPrintersByType"
co.Parameters.Refresh
co.Parameters("@PrinterType") = pType
Set rs = New ADODB.Recordset
rs.Open co
If rs.RecordCount < 1 Then
sSQL = "SELECT * FROM parameters WHERE printer_type & " & pType & " = " & pType & ""
Set rs = New ADODB.Recordset
rs.Open sSQL, PrinterDBPrivate
End If
If rs.RecordCount > 0 Then
Set LoadPrintersByType = rs
Else
Set LoadPrintersByType = Nothing
End If
'Set rs = Nothing
'Set co = Nothing
End If
return Rs
End Function
July 17, 2012 at 9:52 am
I finally got it working.
My problem was the bug with Recordset and StoredProcedures
You need to set
rs.CursorLocation = adUseClient
otherwise RecordCount, BOF and EOF are not set.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply