#137262 - 2005-04-06 07:31 PM
Help with test DB Connection
|
thepip3r
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
KiX Supporter
Registered: 2000-09-15
Posts: 5809
Loc: Harrisburg, PA USA
|
|
Top
|
|
|
|
#137264 - 2005-04-07 06:47 PM
Re: Help with test DB Connection
|
thepip3r
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
|
|
|
|
#137267 - 2005-04-07 08:12 PM
Re: Help with test DB Connection
|
Shawn
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
|
|
|
|
#137270 - 2005-04-07 09:27 PM
Re: Help with test DB Connection
|
Shawn
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
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
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
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
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
|
|
|
|
#137276 - 2005-04-13 05:10 PM
Re: Help with test DB Connection
|
thepip3r
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
|
|
|
|
#137278 - 2005-04-13 06:49 PM
Re: Help with test DB Connection
|
maciep
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
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
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
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)
|
Top
|
|
|
|
Moderator: Jochen, Allen, Radimus, Glenn Barnas, ShaneEP, Ruud van Velsen, Arend_, Mart
|
2 registered
(morganw, mole)
and 414 anonymous users online.
|
|
|