Page 1 of 2 12>
Topic Options
#137262 - 2005-04-06 07:31 PM Help with test DB Connection
thepip3r Offline
Hey THIS is FUN
*****

Registered: 2005-03-02
Posts: 350
I'm new to KiXtart so please don't destroy my code at my first attempt at writing a script. All I want to do is write a simple entry to a database and this is what I've tried:

Code:


$DBServer = "localhost"
$DBName = "test"
$DBUser = ""
$DBPassword = ""

$cnDriver = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=$DBServer;DATABASE=$DBName;USER=$DBUser;PASSWORD=$DBPassword;OPTION=3;"
$sql = "SELECT * FROM test"

$cn = CreateObject ("ADODB.Connection")
$cmd = CreateObject ("ADODB.Command")
$rs = CreateObject ("ADODB.Recordset")

$cn.ConnectionString = $cnDriver
$cn.Open
$cmd.ActiveConnection = $cn
$rs.CursorType = 3
$rs.LockType = 3
$rs.ActiveCommand = $cmd

$cmd.CommandText = $sql
$rs.Open ($cmd)

If $rs.EOF = -1
$rs.AddNew
EndIf

$rs.Fields.Item("name").Value = "test"

$rs.Update
$rs.Close
$cn.Close



It runs without errors but it doesn't write anything to my DB


Edited by thepip3r (2005-04-06 08:00 PM)

Top
#137263 - 2005-04-06 08:30 PM Re: Help with test DB Connection
Howard Bullock Offline
KiX Supporter
*****

Registered: 2000-09-15
Posts: 5809
Loc: Harrisburg, PA USA
Below are some links that should be helpful.

How to use UDFs
DBConnOpen() - Open a connection to a database using ADODB
DBExecuteSQL() - Executes a SQL command on a database
DBGetRecordset() - Retrives a recordset from a database as the result of a SQL query
_________________________
Home page: http://www.kixhelp.com/hb/

Top
#137264 - 2005-04-07 06:47 PM Re: Help with test DB Connection
thepip3r Offline
Hey THIS is FUN
*****

Registered: 2005-03-02
Posts: 350
Ok, I tried to follow the examples in the UDFs and my script runs without error but does nothing. Any suggestions?

Code:
;START FUNCTIONS

;DATABASE RELATED FUNCTIONS
Function DBConnOpen($objConn, optional $ConnTimeout, optional $CmdTimeout)

Dim $objConn, $adStateOpen

$dbconnopen=''
$adStateOpen=1

if vartype($ConnTimeout)
$ConnTimeout=val($ConnTimeout)
else
$ConnTimeout=15
endif

if vartype($CmdTimeout)
$CmdTimeout=val($CmdTimeout)
else
$CmdTimeout=30
endif

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

$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=''
$DBConnOpen=''
exit @ERROR
endif

$DBConnOpen=$objConn
exit 0

EndFunction



Function DBConnClose($objConn)
Dim $adStateOpen

$adStateOpen = 1

If vartype($objConn)=9
If $objConn.State = $adStateOpen
$objConn.Close()
if @ERROR
exit @ERROR
endif
EndIf
$objConn=''
else
exit 87
endif

$DBConnClose=@ERROR
EndFunction



function DBExecuteSQL($objConn, $sql, optional $cmdType)
dim $cmdCommand, $rsRecordset
dim $adCmdUnspecified, $adCmdText, $adCmdTable, $adCmdStoredProc, $adCmdUnknown, $adCmdFile, $adCmdTableDirect

$adCmdUnspecified = -1
$adCmdText = 1
$adCmdTable = 2
$adCmdStoredProc = 4
$adCmdUnknown = 8
$adCmdFile = 256
$adCmdTableDirect = 512

if vartype($cmdType)
$cmdType=val($cmdType)
else
$cmdType=$adCmdText
endif

if vartype($objConn)<>9 or $sql=''
exit 87
endif

$cmdCommand = CreateObject('ADODB.Command')
if @ERROR
exit @ERROR
endif

$cmdCommand.ActiveConnection = $objConn
if @ERROR
exit @ERROR
endif

$cmdCommand.CommandType = $cmdType
if @ERROR
exit @ERROR
endif

$cmdCommand.CommandText = $sql
if @ERROR
exit @ERROR
endif

$rsRecordset=$cmdCommand.Execute()
$cmdCommand=''
$rsRecordset=''

