[Solved] Need help with another tricky query

[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.

[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.

[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.

[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?

[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.

[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...

[rudolfo]

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.

[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.

[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.

[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.

[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.

[DACM]

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.

[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...

[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.

[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.

[DACM]

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
.

[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?

[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,...)

[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:

[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""=? ")

Explanation: You are using StarBasic as your programming language. With StarBasic, a string is denoted by surrounding it with double quotes . . . for example:

Code: Select all

sStringField = "Hello World"

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 . . .

Code: Select all

""Data""

""Description""

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.

[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)

[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.

[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...

[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.

[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 ?

[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.

[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...

[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.

[rudolfo]

Instead of preaching i'd better provide a working sample. I modified Arineckaig's sample to favour prepareStatement.

[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