Page 1 of 2 12>
Topic Options
#134315 - 2005-02-22 11:48 PM ODBC - DBcommand
Faithfulman Offline
Getting the hang of it

Registered: 2005-02-22
Posts: 68
$ConnDSN = 'ncivesConn'
$sql = "SELECT workst.wSid FROM workst WHERE workst.pcName = '@wksta'"


$recordset = DBCommand($dsn,$sql)
; ? 'Error = '+@ERROR+' - '+@SERROR
sleep 3
for $row=0 to ubound($recordset,1)
for $column=0 to ubound($recordset,2)
? 'Field(row='+$row+', column='+$column+') ='+$recordset[$row,$column]
next
next


This doesn't seem to work. I am just trying to have the Kix Script query for one single variable. There will never ever be more than one variable. What am I doing wrong.??
Help!!

Top
#134316 - 2005-02-23 12:37 AM Re: ODBC - DBcommand
Sealeopard Offline
KiX Master
*****

Registered: 2001-04-25
Posts: 11164
Loc: Boston, MA, USA
What's the error code? Is the DSN correct? Haven't seen one of these so far? What's the backend database?
_________________________
There are two types of vessels, submarines and targets.

Top
#134317 - 2005-02-23 12:53 AM Re: ODBC - DBcommand
Faithfulman Offline
Getting the hang of it

Registered: 2005-02-22
Posts: 68
I get an error 87 - The Parameter is incorrect.

I am wondering if it is not even attempting to connect?
I'm using MySQL ... and I have the ODBC settings already setup and I've been using it with Cyrstal Reports, so I know it works.

Here is my script:

Code:
   
Dim $pcn

SetConsole ("SHOW")

Color w+/n

;$pcn=@wksta

? "Your PC name is " + "@wksta"

sleep 2







function DBCommand($ConnDSN,$sql)
Dim $objConn, $adStateOpen
dim $Conntimeout, $CmdTimeout
dim $cmdCommand, $rsRecordset
dim $Records, $FinalRecords
dim $adCmdText, $adLockReadOnly, $adOpenStatic
dim $row, $rows, $column, $columns

$ConnDSN=trim($ConnDSN)
if not $ConnDSN
exit 87
endif
$sql=trim($sql)
if not $sql
exit 87
endif

; default database parameters
$adStateOpen=1
$ConnTimeout=15
$CmdTimeout=30
$adCmdText = 1
$adOpenStatic = 3
$adLockReadOnly = 1

; open the database connection
$objConn = CreateObject("ADODB.Connection")
if @ERROR
exit @ERROR
endif
$objConn.ConnectionTimeout = $ConnTimeout
if @ERROR
exit @ERROR
endif
$objConn.CommandTimeout = $CmdTimeout
if @ERROR
exit @ERROR
endif
$objConn.Open($ConnDSN)
if @ERROR
exit @ERROR
endif
if not $objConn.State=$adStateOpen
$objConn=''
$DBCommand=''
exit @ERROR
endif

; create the database command object
$cmdCommand = CreateObject('ADODB.Command')
if @ERROR
exit @ERROR
endif
$cmdCommand.ActiveConnection = $objConn
if @ERROR
exit @ERROR
endif
$cmdCommand.CommandType = $adCmdText
if @ERROR
exit @ERROR
endif
$cmdCommand.CommandText = $sql
if @ERROR
$DBCommand=@ERROR
exit @ERROR
endif

if instr($sql,'SELECT')=1
; create the recordset object
$rsRecordSet = CreateObject('ADODB.Recordset')
if @ERROR
exit @ERROR
endif
$rsRecordset.CursorType = $adOpenStatic
if @ERROR
exit @ERROR
endif
$rsRecordset.LockType = $adLockReadOnly
if @ERROR
exit @ERROR
endif
$rsRecordset.Open($cmdCommand)
if @ERROR
exit @ERROR
endif

if $rsRecordset.EOF and $rsRecordSet.BOF
; recordset is empty
$FinalRecords=''
else
if @ERROR
exit @ERROR
endif

; retrieve all records at once and transpose into tabular format
$Records = $rsRecordset.GetRows()
$columns=ubound($records,1)
$rows=ubound($records,2)
redim $FinalRecords[$rows,$columns]
for $row=0 to $rows
for $column=0 to $columns
$FinalRecords[$row,$column]=$records[$column,$row]
next
next
endif

; close recordset
if $rsRecordset.state=$adStateOpen
$rsRecordset.Close()
if @ERROR
exit @ERROR
endif
endif