if @ERROR
exit @ERROR
endif

$DBExecuteSQL=0
exit 0

endfunction
;END DATABASE FUNCTIONS
;END FUNCTIONS


$objConn = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=test;USER=test;PASSWORD='';OPTION=3;"
$sql = "INSERT INTO computer(name) VALUES('testcomputer')"

DBConnOpen($objConn)
DBExecuteSQL($objConn,$sql)
DBConnClose($objConn)



Edited by thepip3r (2005-04-07 06:48 PM)

Top
#137265 - 2005-04-07 06:52 PM Re: Help with test DB Connection
Shawn Administrator Offline
Administrator
*****

Registered: 1999-08-13
Posts: 8611
Got to ask this question first. Did you download and install the "MySQL ODBC 3.51 Driver" driver from MySQL ?
Top
#137266 - 2005-04-07 07:52 PM Re: Help with test DB Connection
thepip3r Offline
Hey THIS is FUN
*****

Registered: 2005-03-02
Posts: 350
No but wouldn't the script have returned an error there was a problem?
Top
#137267 - 2005-04-07 08:12 PM Re: Help with test DB Connection
Shawn Administrator Offline
Administrator
*****

Registered: 1999-08-13
Posts: 8611
They probably did return an error, its up you to check for them though. For example in this line here:

Code:

DBConnOpen($objConn)



Might want to add error checking like this:

Code:

DBConnOpen($objConn)
If @ERROR
?"ERROR @ERROR : @SERROR"
Endif



-Shawn

Top
#137268 - 2005-04-07 08:41 PM Re: Help with test DB Connection
Jose Offline
Seasoned Scripter
*****

Registered: 2001-04-04
Posts: 693
Loc: Buenos Aires - Argentina
Hi thepip3r just in case:
Check out ODBC 3.51 Downloads.
In case you need to install it in several machines.... MySQLODBC().
You might need admin rights for this UDF.

If I have to guess I would say you need the ODBC driver to attempt your goal.
_________________________
Life is fine.

Top
#137269 - 2005-04-07 09:22 PM Re: Help with test DB Connection
thepip3r Offline
Hey THIS is FUN
*****

Registered: 2005-03-02
Posts: 350
Thanx guys... I'll give that shot.
Top
#137270 - 2005-04-07 09:27 PM Re: Help with test DB Connection
Shawn Administrator Offline
Administrator
*****

Registered: 1999-08-13
Posts: 8611
Read this thread. Should be very helpfull to you ...

ODBC - DBcommand

-Shawn

Top
#137271 - 2005-04-12 06:07 PM Re: Help with test DB Connection
thepip3r Offline
Hey THIS is FUN
*****

Registered: 2005-03-02
Posts: 350
Update: I still haven't downloaded the MySQL drivers but am just trying to understand error trapping so I can use it in the future. Is my error trapping totally incorrect? I tried to follow Shawn's example above. I just don't get why my script runs in about .05 seconds and doesn't give me anything!

Code:
$objConn 		= "DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=test;USER=test;PASSWORD='';OPTION=3;"
$sql = "INSERT INTO computer(name) VALUES('testcomputer')"

DBConnOpen($objConn)
if @error
? "Error @ERROR: @SERROR"
endif

DBExecuteSQL($objConn,$sql)
if @error
? "Error @ERROR: @SERROR"
endif

DBConnClose($objConn)
if @error
? "Error @ERROR: @SERROR"
endif


Top
#137272 - 2005-04-12 06:55 PM Re: Help with test DB Connection
thepip3r Offline
Hey THIS is FUN
*****

Registered: 2005-03-02
Posts: 350
ok, modified it to this:

Code:
$objConn 		= "DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=test;USER=test;PASSWORD='';OPTION=3;"
$sql = "INSERT INTO computer(name) VALUES('testcomputer')"

DBConnOpen($objConn)
? "Error @ERROR: @SERROR"
sleep 5

DBExecuteSQL($objConn,$sql)
? "Error @ERROR: @SERROR"

DBConnClose($objConn)
? "Error @ERROR: @SERROR"



and i get, "Error 87: The parameter is incorrect."

Is that still indicative that I need to have the driver installed?

Top
#137273 - 2005-04-12 07:11 PM Re: Help with test DB Connection
Shawn Administrator Offline
Administrator
*****

Registered: 1999-08-13
Posts: 8611
That error is coming from this part of the UDF ...

