Execute SQL query in Basic (for Calc) over MySQL DB connection already set

[tharos]

Hello!

To give you some context: I’ve asked a couple of questions that got me to this point. If you would like to see that context you can check my questions (the only I’ve asked so far)

I have a spreadsheet that, so far, has 1 form. In that form I have (3) comboboxes which are populated from MySQL tables. I set that MySQL DB connection via Base. Those combo’s are correctly populated.

I know I’ve said I would preferably not use any programming but changed my mind.
So, lets say combo2 needs to be updated when combo1 changes. So I made use of event handling and when combo1 changes its value I need to run a select query against MySQL IN Basic. I have everything set excepting that I don’t know how to use the active connection neither how to run a query and get that resultset to populate combo2.

Thanks for your time.

[Villeroy]

A combo box is just a text box with auto-complete. You can type any text into a combo box and the text will be written to the underlying field.
Most users want listboxes. A listbox lets you select one particular text and writes the corresponding primary key into the form’s foreign key field.
Cascading list boxes with and without macros
[Tutorial] Cascading Listboxes with macros

[tharos]

Okay, I understand and will go around it. However I still need an answer to the question.

[Villeroy]

Tutorial on combo and list boxes: Apache OpenOffice Community Forum - Combo vs List Box - (View topic)
What you actually need depends entirely on your database. If the database column in question has mostly repetetive text, you may want to work with a combo box instead of a flat input box. This is analog to Calc’s AutoComplete feature.
If your database column is a foreign key (some kind of identifier) related to another table’s primary key (unique items of that other table), then you definitively need a listbox.

[tharos]

Ok, I understood very well your point on Combo vs List on your first answer.

How do I run an SQL query in Basic and get the resultset?

Example: when list1 changes its value, I need to run this query:

select concept from concepts where opID = 'list1.value'

[Villeroy]

Assign the SQL to the ListSource property of the listbox model and refresh the listbox.

[tharos]

I appreciate your time and point outs, however, I want to do it in Basic. What if I want to call a Stored Procedure?

[Villeroy]

I’m talking about Basic or any other macro language and Apache OpenOffice Community Forum - [Tutorial] Cascading Listboxes with macros - (View topic) does exactly that.

[mikekaganski]

It would be useful to have your sample ODB, to be able to play with it and suggest something. E.g., I don’t feel like creating a sample database, table, form, two boxes, set up events, simply to arrive at the stage where you are; and only then start looking which properties of the form or a box point to the connection that may be used in a call, or how to access these objects from the event.