| thepip3r |
| (Hey THIS is FUN) |
| 2005-04-06 07:31 PM |
Help with test DB Connection
|
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
| Howard Bullock |
| (KiX Supporter) |
| 2005-04-06 08:30 PM |
|
|
Re: Help with test DB Connection
|
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
| thepip3r |
| (Hey THIS is FUN) |
| 2005-04-07 06:47 PM |
|
Re: Help with test DB Connection
|
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)
Shawn
|
| (KiX Supporter) |
| 2005-04-07 06:52 PM |
|
|
Re: Help with test DB Connection
|
Got to ask this question first. Did you download and install the "MySQL ODBC 3.51 Driver" driver from MySQL ?
| thepip3r |
| (Hey THIS is FUN) |
| 2005-04-07 07:52 PM |
Re: Help with test DB Connection
|
No but wouldn't the script have returned an error there was a problem?
|
Shawn
|
| (KiX Supporter) |
| 2005-04-07 08:12 PM |
|
|
|
Re: Help with test DB Connection
|
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
| Jose |
| (Seasoned Scripter) |
| 2005-04-07 08:41 PM |
|
|
Re: Help with test DB Connection
|
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.
| thepip3r |
| (Hey THIS is FUN) |
| 2005-04-07 09:22 PM |
|
Re: Help with test DB Connection
|
Thanx guys... I'll give that shot.
|
Shawn
|
| (KiX Supporter) |
| 2005-04-07 09:27 PM |
|
|
|
Re: Help with test DB Connection
|
Read this thread. Should be very helpfull to you ...
ODBC - DBcommand
-Shawn
| thepip3r |
| (Hey THIS is FUN) |
| 2005-04-12 06:07 PM |
|
Re: Help with test DB Connection
|
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| thepip3r |
| (Hey THIS is FUN) |
| 2005-04-12 06:55 PM |
|
Re: Help with test DB Connection
|
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?
|
Shawn
|
| (KiX Supporter) |
| 2005-04-12 07:11 PM |
|
|
|
Re: Help with test DB Connection
|
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
| thepip3r |
| (Hey THIS is FUN) |
| 2005-04-12 07:22 PM |
|
Re: Help with test DB Connection
|
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...
|
Shawn
|
| (KiX Supporter) |
| 2005-04-12 07:47 PM |
|
|
|
Re: Help with test DB Connection
|
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
| thepip3r |
| (Hey THIS is FUN) |
| 2005-04-13 05:10 PM |
|
Re: Help with test DB Connection
|
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 5but 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.
| thepip3r |
| (Hey THIS is FUN) |
| 2005-04-13 05:23 PM |
|
Re: Help with test DB Connection
|
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?
| maciep |
| (Korg Regular) |
| 2005-04-13 06:49 PM |
|
Re: Help with test DB Connection
|
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()
| thepip3r |
| (Hey THIS is FUN) |
| 2005-04-13 08:18 PM |
|
Re: Help with test DB Connection
|
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?
| thepip3r |
| (Hey THIS is FUN) |
| 2005-04-13 08:24 PM |
|
Re: Help with test DB Connection
|
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 5and 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.
| Howard Bullock |
| (KiX Supporter) |
| 2005-04-13 08:35 PM |
|
|
|
Re: Help with test DB Connection
|
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]
| maciep |
| (Korg Regular) |
| 2005-04-13 11:00 PM |
|
Re: Help with test DB Connection
|
I think you're still missing the point. Shawn (and I) are talking about the actual function itself. You have the parameters in the function itself incorrectly named.
Not here
Code:
$ConnDSN = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=test;USER=test;PASSWORD='';OPTION=3;"
But here
Code:
Function DBConnOpen($objConn, optional $ConnTimeout, optional $CmdTimeout)
I'm not sure how else to explain it. Post your entire script again so that we're all on the same page.
| thepip3r |
| (Hey THIS is FUN) |
| 2005-04-13 11:53 PM |
|
Re: Help with test DB Connection
|
Well I just wanted to let you all know that once I downloaded the MySQL ODBC driver, the script worked great for my test information. A big, "thank you" to all who continually helped me through this process...
| thepip3r |
| (Hey THIS is FUN) |
| 2005-04-14 10:45 PM |
|
Re: Help with test DB Connection
|
Ok, the problem with $objConn was with calling it as the wrong var name in one of the UDFs. i was using $objConn instead of $ConnDSN which the UDF wanted.
| maciep |
| (Korg Regular) |
| 2005-04-14 10:49 PM |
|
Re: Help with test DB Connection
|
So you understand what we were trying to tell you now? That's good.
In general, there's no need to ever make changes to a udf provided here, especially if you're new to UDFs. They are built to work as provided.