$rsRecordset=''
$cmdCommand=''

$DBCommand=$FinalRecords
else
$rsRecordset=$cmdCommand.Execute()
$cmdCommand=''
$rsRecordset=''
if @ERROR
exit @ERROR
endif

$DBCommand=0
endif

; close the database connection
If $objConn.State = $adStateOpen
$objConn.Close()
if @ERROR
exit @ERROR
endif
EndIf
$objConn=''

exit 0
endfunction




$ConnDSN = "ncivesConn"
$sql = "SELECT workst.wSid FROM workst WHERE workst.pcName = '@wksta'"


$recordset = DBCommand($dsn,$sql)

? 'Error = '+@ERROR+' - '+@SERROR

? $recordset
; sleep 3

for $row=0 to ubound($recordset,1)
for $column=0 to ubound($recordset,2)
'Field(row='+$row+', column='+$column+') ='+$recordset[$row,$column]
next
next



? "Your workstation ID number is " + $Records

sleep 3

SetConsole ("Hide")


;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;:


Top
#134318 - 2005-02-23 05:40 PM Re: ODBC - DBcommand
Faithfulman Offline
Getting the hang of it

Registered: 2005-02-22
Posts: 68
Does anyone have any advice on this? Maybe someone can help me make a basic working script? Thanks,

Faithful

Top
#134319 - 2005-02-23 06:04 PM Re: ODBC - DBcommand
Shawn Administrator Offline
Administrator
*****

Registered: 1999-08-13
Posts: 8611
Right off the bat, i see a disconnect between your DSN here:

$ConnDSN = "ncivesConn"

and they way you use it, here:

$recordset = DBCommand($dsn,$sql)

should (at least) be trying $ConnDSN instead of $dsn ?

Top
#134320 - 2005-02-23 09:20 PM Re: ODBC - DBcommand
Faithfulman Offline
Getting the hang of it

Registered: 2005-02-22
Posts: 68
Wow ... I cant believe I forgot that.

But I still get an error. It says the following:

"his context.) [-2147352567/80020009]eration. It is either closed or invalid in t"


What does this mean??

Thanks,

Faithful

Top
#134321 - 2005-02-23 09:48 PM Re: ODBC - DBcommand
Stanley Offline
Starting to like KiXtart

Registered: 2004-06-03
Posts: 130
Loc: Upstate NY, USA
I know very little about your database connection issue but,
Including
Code:
  
$ = SetOption("WrapAtEOL","ON")


At the beginning of your script may give you a more readable error message.

_________________________
Taxation WITH representation isn't so hot, either!

Top
#134322 - 2005-02-23 10:24 PM Re: ODBC - DBcommand
Shawn Administrator Offline
Administrator
*****

Registered: 1999-08-13
Posts: 8611
ja for sure - put in Stanley's suggestion and lets see the full text of that error message.

-Shawn

Top
#134323 - 2005-02-23 10:31 PM Re: ODBC - DBcommand
Faithfulman Offline
Getting the hang of it

Registered: 2005-02-22
Posts: 68
Sorry about that ... here is the full error message:

Error = 2147352567 COM exception error "Open" (ADODB.Recordset - The connection cannot be used to perform this operation. It is either closed or invalid in this context.) [-2147352567/80020009]

Top
#134324 - 2005-02-23 10:45 PM Re: ODBC - DBcommand
Stanley Offline
Starting to like KiXtart

Registered: 2004-06-03
Posts: 130
Loc: Upstate NY, USA
Shouldn't this
Code:
 

$sql = "SELECT workst.wSid FROM workst WHERE workst.pcName = '@wksta'"


be this ?
Code:
 

$sql = "SELECT workst.wSid FROM workst WHERE workst.pcName = "+@wksta



Or does it need to be quoted ?

Code:
 

$sql = "SELECT workst.wSid FROM workst WHERE workst.pcName = "+CHR(34)+@wksta+CHR(34)





Edited by Stanley (2005-02-23 10:48 PM)
_________________________
Taxation WITH representation isn't so hot, either!

Top
#134325 - 2005-02-23 11:02 PM Re: ODBC - DBcommand
Anonymous
Unregistered


No, the SQL statement is correct. I tried both of your solutions and it gave me the same result.

I am printing out the SQL statement and testing it in PHPMYADMIN and it works fine in there. It has gotta to be something else.

HELP?