Code:

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



which means your first param isn't being passed properly - are you running the exact code as shown ?

-Shawn

Top
#137274 - 2005-04-12 07:22 PM Re: Help with test DB Connection
thepip3r Offline
Hey THIS is FUN
*****

Registered: 2005-03-02
Posts: 350
Here is the full code being run, copied and pasted:

Code:
Break ON
$ = SetOption("WrapAtEOL","ON")


;START FUNCTIONS

;DATABASE RELATED FUNCTIONS
Function DBConnOpen($objConn, optional $ConnTimeout, optional $CmdTimeout)

Dim $objConn, $adStateOpen

$dbconnopen=''
$adStateOpen=1

if vartype($ConnTimeout)
$ConnTimeout=val($ConnTimeout)
else
$ConnTimeout=15
endif

if vartype($CmdTimeout)
$CmdTimeout=val($CmdTimeout)
else
$CmdTimeout=30
endif

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

$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=''
$DBConnOpen=''
exit @ERROR
endif

$DBConnOpen=$objConn
exit 0

EndFunction



Function DBConnClose($objConn)
Dim $adStateOpen

$adStateOpen = 1

If vartype($objConn)=9
If $objConn.State = $adStateOpen
$objConn.Close()
if @ERROR
exit @ERROR
endif
EndIf
$objConn=''
else
exit 87
endif

$DBConnClose=@ERROR
EndFunction



function DBExecuteSQL($objConn, $sql, optional $cmdType)
dim $cmdCommand, $rsRecordset
dim $adCmdUnspecified, $adCmdText, $adCmdTable, $adCmdStoredProc, $adCmdUnknown, $adCmdFile, $adCmdTableDirect

$adCmdUnspecified = -1
$adCmdText = 1
$adCmdTable = 2
$adCmdStoredProc = 4
$adCmdUnknown = 8
$adCmdFile = 256
$adCmdTableDirect = 512

if vartype($cmdType)
$cmdType=val($cmdType)
else
$cmdType=$adCmdText
endif

if vartype($objConn)<>9 or $sql=''
exit 87
endif

$cmdCommand = CreateObject('ADODB.Command')
if @ERROR
exit @ERROR
endif

$cmdCommand.ActiveConnection = $objConn
if @ERROR
exit @ERROR
endif

$cmdCommand.CommandType = $cmdType
if @ERROR
exit @ERROR
endif

$cmdCommand.CommandText = $sql
if @ERROR
exit @ERROR
endif

$rsRecordset=$cmdCommand.Execute()
$cmdCommand=''
$rsRecordset=''

if @ERROR
exit @ERROR
endif

$DBExecuteSQL=0
exit 0

endfunction
;END DATABASE FUNCTIONS
;END FUNCTIONS


$objConn = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=test;USER=test;PASSWORD='';OPTION=3;"
$sql = "INSERT INTO computer(name) VALUES('testcomputer')"

DBConnOpen($objConn)
? "Error @ERROR: @SERROR"
sleep 5

DBExecuteSQL($objConn,$sql)
? "Error @ERROR: @SERROR"

DBConnClose($objConn)
? "Error @ERROR: @SERROR"



I just excluded the UDFs because I figured most of the people who would be helping me would know them. That may be an incorrect assumption though...


Edited by thepip3r (2005-04-12 07:23 PM)

Top
#137275 - 2005-04-12 07:47 PM Re: Help with test DB Connection
Shawn Administrator Offline
Administrator
*****

Registered: 1999-08-13
Posts: 8611
Might want to double-check your UDF's there. The function prototype for DBConnOpen that I found (in the library) is this:

Function DBConnOpen($ConnDSN, optional $ConnTimeout, optional $CmdTimeout)

but you got this:

Function DBConnOpen($objConn, optional $ConnTimeout, optional $CmdTimeout)

The implication being that the first parm is an OBJECT (connection), not a DSN string (which you are using).

-Shawn

Top
#137276 - 2005-04-13 05:10 PM Re: Help with test DB Connection
thepip3r Offline
Hey THIS is FUN
*****

Registered: 2005-03-02
Posts: 350
How is my error checking wrong? Now I'm getting a notice about a missing function call and a missing comma from. I think I can figure things out on my own if someone would just explain where my error checking is lacking so that I can trap my errors so I'll know where my code if failing...

