Hi, I am trying to understand how to use DBCommand() properly. I use the example provided in the function to base my test:
Code:
$sql = "select * from USERS where UserID = 'test'"
$dsn ='Provider=SQLNCLI;Server=bp-sys-01;Database=audits_netlogon;Trusted_Connection=yes;'
$recordset = DBCommand($dsn,$sql)
? 'Error = '+@ERROR+' - '+@SERROR
for $row=0 to ubound($recordset,1)
for $column=0 to ubound($recordset,2)
? 'Field(row='+$row+', column='+$column+') ='+$recordset[$row,$column]
next
next
The error code I get is this: -2147352567
I know my $sql statement works, because I tried it directly in SQL Server Management Studio and it returns data.
For the $dsn connection string, I tried it with DBConnOpen() and it works ok.
#205246 - 2012-05-1108:48 PMRe: DBCommand() problem
[Re: marvince]
MartMart KiX Supporter
Registered: 2002-03-27
Posts: 4673
Loc: The Netherlands
I never used DBCommand() but the code below using DBConnOpen(), DBGetRecordset() and DBConnClose() work fine for me
Code:
$open = DBConnOpen("DRIVER={SQL Server};SERVER=SQLSERVERNAME;UID=USERNAME;PWD=PASSWORD;DATABASE=DATABASENAME")
$query = "SELECT Value FROM dbo.ListOptions where Type = 'Carrier' Order by Value"
$recordset = DBGetRecordset($open, $query)
$close = DBConnClose($open)
Do you get the same results with these functions?
Edited by Mart (2012-05-1108:56 PM) Edit Reason: Typo.
$open = DBConnOpen("DRIVER={SQL Server}Server=bp-sys-01;Database=audits_netlogon;Trusted_Connection=yes;")
$query = "select * from USERS where UserID = 'test'"
$recordset = DBGetRecordset($open, $query)
$close = DBConnClose($open)
for $row=0 to ubound($recordset,1)
for $column=0 to ubound($recordset,2)
? 'Field ='+$recordset[$row,$column]
next
next
Quit
If I try this code, no error and no data... strange because in Management Studio the query returns 2634 rows. I use the same Windows account for both.
OK, this is my mistake. The first query works with DBCommand(). The reason it was not working is the account running kix32 didn't have select access on the table... My bad!
Edited by marvince (2012-05-1109:36 PM) Edit Reason: typo
Can happen to everyone. Usually it is the obvious and easy thing that is missed.
