Page 1 of 2
[Solved] Need help with another tricky query
Posted: Tue Aug 23, 2011 2:58 am
by erf9201
Hi guys,
I need your help once more:
Code: Select all
ID Section Description
1 1 Red
2 1 White
3 1 Orange
4 1 Blue
5 2 Tan
6 2 Yellow
7 2 Red
... ... ...
I want to create a separate form so that when I go through my records and, say, change color field in record #7 to, say, Black, I want (on update or either separate button?) to have form search and replace other records that have the same color. In this case - also record #1. Also, if possible, I want to have it display number of changes. I.e - 2 in this case.
So that the main table looks like this:
Code: Select all
1 1 Black
2 1 White
3 1 Orange
4 1 Blue
5 2 Tan
6 2 Yellow
7 2 Black
... ... ...
I'm sorry if this has an obvious solution but Im a former Access person so Base is still somewhat confusing to me.
Thank you for your help.
Re: Need help with another tricky query
Posted: Tue Aug 23, 2011 6:23 pm
by rudolfo
If as you described in your example record #7 and #1 always change the color synchronously then you have redundancy in your table model. Relational database should follow the normalisation rules.
In your case create an addition table
color_group or a similar name.
Code: Select all
group_id | color
---------+-------
1 | Red
2 | White
3 | Orange
4 | Blue
5 | Tan
6 | Yellow
and a modified main table
ID | Section | color_group_id
-----+---------+---------------
1 | 1 | 1
2 | 1 | 2
3 | 1 | 3
4 | 1 | 4
5 | 2 | 5
6 | 2 | 6
7 | 2 | 1
Now when you want to change a color you simply change the record in the color_group table with the ID 1.
The equivalent result set as your original table would then be based on a view:
Code: Select all
SELECT main.id, main.section, color_group.color FROM main, color_group
WHERE main.color_group_id = color_group.group_id
(Note this is SQL standard, BASE might not accept this because it needs quotes around table and column names.)
Typically looking only at numbers in the main table makes it less human readable. So instead of the numerical group_id in the color_group table you can also have a string column with a more talking, but unique name. Obviously you shouldn't name it "red1" because this leads to a lot of irritation if you change the color to black or a few weeks later to green.
Re: Need help with another tricky query
Posted: Tue Aug 23, 2011 7:53 pm
by erf9201
Yes, thank you for your advice. But this is not what I was asking.
I was trying to figure out how to change values based on a query. And at the same time I was asking how to do it through the standard Base form.
I.e in my form there is a box called "Color". When I browse through the records I want to be able to simply change the value in that box (as you can usually do). But I want it to update all similar records somehow as well.
And this is what I dont understand how to do.
I.e how can I make a button next to the box "Color" so that it calls that replace query. And how exactly I do such a query.
I can easily make a query that looks up all records with the value "Red", but I do not understand what do I need to do next to replace those records with the new value.
Re: Need help with another tricky query
Posted: Tue Aug 23, 2011 9:20 pm
by erf9201
Here is a screenshot of the sample database im trying to do:
- Sample
What I need is when I press the "Update Description" button,it calls the query that replaces all records where Description=Red to whatever is entered in the field.
How do I make such a query ? And how do I link it to that button?
Re: Need help with another tricky query
Posted: Wed Aug 24, 2011 11:32 am
by rudolfo
I know that my answer was not what you were asking for. But OpenOffice requires much more than MS Office that the users follow certain habits. MS Word lets you do all (stupid) direct formatting things while you ran against walls pretty soon if you don't use styles in Writer. Similarly MS Access allows you to do quick and dirty modifications through GUI supported VBA macros. Not with OOo Base. Conceptionally it is a bridge or frontend to different database backend so it needs to find some common ground for all backends (server based engines like MySQL, MS SQL-Server, Oracle,... and file based databases like SQLite, HDBSQL, dBase..) and these are the techniques offered by Standard SQL. One of them are the principals of normalization. I can only recommend that you try to adjust to the OOo Base way of doing it otherwise you will run from one question into the next one.
Despite of this warning you can try to do it like you would have done it in MS Access. You need to assign a macro to the button's execute event.
1) Write a macro that uses an event object as parameter:
Code: Select all
Sub btnClick_updateColors(oEvent As Object)
Dim oForm As Object
Dim oCon As Object, oStmt As Object
Dim oDescriptionCtrl As Object
' Retrieve the current connection from the event and form
oForm = oEvent.Source.Model.getParent()
oCon = oForm.ActiveConnection
oDescriptionCtrl = oForm.getByName("... Name of your description control needed ...")
'oDescriptionCtrl.Text has the value of color/description field
oStmt = oCon.prepareStatement("UPDATE .... ?")
oStmt.setString(1, oDescriptionCtrl.Text)
oStmt.executeUpdate()
oStmt.close()
2) Right click on the button, choose "Control ..." and select the "Events" tab and assign your new macro to the "Execute action"
Now the problem is the UPDATE statement that you want to run: It should be something like
UPDATE ... SET description =
:new_color WHERE description =
:old_color
But the problem is that in your form you have either the new color or the old color, but you need both parameters to run the statement in the macro code.
And once again: If you haven't used a macro programming language before, don't go this path.
Re: Need help with another tricky query
Posted: Wed Aug 24, 2011 8:59 pm
by erf9201
No, rudolfo, macros not going to cut it. I need a query. Thanks for your help though.
I looked through forums and I think I sorta outlined the possible way to solve this problem.
1) I need to create a query that would replace records.
2) I think SELECT REPLACE WHERE would do that trick.
3) However, I dont understand how to pass current record to the query on a button press, and also how to address the curent record box. Is it "=:Description" or something?
I simply do not believe that no one can help me with this problem...
Its basic database functions...
Re: Need help with another tricky query
Posted: Wed Aug 24, 2011 9:48 pm
by rudolfo
erf9201 wrote:No, rudolfo, macros not going to cut it. I need a query.
[...]
3) However, I dont understand how to pass current record to the query on a button press, and also how to address the curent record box. Is it "=:Description" or something?
Now, I might be wrong in this, but I am pretty sure about it that "button press" requires a macro. It is surely a event, so you have to configure it on that second tab page of the Controls properties, as I have pointed out in the last post. I think you can assign hyperlinks to such an event which will cause the form or OpenOffice to follow the link and open the referenced document or resource. But in most cases you assign a macro to a button pressed event. So, yes you need a "query", but surely you will also need a macro to wrap this SQL statement into a functional interface that can be used together with a button.
Your question about "the current record box" is answered in my sample code. It is
form.getByName("...").Text. The name of a form control is usually something like btnForm1 or btnDescription2 if you used the wizard to create your form in the first stage. In short a right click on the Button for the "Control..." Properties will tell you what you need to know. It is the very first field in the "General" tab.
The
oStmt.setString(1,...) is the way to pass parameters to a SQL statement (for placeholders that were before identified with a ? - a literal question mark!) If you have worked with JDBC, ODBC or Perl's DBI .. this is how it is done nearly anywhere.
Also if you have questions about the code, pick out one significant key word and run a search on the forum with it. Probably the fastest way to get further.
Re: Need help with another tricky query
Posted: Wed Aug 24, 2011 10:29 pm
by erf9201
rudolfo,
It seems that its possible to use SQL commands (or queries) if you assign the button to the subform of the main form. I tried it and it works.
However, I still cannot link the button the the query in a way that I need it to be linked
I do understand your solution and I appreciate your help, but I ... simply refuse to use macro code. Sorry.
Re: Need help with another tricky query
Posted: Thu Aug 25, 2011 11:55 am
by rudolfo
It is a good guideline to try to find a solution without macros. But I think in this situation you won't get around macros.
You want to run a update statement with a field of the current record as paramater. Although Base allows queries (here I mean the queries that you can see on the left pain between tables and forms) with parameters, these parameters will always cause a visual parameter request to the user.
In short I can only offer you a solution based on macros. And actually it won't be a solution, because there is still the open question how to retrieve both, the old and the new value for the replace operation of the color.
So I leave you alone on this. Maybe you have too many pre-conditions to that you stick.
* I want to keep my table structure as it is and don't follow normalization rules.
* I don't want to use macros.
Re: Need help with another tricky query
Posted: Thu Aug 25, 2011 5:27 pm
by erf9201
Sorry, rudolfo. I did not mean to be rude on unappreciative of your help. I'm just trying to get necessary information that usually should be a part of the documentation for the software.
Can I ask you a more specific question:
Is there any way to somehow pass current record parameter to the SQL query. I think this may help me to solve my problem.
I was thinking about adding a second input box and a "fake" table with 1 record - this should solve the "old/new value" issue. All I need is to be able to tell the SQL which "old" record it needs to use as a search criteria.
You think maybe at least this can be done?
Thanks for your help and time.
Re: Need help with another tricky query
Posted: Sun Aug 28, 2011 9:46 pm
by rudolfo
If you have a query with parameters you can either pass the parameters as user when you are asked for them or you can use macro code to convert the query into a preparedStatement and use the setString() and setLong() methods that I mentioned before to fill the parameters with specified values.
So here is how this works if you have a stored query with the name "MyQuery":
Code: Select all
sParam = "matches this"
nParam = 2
oActiveConnection = ThisComponent.CurrentController.ActiveConnection
oStmt = oActiveConnection.prepareCommand("MyQuery",com.sun.star.sdb.QUERY);
' prepareCommand returns a prepared statement handle so from here on the code
' is the same that you use after oStmt = oCon.prepareStatement("select ... from ...")
oStmt.setString(1, sParam)
oStmt.setLong(2, nParam)
oResult = oStmt.executeQuery()
In general I don't see the point for the
prepareCommand method. Because instead of writing the name of stored Query you could as well write the query statement itself and use
prepareStatement. That's maybe 50 keystrokes that you have to type more. But after all you are in a macro and if you see the statement directly there you save some keystrokes that you would have needed to comment the string "MyQuery" that doesn't speak for himself in contrary to a literal select statement.
Re: Need help with another tricky query
Posted: Mon Aug 29, 2011 10:14 am
by DACM
erf9201 wrote:Is there any way to somehow pass current record parameter to the SQL query. I think this may help me to solve my problem...adding a second input box and a "fake" table with 1 record...All I need is to be able to tell the SQL which "old" record it needs to use as a search criteria.
Yes, you can certainly use a
SubForm based on a parameter-query to search/filter a Table using input from a Text-Box(es) on a Form. That's not the problem. The problem would then be storing and running an UPDATE query without utilizing a Macro.
rudolfo's done a great job outlining the options, so perhaps a
list of Base query limitations will prove helpful. I'm certainly open to corrections but as I understand it:
(1) The Base "SQL parser" is
a work in progress. It's currently limited to:
- (a) Single SELECT queries (no UNION queries) that return a result-set.
(b) Multiple SELECTs are allowed if embedded within a sub-query expression.
(c) No INSERT, UPDATE, DELETE, CREATE, ALTER, DROP or other data/structure changing commands are allowed
(2) Bypassing the Base parser has it own limitations (using
Tools>SQL...;
Direct-SQL toggle; or
SQL [Native] list box):
- (a) Read-only result-sets, even if the query is saved as a View
(b) Parameter queries no longer work since they're a function of the Base parser
(c) Saved queries can be run in-place if they produce a result-set (SELECT or CALL only)
(d) INSERT, UPDATE, DELETE, CREATE, ALTER, DROP or other data/structure changing commands may be saved as a "Native" Query, but they can't be run in-place. So they must be copied-and-pasted to Tools>SQL... (which has no re-entry memory). And because they exist but won't run in-place you may experience extraneous popups while using Base.
Re: Need help with another tricky query
Posted: Mon Aug 29, 2011 4:59 pm
by erf9201
2 rudolfo:
Ok. I think you have convinced me. But please understand that I was not asking for the impossible - I was just trying to figure out if I can use the GUI to program what I need. I mean, what is the point of having it if you can't use it?
Now, having said that... I do see your point now, because I did try to implement my own solution over the weekend, but apparently it did not work because of the limitations (akwardness?) of the above mentioned GUI.
I really really appreciate your help, but please give me a few days to try it out so that I can report the results.
2 DACM:
Yes, I have noticed the these limitations. I can only hope that Base eventually will be brought up to speed on this one...
P.S> I need to update my signature as I'm running HSQLDB...
Re: Need help with another tricky query
Posted: Tue Aug 30, 2011 3:14 pm
by rudolfo
That's fine with me. I had to go similar ways myself when I made my first steps with Base.
Maybe you have a look into the two pdf documents by Roberto Benitez listed at the bottom of
this page: "OOo Basic Db Development" and "Forms and Dialogs". They give a good introduction what you can achieve with Forms and for which areas you require macros to get things done effectively.
Regarding your signature: Are you really using HSQLDB 2.2 together with OpenOffice? As far as I understood not even LibreOffice 3.4 includes a newer version of HSQLDB than 1.8 for the embedded database. Might be different if you use JDBC to connect to an external stand alone HSQLDB server.
Re: Need help with another tricky query
Posted: Tue Aug 30, 2011 5:09 pm
by erf9201
Yes, you can run HSQLDB 2.2 with some...hmm... trickery
I needed some standard SQL functions so I had to switch - see this thread for details:
http://user.services.openoffice.org/en/ ... 55#p193655
And thanks for these nice PDFs.
Re: Need help with another tricky query
Posted: Wed Aug 31, 2011 12:58 am
by DACM
erf9201 wrote:Yes...HSQLDB 2.2...I needed some standard SQL functions so I had to switch.
OH...Then...
In that case, you do have another option:
TRIGGERS
You can write an SQL procedure, store it in the HSQLDB 2.x database, and CREATE a TRIGGER to run the procedure BEFORE INSERT ON the DESCRIPTION field of your database. This will then run upon 'color' change whether using a Form or otherwise.
See the
documentation and
sliderule's example for additional details
.
Re: Need help with another tricky query
Posted: Thu Sep 01, 2011 4:21 am
by erf9201
Ok, I did come up with this macro (thanx rudolfo):
Code: Select all
REM ***** BASIC *****
Sub btnClick_updateColors(oEvent As Object)
Dim oForm As Object
Dim oCon As Object, oStmt As Object
Dim oDescriptionCtrlNew As Object
Dim oDescriptionCtrlOld As Object
' Retrieve the current connection from the event and form
oForm = oEvent.Source.Model.getParent()
oCon = oForm.ActiveConnection
oDescriptionCtrlNew = oForm.getByName("NewValue")
'oDescriptionCtrlNew.Text has the value of an extra textbox that is populated with some new value
oDescriptionCtrlOld = oForm.getByName("OldValue")
'oDescriptionCtrlOld.Text has the value of color/description field
oStmt = oCon.prepareStatement(" UPDATE Data SET Description='?' WHERE Description='?' ")
oStmt.setString(1, oDescriptionCtrlNew.Text)
oStmt.setString(2, oDescriptionCtrlOld.Text)
oStmt.executeUpdate()
oStmt.close()
End Sub
But it gives me an error that "parameter index is out of range 1". Any suggestions?
Re: Need help with another tricky query
Posted: Fri Sep 02, 2011 12:21 am
by rudolfo
The questionmarks in
prepareStatement mustn't be quoted. It will always be a simple question mark no matter if the field that it is refering to is a numerical type or a string type.
Code: Select all
oStmt = oCon.prepareStatement(" UPDATE Data SET Description=? WHERE Description=? ")
That's the same as in all other languages using the question mark placeholder (JDBC,ODBC,...)
Re: Need help with another tricky query
Posted: Fri Sep 02, 2011 12:38 am
by erf9201
I removed the quotes and now I get a different error: "Property or method not found: Text".
Errors also occur if I change "Text" to "CurrentValue" or "Value". I'm not sure what is wrong here...
P.S.> Here is how my updated form looks like now:
Re: Need help with another tricky query
Posted: Fri Sep 02, 2011 2:23 am
by Sliderule
Sorry to add my thoughts here, but, you might what to try the following code:
Code: Select all
oStmt = oCon.prepareStatement(" UPDATE ""Data"" SET ""Description""=? WHERE ""Description""=? ")
: You are using StarBasic as your programming language. With StarBasic, a string is denoted by surrounding it with double quotes . . . for example:
BUT, you have choosen to use MIXED case table and field names for your database definition. For example:
Data . Since, with the HSQL database engine, table and field names must MATCH exactly, including CASE ( UPPER / Mixed / lower ) . . . AND . . . the HSQL DB will automatically change to UPPER CASE any names NOT surrounded by double quotes . . . for example . . .
DATA . . and . . . since
Data is NOT the same as
DATA . . . you MUST surround it in the SQL code with double quotes.
One more BUT . . . StarBasic needs to know that your table and field names really should be surrounded by the double quotes, so, the syntax is to use TWO ( 2 ) double quotes next to each other withOUT spaces . . . like . . .
I hope this helps, please be sure to let me / us know.
Sliderule
Thanks to add
[Solved] in your
1st post Subject (edit button top right) if this issue has been resolved.
Re: Need help with another tricky query
Posted: Fri Sep 02, 2011 2:37 am
by erf9201
Thanks for your thoughts, Sliderule, but I do not think that it is causing the error.
I tried your code and I still get "Property or method not found: Text" at line
Code: Select all
oStmt.setString(2, oDescriptionCtrlOld.Text)
Re: Need help with another tricky query
Posted: Fri Sep 02, 2011 2:52 am
by Sliderule
How about trying to 'string together' the actual UPDATE SQL that you want, from the variables you said contains the values you want.
For example:
Code: Select all
sSQLString = "UPDATE ""Data"" SET ""Description""='" + oDescriptionCtrlNew.Text + "' WHERE ""Description""='" + oDescriptionCtrlOld.Text + "'"
' You can comment this out afterwards . . . use it to display the complete UPDATE SQL to confirm it gets the values you want
Print sSQLString
You can then issue the statement:
Code: Select all
iNumUpdated = oStmt.executeUpdate( sStringString )
MsgBOx( "Number of Records Updated: " + iNumUpdated,0,"Num Updated")
I hope this helps, please be sure to let me /us know.
Sliderule
Thanks to add
[Solved] in your
1st post Subject (edit button top right) if this issue has been resolved.
Re: Need help with another tricky query
Posted: Fri Sep 02, 2011 3:05 am
by erf9201
No. It does not work. I get the same error.
It seems that for some reason it cannot locate "Text" field(?) within the control that was fetched.
I dont get it...
Re: Need help with another tricky query
Posted: Sat Sep 03, 2011 10:01 am
by rudolfo
As far as I remember from Oracle and MySQL any reasonable database should do the parsing of the SQL statement during the
prepareStatement step. In other words if the errors appears in the line with
.setString() the statement itself is accepted by the DB engine. Though I am sure, what Slidelrule says about the quotes is important and needs to be followed.
erf9201, can you inspect text field objects a bit closer? First ensure that "NewValue" and "OldValue" are really the names of your text controls. Use the Form Navigator to verify this.
I also recommend to work with an object inspector like
MRI. MRI adds an extra topic to the online help of OpenOffice in case you are lost after the installation of the extension.
It might be a bit difficult in your case because you have a text field that is bound to a database table column (OldValue) and one that is not bound to any column (NewValue). Property not found could mean two things: Either your object doesn't have the .Text property or you don't have a object at all. The later case is easy to verify just add a
Code: Select all
If IsNull(oDescriptionCtrlOld) Then
MsgBox "oDescriptionCtrlOld is not an object"
Exit Sub
End If
to your code. Likewise for
oDescriptionCtrlNew.
Re: Need help with another tricky query
Posted: Sun Sep 04, 2011 5:18 am
by erf9201
Ok, I figured out the error with ".Text".
The problem was the type of field I was using for the "OldValue" (Description Field). It was multiselect field, and it seems that you can only get values out of it if you address it as an array, as it does not have a ".Text" property at all.
So, once I have replaced it with a regular TextBox that indeed has ".Text" property, and linked it to my Table - it worked.
Here is the result:
But now I get another error for some reason. Once I press "OK" button, this comes up:
Any suggestions ?
Re: Need help with another tricky query
Posted: Sun Sep 04, 2011 11:00 am
by Arineckaig
Any suggestions ?
First, I hesitate to intervene when you have had good advice from those more skilled than I. Secondly, my screen is unable to show your images with sufficient clarity to indicate where the error may lie.
Hopefully an example may serve better than my inadequate explanations, so I have attached a crude sample. The macro is triggered by use of the ENTER key in the New colour entry box.
You will see I have followed Sliderule's suggestion of using a simple SQL statement - a Prepared statement is not needed when the criteria are filled from the content of the form controls. The key line in the macro is:
Code: Select all
sSQL = " UPDATE ""DATA"" SET ""Code"" = '" & sNew & "' WHERE ""Code"" = '" & sOld & "'"
Check the quotes required: double quotes around table and field names, but single quotes also required around the variables.
Later edit: Updated macro to catch entry of single or double quote.
Re: Need help with another tricky query
Posted: Sun Sep 04, 2011 1:00 pm
by erf9201
Why no, Arineckaig, your DB example was quite helpful. I think the problem is now solved.
.PrepareStatement was indeed causing the error, so I have removed it. The final code, that works on my machine is as follows:
Code: Select all
REM ***** BASIC *****
Sub btnClick_updateColors(oEvent As Object)
Dim oForm As Object
Dim oCon As Object, oStmt As Object
Dim oDescriptionCtrlNew As Object
Dim oDescriptionCtrlOld As Object
' Retrieve the current connection from the event and form
oForm = oEvent.Source.Model.getParent()
oCon = oForm.ActiveConnection
oDescriptionCtrlNew = oForm.getByName("NewValue")
'oDescriptionCtrl.Text has the new value of color/description field (using TextBox box - not MultiSelect box)
oDescriptionCtrlOld = oForm.getByName("OldValue")
'oDescriptionCtrl.Text has the old value of color/description field (using TextBox box - not MultiSelect box)
oStmt = oCon.CreateStatement()
sSQLString = "UPDATE ""Data"" SET ""Description""='" + oDescriptionCtrlNew.Text + "' WHERE ""Description""='" + oDescriptionCtrlOld.Text + "'"
'Print sSQLString
iNumUpdated = oStmt.executeUpdate(sSQLString)
MsgBOx( "Number of Records Updated: " + iNumUpdated,0,"Num Updated")
oStmt.close()
'Creating SQL statement, updating relevant records and displaying number of updates.
oBkMark = oForm.getBookmark
oForm.reload
oForm.moveToBookmark( oBkMark )
'Refreshing form and returning it to the parent record number.
End Sub
I'll test it tomorrow some more, but I think this is a done deal...
Re: Need help with another tricky query
Posted: Sun Sep 04, 2011 11:03 pm
by rudolfo
Sorry for preaching, but honestly if you work with strings prepareStatement is always needed. The crucial point with a statement like
Code: Select all
sSQLString = "UPDATE ""Data"" SET ""Description""='" + oDescriptionCtrlNew.Text + "' WHERE ""Description""='" + oDescriptionCtrlOld.Text + "'"
is that the content of the Textfield can have any possible character and sometimes this is a surname like O'Henry. The apostrophe will mess up your carefully made up qouting because the SQL parser will see something like:
WHERE "Description"='O'Henry'
prepareStatement instead ensures that no matter what you have in your text field the SQL parser will always see
WHERE "Description"=?, which is always correct. Later on you tell the SQL engine explicitly that you want to bind a string to the question mark with the
setString method. And because you explicitly saying this with the method name you don't need to mess with single quotes for sql string delimiters.
In short most of the times it is the missing patience of the coding person and not the fault of prepareStatement. Please don't read any offence into this last sentence, I don't refer to this issue, rather to many other cases where I have seen folks giving up to early on
prepareStatement because they think it is too complicated. More likely it expresses my frustration about PHP programmers that open the door for SQL injection because they ignore
prepareStatement.
I am pretty sure that the following code should work as well (and in a more robust way).
Code: Select all
oStmt = oCon.prepareStatement("UPDATE ""Data"" SET ""Description""=? WHERE ""Description""=?")
'Print sSQLString
oStmt.setString(1, oDescriptionCtrlNew.Text)
oStmt.setString(2, oDescriptionCtrlOld.Text)
iNumUpdated = oStmt.executeUpdate()
See also
PreparedStatement UNO API and
XParameters for setString() and friends.
Re: Need help with another tricky query
Posted: Sun Sep 04, 2011 11:20 pm
by rudolfo
Instead of preaching i'd better provide a working sample. I modified Arineckaig's sample to favour prepareStatement.
Re: Need help with another tricky query
Posted: Sun Sep 04, 2011 11:41 pm
by erf9201
No, by all means, Father. Preach to me !!! Preach !!!
But seriously, this is an excellent suggestion. And it does work. Thank you very much, rudolfo. I did not think about the ' ' ' situation.
So yes, your code is much better. One thing I still do not understand why it did not work the first time I tried it... Must've been gremlins or somth
Let me test it some more and then we can call it SOLVED