I took into account what you said Shawn and tried to alter my code to the following:

Code:
$objConn 		= DBConnOpen('DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=test;USER=test;PASSWORD='';OPTION=3;')
? "Error @ERROR: @SERROR"
sleep 5
$sql = "INSERT INTO computer(name) VALUES('testcomputer')"

DBExecuteSQL($objConn,$sql)
? "Error @ERROR: @SERROR"
sleep 5

DBConnClose($objConn)
? "Error @ERROR: @SERROR"
sleep 5



but i get that error about missing a comma and it being and invalid method/function call. Also, this message is being reported on line 145 which is my line prefaced with $objConn.


Edited by thepip3r (2005-04-13 05:11 PM)

Top
#137277 - 2005-04-13 05:23 PM Re: Help with test DB Connection
thepip3r Offline
Hey THIS is FUN
*****

Registered: 2005-03-02
Posts: 350
ok... i copied and pasted my $objConn line and the DBConnOpen code to another kix file and get the same error. Can anyone see anything that looks wrong with my $objConn line?
Top
#137278 - 2005-04-13 06:49 PM Re: Help with test DB Connection
maciep Offline
Korg Regular
*****

Registered: 2002-06-14
Posts: 947
Loc: Pittsburgh
I think you misunderstood Shawn. You're not having an issue when you call the DBConnOpen() function, but you have edited or incorrectly copied the function itself.

Look at the function header in your script and look at what it should be

Yours

Quote:


Function DBConnOpen($objConn, optional $ConnTimeout, optional $CmdTimeout)
.
.
.
EndFunction





What it should be

Quote:


Function DBConnOpen($ConnDSN, optional $ConnTimeout, optional $CmdTimeout)
.
.
.
EndFunction





Here's the UDF as it's posted DBConnOpen()

Top
#137279 - 2005-04-13 08:18 PM Re: Help with test DB Connection
thepip3r Offline
Hey THIS is FUN
*****

Registered: 2005-03-02
Posts: 350
no no... i understood Shawn fully. The reason I have the information in $objConn is because I followed Shawn's advice and looked at the DBConnOpen() link. In the commented-out information, the example shows:

Code:
;EXAMPLE       $objConn = DBConnOpen('DRIVER={Microsoft Access Driver (*.mdb)}; UID=; PWD=; DBQ=database.mdb')



so that is what I was using for a basis. and since DBConnOpen()'s other vars are optional, I didn't put anything else. Is my code still wrong?

Top
#137280 - 2005-04-13 08:24 PM Re: Help with test DB Connection
thepip3r Offline
Hey THIS is FUN
*****

Registered: 2005-03-02
Posts: 350
well it seems that your post, maciep, did make me re-look at my code and modify it a little to the following:

Code:
$ConnDSN		= "DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=test;USER=test;PASSWORD='';OPTION=3;"
$objConn = DBConnOpen($ConnDSN)
? "Error @ERROR: @SERROR"
sleep 5
$sql = "INSERT INTO computer(name) VALUES('testcomputer')"

DBExecuteSQL($objConn,$sql)
? "Error @ERROR: @SERROR"
sleep 5

DBConnClose($objConn)
? "Error @ERROR: @SERROR"
sleep 5



and now I get all of my @ERRORs printing out the way I wanted them to in the first place. My first error refrences not having a driver for the DB so I'll look at the previous posts on how to acquire those and install them.

Top
#137281 - 2005-04-13 08:35 PM Re: Help with test DB Connection
Howard Bullock Offline
KiX Supporter
*****

Registered: 2000-09-15
Posts: 5809
Loc: Harrisburg, PA USA
I think your quotes are incorrect on the line. You have two strings butting against each other. I think you want to have single quotes as part of the string. Try:
Code:

$objConn = DBConnOpen("DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=test;USER=test;PASSWORD='';OPTION=3;")




[edit]
I wonder why I could not see the previous post before I posted this response? hmmm...
[/edit]


Edited by Howard Bullock (2005-04-13 08:39 PM)
_________________________
Home page: http://www.kixhelp.com/hb/

Top
Page 1 of 2 12>


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

Who's Online
0 registered and 158 anonymous users online.
Newest Members
Jodey, Hiker, GTRoberts, KatieQuinn, Brennnnn
17684 Registered Users

Generated in 0.045 seconds in which 0.013 seconds were spent on a total of 13 queries. Zlib compression enabled.

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