Page 1 of 1
Connecting MySQL on the fly
Posted: Tue Jun 09, 2009 11:13 am
by ooo labaye
Hello,
I'm a newbie in the oobasic script and I supose that the following question is elementary but I dit not find the good answer on the WEB.
I'm looking for informations about how to connect to a MySQL database (some snipet, ...)
I'm wondering if this is possible without using a registered odb file.
It would be so fine if someone can help me to find the good way.
thanks in advance
Re: Connecting MySQL on the fly
Posted: Tue Jun 09, 2009 12:24 pm
by Villeroy
What's wrong with the Base file? The Base file is nothing but configuration until you add queries, forms and reports. Of course you can write your own database application (not in Basic), reading from MySQL and dumping XML directly into ODF documents or passing it over to the UNO-services of a listening office process. But why?
P.S. The web is full of database related code snippets.
Re: Connecting MySQL on the fly
Posted: Tue Jun 09, 2009 5:10 pm
by ooo labaye
CONTEXT
I need to adapt the data before doing some printing.
I hope to print EAN13 codebar and I have an EAN13 font and a macro to compute the good string with an item key.
But I can't do that during the printing. It seems to be hard (or maybe impossible) to add a function to transform the data from a database during printing.
A solution was to include the result string for each row in a temporary table or something like this.
To do the job, and to avoid to the final user a too complex manipulation, I try to add a script at the opening of the document that compute the good EAN13 code.
And I try the following code (under win XP) :
Code: Select all
Sub TDCConnect
Dim oDBContext As Object, oDataSource As Object
Dim sName As String, oConnexion As Object
Dim oStatement As Object, oRS as Object
Dim oStatement1 As Object
Dim flatProp(0) As New com.sun.star.beans.PropertyValue
Dim SQL as string, ARTCODE As String , EAN13bc As String
oDBContext = CreateUnoService("com.sun.star.comp.sdbc.ado.ODriver")
sName = "sdbc:ado:" +_
"Provider=MSDASQL.1;Persist Security Info=True" +_
";Extended Properties=""DSN=t11;SERVER=XXX;UID=XXX;PWD=XXX;DATABASE=XXX;PORT=3306""" +_
";Initial Catalog=XXX"
'
' Alternative to ADO is ODBC
' oDBContext = CreateUnoService("com.sun.star.comp.sdbc.ODBCDriver")
' sName = "sdbc:odbc:XXX"
oConnexion = oDBContext.connect(sName, flatProp())
' Select empty EAN13 codes from table
SQL = "SELECT * FROM tdc.suiviprix s WHERE EAN13="""""
oStatement = oConnexion.CreateStatement()
oStatement1 = oConnexion.CreateStatement()
oRS=oStatement.executeQuery(SQL)
If Not IsNull(oRS) Then
While oRS.next
' MsgBox oRS.getString(1) & " / " & oRS.getString(2)
' MsgBox oRS.getString(oRS.findColumn("ARTCODE")) & " / " & oRS.getDouble(oRS.findColumn("PRIXTVAC"))
ARTCODE = oRS.getString(oRS.findColumn("ARTCODE"))
' Compute the EAN13 code
EAN13bc = ean13(Left(ARTCODE,12))
' If the EAN13 code is OK than update the field in the database
IF LEN(EAN13bc) > 0 THEN
SQL = "UPDATE tdc.suiviprix SET EAN13=""" + EAN13bc + """ WHERE ARTCODE=""" + ARTCODE + """;"
oStatement1.executeQuery(SQL)
END IF
Wend
End If
oConnexion.close
oConnexion.dispose
End Sub
After that code, I have a column in a table of the database that I can use in a mailing to print stickers.
Any suggestion to simplify the solution is well come.