Top
#134326 - 2005-02-23 11:05 PM Re: ODBC - DBcommand
Faithfulman Offline
Getting the hang of it

Registered: 2005-02-22
Posts: 68
Sorry, ... that was me, I just forgot to sign back in.
Top
#134327 - 2005-02-23 11:14 PM Re: ODBC - DBcommand
Shawn Administrator Offline
Administrator
*****

Registered: 1999-08-13
Posts: 8611
Can you try hand-jobbing the DSN as a test, format should be something like:

$dsn = "Provider=MSDASQL;Driver={SQL Server};Server=myServerName; Database=myDatabaseName;Uid=myUsername;Pwd=myPassword"

Something like that - your mileage will vary.

-Shawn


Top
#134328 - 2005-02-23 11:20 PM Re: ODBC - DBcommand
Faithfulman Offline
Getting the hang of it

Registered: 2005-02-22
Posts: 68
Is there any reason it would be different because I am using a MYSQL server?


Top
#134329 - 2005-02-23 11:33 PM Re: ODBC - DBcommand
Shawn Administrator Offline
Administrator
*****

Registered: 1999-08-13
Posts: 8611
woops, ja - should be :

$dsn = "Provider=MySQLProv;Data Source=mySQLDB;User Id=myUsername;Password=myPassword"

-Shawn

Top
#134330 - 2005-02-23 11:36 PM Re: ODBC - DBcommand
Faithfulman Offline
Getting the hang of it

Registered: 2005-02-22
Posts: 68
Well, at least I know it's trying to work .... If I put in a bad password it comes up with "Access denied for user..."

But when I put the right password in. .... I still get the same error as before:

COM exception error "OPEN" .......

Any other ideas??

Top
#134331 - 2005-02-23 11:38 PM Re: ODBC - DBcommand
Shawn Administrator Offline
Administrator
*****

Registered: 1999-08-13
Posts: 8611
Lets see all your code again (no need to show DBCommand again)
Top
#134332 - 2005-02-23 11:45 PM Re: ODBC - DBcommand
Faithfulman Offline
Getting the hang of it

Registered: 2005-02-22
Posts: 68
Here you go

Code:
 

$ = SetOption("WrapAtEOL","ON")
Dim $pcn

SetConsole ("SHOW")

Color w+/n

;$pcn=@wksta

? "Your PC name is " + "@wksta"

sleep 2


;$ConnDSN = "ncivesConn"


$ConnDSN = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=ncives;USER=Ref;PASSWORD=Ref;OPTION=3;"




$sql = "SELECT * FROM workst;"
;$sql = "SELECT workst.wSid FROM workst WHERE workst.pcName = '@wksta'"

? $sql
sleep 3

$recordset = DBCommand($ConnDSN,$sql)

? 'Error = '+@ERROR+' - '+@SERROR


sleep 1

for $row=0 to ubound($recordset,1)
for $column=0 to ubound($recordset,2)
? 'Field(row='+$row+', column='+$column+') ='+$recordset[$row,$column]
next
next



? "Your workstation ID number is " + $Records



<<>>




sleep 5

SetConsole ("Hide")


;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;:



Top
#134333 - 2005-02-23 11:58 PM Re: ODBC - DBcommand
Shawn Administrator Offline
Administrator
*****

Registered: 1999-08-13
Posts: 8611
ok, I just got this to return success on my machine, what yours say ?

Code:

break on

$= SetOption("WrapAtEol", "On")

$connection = createobject("adodb.connection")

$ConnDSN = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=phone;USER=root;PASSWORD=********;";OPTION=3;"

$= $connection.open($connDSN)

? @SERROR



hmmm - i took the option=3 off though

[edit]

option=3 works for me too



Top
#134334 - 2005-02-24 12:01 AM Re: ODBC - DBcommand
Shawn Administrator Offline
Administrator
*****

Registered: 1999-08-13
Posts: 8611
I didn't post full code, see above
Top
Page 1 of 2 12>


Moderator:  Jochen, Allen, Radimus, Glenn Barnas, ShaneEP, Ruud van Velsen, Arend_, Mart 
Hop to:
Shout Box

Who's Online
0 registered and 259 anonymous users online.
Newest Members
gespanntleuchten, DaveatAdvanced, Paulo_Alves, UsTaaa, xxJJxx
17864 Registered Users

Generated in 0.074 seconds in which 0.027 seconds were spent on a total of 15 queries. Zlib compression enabled.

Search the board with:
superb Board Search
or try with google:
Google
Web kixtart.org