Is it possible to output cell values directly to base or MYSQL with macro

[Neil-B]

Hi All

I have a Calc spread sheet which i use for my invoicing, on that I have a "save"button that as well as saves the invoice to the appropriate folder it also opens a text file with the next invoice number with open for input to read the invoice number to apply’ it then updates the file with open for output and saves the new invoice number for next time.

is it possible to use something like “open for output” to a base file or even better a MYSQL database as part of the save macro

I am only looking to save things like the invoice date for instance (A1) and invoice amount (B2)

I have a base file connected to MSQL for this information but i have to manually input it after i save the invoice which is prone to errors

If someone could point me in the right direction I would much appreciate the help

Many thanks Neil

[Ratslinger]

This routine works from any DB to another (utilizing Base) or from Calc to Base attached to MySQL (all tested):

Sub UpdateSQL
    Dim DATABASE_NAME
    Dim Context as Object
    Dim Db as Object
    Dim Conn as Object
    Dim Stmt as Object
    Dim strSQL as String

    rem Run an SQL command on a table in LibreOffice Base
    Context = CreateUnoService("com.sun.star.sdb.DatabaseContext")

    rem URL of database (it is also possible to use a registered DB name)
    DATABASE_NAME = "file:///home/YOUR-DIRECTORY/YOUR-FILE.odb"
    Db = Context.getByName(DATABASE_NAME)

    rem connect to the database
    Conn = Db.getConnection("","") 

    rem create statement object
    Stmt = Conn.createStatement()

    rem compose SQL statement, retrieve your data from sheet and insert your data
    rem Example "Insert" statement; construct as appropriate or create "Update" statement
    strSQL = "INSERT INTO YOUR-TABLE (NAME,NOTE) VALUES ('Input 95','New Record91');"

    rem execute SQL statement
    Stmt.executeUpdate(strSQL)

    rem close the database connection
    Conn.close()

    rem notify the user
    msgbox("table updated")
End Sub

As noted in the remarks, what and how (fields, Insert/Update) are up to you as well as where you get the data from.

[Ratslinger]

Should have mentioned: prior to Conn = Db.getConnection("","") you would want a prompt for user & password (or hardcode if wanted) to end up with Conn = Db.getConnection(MYSQLID, MYSQLPW)

[Neil-B]

Hi Ratslinger,

Thank you for your answer, that looks like what i am looking for, I will give it a try in the next day or so.

Many thanks Neil

Update,

I have "Rem"ed out the compose the sql statement section, mainly because I haven’t a clue how to form the statement in the first place, Here comes another learning curve.

but i it seems to connect with my data base fine, so again many thanks for you help

Neil