[Solved] Connection to DB on external host

[zwora]

Hi,
I would like to connect to external host from calc and send there some data from the spreadsheet. And I am got stuck on unsuccesfull connection. My code looks as follows:

Code: Select all

Sub send2DB
	Dim oManager     'Connection driver manager.
	Dim oCon         'Connection object.
	Dim oResult      'Result from an SQL statement.
	Dim oStatement   'A created statement that can execute SQL.
	Dim sDatabaseName$
	Dim sUserName$
	Dim sUserPassword$
	Dim sDatabaseHost$
	Dim sURL$
	Dim sSQL$
	Dim oParams() As New com.sun.star.beans.PropertyValue

	sDatabaseHost = "hostname.pl:3306/"
	sDatabaseName = "dbname"
	sUserName = "usrname"
	sUserPassword = "passwd"
	oManager = CreateUnoService("com.sun.star.sdbc.DriverManager")
	
	AppendToPropertyArray(oParams(),"user",UserName)
	AppendToPropertyArray(oParams(),"password",sUserPassword)
	AppendToPropertyArray(oParams(),"JavaDriverClass","com.mysql.jdbc.Driver")
	
	sURL = "sdbc:mysqlc:://" & sDatabaseHost & sDatabaseName
	oCon = oManager.getConnectionwithInfo(sURL,oParams())
	oStatement = oCon.CreateStatement()
End Sub

Running macro gives the prompt: "Object variable is not set" and the line oStatement = oCon.CreateStatement() is marked. Probably no connection was established, but is there any way to check the reason? The connection data should be fine, as I can connect using them in different way (i.e. from command window typing: mysql -h hostname.pl -u usrname -p)

Any help will be appreciated

[Villeroy]

Database connections are bound to so called "database documents". They are configuration files actually.
File>New>Database...
[x] Connect to existing database
Type: MySQL (that one uses the built-in sdbc driver, choose JDBC if you want to use a Java driver, choose ODBC if you prefer a preconfigured ODBC source)
Specify the MySQL host or the jdbc-URL or the ODBC name respectively.
[x] Register the database (useful in most cases)
Save the database document.

Now you have dozends of options to use this database connection with the Base document and with the rest of the office suite. Most macros are unproductive bullshit. Document templates, pivot tables, import ranges in spreadsheets, forms, reports, serial letters or simple copy/paste operations do not require a single line of stupid Basic code.

[zwora]

Thank you for the answer. I've done what you'd adviced and I get the proper connection.

And now I would like to send value of some felds as the new record in database by pressing the button. What I should put in such a macro? Should I get the connection first (the database is just registered)?

[Villeroy]

Base is a bridge between databases and office documents.
Filling up some database programatically is something where you do not need any office suite at all (and no dull language called "StarBasic" in particular).
Use a proper programming language or a CLI tool for MySQL.
Choose an appropriate driver.
Connect to the database and run SQL statements such as INSERT INTO "MyTable" VALUES('abc', 23, {d '2018-10-07'}) [or whatever syntax is understood by your MySQL driver]

If you want to enter some user input from a local keyboard into your database, then your office suite is one way to perform the task. Create a form for new records only, add a push button with property action="new record" and another push button with action "undo data entry". No macro required. You can even create such input forms on stand-alone text documents, spreadsheets and drawing sheets.

[zwora]

I maybe explain what I really would like to do.

I've got a web application (web 2.0) that holds all the data in DB (these are details of orders). But I also use OO Calc to generate price evaluations, agreements and invoices - this is quicker, because every product is of different type and has got different number of features and different pricelist. At present new orders are typed by hand in web application. But many of these data are already in calc files and would be great to send them by pushing some button.

So I don't need to use the Calc as the DB client, just I need to withdraw some data from spreadsheets and send them to DB. I can use other macro language (I would prefer JS, but eventually python also would be ok). But I need just the example how to send the content of e.g. cell B1 to DB. I am familiar with SQL, but don't know how to access particular cell in query. Probably I would need to concatanate query with some variable representing value from cell I'd like to send (similarly to php scripts).

[Villeroy]

Data transfer from spreadsheet cells into databases is very difficult and most programmers do not understand spreadsheets.
Data types are one problem. Calc knows floating point numbers and text. MySQL knows a dozend of different types.

It is possible to copy and paste spreadsheet cells to database tables.
Copy rows of sheet cells with or without column labels.
Select the icon of a database table and paste.
A dialog pops up.
Choose "Append data" and if your clipboard data contain column labels or not.
In the second step, you can map the clipboard fields to the existing table fields.
If you arrange your spreadsheet links to reflect the right column order then you can skip the second step.

[zwora]

The problem is that adding to DB should not be visible to user. So I would need to do everything by a macro and not by using a menu. I've got alternative solution which is sending data to csv files and then retreiving them from the web application and sending to DB. This I should do without problems, but requires a bit of work. I thought that sending to DB from macro is easier and wanted to start from this.

[Villeroy]

A generic macro solution to this problem requires a full weekend of coding.
Anything fairly simple requires detailed knowledge about your database.
Nevertheless, macro coding for Base is the most complex stuff in this whole office suite.

[Villeroy]

Having a connection object to your database, you may be able to do something like this:
oStatement = oConnection.createStatement()
oStatement.executeUpdate(sql)

where sql is some concatenation in MySQL syntax.
Base provides a little test box in Tools>SQL...

[Villeroy]

Short note on syntax and data types:

SQL decimals always require a point as decimal separator whereas cStr(cell.getValue) or cell.getString() may return a comma according to your office locale.
Lenghts of strings must not exceed the alowed lengths in your db fields.
Dates and times are extremely complicated to handle in Calc, SQL, UNO and macro languages because they all use completely different data types to represent dates and times. I wrote a Basic function which converts all types of dates, times and date-times that may occur in this office suite: viewtopic.php?f=74&t=82181
SQL requires ISO strings for literal dates, times and time stamps; spreadsheets store everything as serial day numbers and fractions of day numbers (double-precision floating point numbers). My helper function allows you to do the following conversion: myISO = convertDateTime(cell_value, "ISO") or if you use methods of the UNO API instead of SQL: myStruct = convertDateTime(cell_value, "UNODATE")

I know nothing about time zones in MySQL which may impose another challenge. I don't know.

[zwora]

So it seems that going through csv files will be the most convenient way to do my job. It's got another asset - user of file cannot see the content of whole database (with database registered on OO it has). THank you very much for your help. You saved me a lot of time.

[Villeroy]

MySQL handles csv files. You need valid SQL statements to link the right csv file and then update the database from this linked source. I don't know if this is convenient and I don't know if "convenient" is a valid category. I do this quite frequently using the above outlined clipboard method. For me this is the most convenient method. It simply works as designed, converts data types correctly while raising comprehensible errors in case of invalid data.

Another variant without database window.
1) Hit F4 and double-click the name of your registered database
2) Select the sheet cells and drag them onto the "Tables" icon.

Enter the name of the target table and choose "Append data".
Unfortunately you can't drop data on the icon of a table here.