On Sat, 2005-04-16 at 20:30 +0000, Andrew Douglas Pitonyak wrote:
> Ludovic Coum�tou (aka Coume) wrote:
>
> >Andrew,
> >
> >Any chance to have a look at the macro to do that? Or was it just a
> >teaser?
> >
> >Thanks in advance
> >Ludovic
> >
> >
> >
> Here is how to read data from a Calc document. I assume that the first
> column is an integer and that the second and third columns are text
> data. Things are a bit trickier for dates, which use a special format.
>
> Sub CalcAsDB()
> Dim s As String 'Temporary string.
> Dim sFileName$ 'File name of the Calc document.
> Dim sURLCalc$ 'URL of the Calc document.
> Dim oManager 'Connection driver manager.
> Dim oCon 'Connection object.
> Dim sSQL$ 'SQL that is executed.
> Dim oResult 'Result from an SQL statement.
> Dim oStatement 'A created statement that can execute SQL.
>
> sFileName = "test_data"
> sURLCalc = "file:///andrew0/home/andy/" & sFileName & ".ods"
> oManager = CreateUnoService("com.sun.star.sdbc.DriverManager")
> oCon = oManager.getConnection("sdbc:calc:" & sURLCalc)
> oStatement = oCon.CreateStatement()
> sSQL = "SELECT * FROM """ & sFileName & """"
> oResult = oStatement.executeQuery(sSQL)
> s = s & CHR$(10)
> Do While oResult.next()
> s = s & "ID = " & CStr(oResult.getLong(1)) & _
> " Name = '" & oResult.getString(2) & "'" & _
> " Text = " & oResult.getString(3) & CHR$(10)
> Loop
> oCon.close()
> MsgBox s, 0, "Data from the Calc file"
> End Sub
>
> Connecting to your ODBC database will likely be simple as well. I have
> not made a connection using ODBC. Do you require a password? You could
> use something like the following:
>
> sUser$ = "user"
> sPass$ = "password"
> sURL$ = "sdbc:odbc:data_source_name_here"
>
> oManager = CreateUnoService("com.sun.star.sdbc.DriverManager")
> AppendProperty(oParms(), "user", sUser)
> AppendProperty(oParms(), "password", sPass)
> oCon = oManager.getConnectionWithInfo(sURL, oParms())
>
> Are you able to connect to the ODBC SQL database?
>
Hello,
Ok I finally have time to access the laptop and play around...
So the first part of the script works well, it displays me a window with
the data in my Calc, except the first line.
I I have trouble with the connexion to the mySQL DB :(
===== begin ====
Sub CalcAsDB()
Dim s As String 'Temporary string.
Dim sFileName$ 'File name of the Calc document.
Dim sURLCalc$ 'URL of the Calc document.
Dim oManager 'Connection driver manager.
Dim oCon 'Connection object.
Dim sSQL$ 'SQL that is executed.
Dim oResult 'Result from an SQL statement.
Dim oStatement 'A created statement that can execute SQL.
sFileName = "test_data"
sURLCalc = "/home/lcoumetou/" & sFileName & ".ods"
oManager = CreateUnoService("com.sun.star.sdbc.DriverManager")
oCon = oManager.getConnection("sdbc:calc:" & sURLCalc)
oStatement = oCon.CreateStatement()
sSQL = "SELECT * FROM """ & sFileName & """"
oResult = oStatement.executeQuery(sSQL)
s = s & CHR$(10)
Do While oResult.next()
s = s & "ID = " & CStr(oResult.getLong(1)) & _
" Name = '" & oResult.getString(2) & "'" & _
" Text = " & oResult.getString(3) & CHR$(10)
Loop
oCon.close()
MsgBox s, 0, "Data from the Calc file"
sUser$ = "openoffice"
sPass$ = "xxxxx"
sURL$ = "sdbc:odbc:localhost:3306" <- my mysql db is on my
localhost, I don't know if that's the correct setting
oManager = CreateUnoService("com.mysql.jdbc.Driver") <-
That's the driver user for the connexion ?
AppendProperty(oParms(), "user", sUser) <---- ERROR
AppendProperty(oParms(), "password", sPass)
oCon = oManager.getConnectionWithInfo(sURL, oParms())
End Sub
==== end ====
When I try to run the script, I get an error on the AppendProperty line.
==== begin ====
Basic Runtime error:
Sub-procedure or function procedure not defined
==== end ====
Thanks for helping!
Ludovic
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
