HOW do I assign the result from a query to a variable in LO Base?

English
, , ,
#1

My specific question has to do with a confusing database issue I have. I have 2 databases, one functions properly, The other, not so much.

Within Bowling4Answers.odb , the form ALLEYS utilizes a pair of MACROS to populate a field named NUGGET_NUM in Table ALLEYS by joining a text string (a preconfigured text box with three options) with the auto-incrementing primary key. It seems to work just fine.

Within ECR-ECO.odb, the form frm_ORIGINATOR(16) is attempting to use a similar field/structure as NUGGET_NUM named ECx_NUM. This form is set to allow additions, but not allow modifications or deletions, so one the form is submitted, the values are not editable (this is intentional as a second level reviewer will make any edits). This is the db I am having problems with getting the ECx_NUM populated.

The Error I am getting is an “Inadmissable value or data type. Overflow.” but I don’t know how to find the problem. The 2 macros that Ratslinger has provided are awesome, I can not thank him OR her enough. I have attached both db’s to refer to… Thank you in advance.

COMBINED.odb

#2

Hello,

This may be solved but SQL is probably not the answer. You do not state your OS, the specific LO version and which database you are using - the last most important for this question. It is probably easiest done using a DB trigger or a macro but don’t know your background in this - Triggers are depend upon the DB and LO Base macros are not the same as say Access VB. Macros would also need specifics from your file.

Need more info - either edit original question or use add a comment. Please do not use an Answer.

#3

Another read through the question adds confusion. It appears you have two fields in ALLEYS and want to make a third field by combining them - is that correct? If so this is not normal and when something as this is needed it is easy enough to just do this through regular SQL as the occasion dictates.

#4

My OS is Win10, LO is version 6.1.6.3 (x64) Build 5896ab~~~72 and the db is the embedded db that came with LibreOffice. I don’t have any background of triggers, but I am learning MACROs by referencing everything I can find on the WEB (I made the CURRENT_TIMESTAMP Macro to occur when the record is submitted). My table is such that I have a number of fields, but the two important fields are the index (ALLEYID) and the value in ALLEYNAME (which can be ECR-, ECO-, or ECN-) . My understanding is such that if I query for the MAXimum value of Field1 and its associated ALLEYNAME value in the same record, I should be able to (1) edit the value by some means and/or (2) save it to a new location (it doesn’t need to be in the same table per se). I’m just confused as to collecting the value and editing it as I need it to be. This new value (ECR-10027) will be used as the seed for the naming of a collection of files that are zipped together (ECR-10027.zip) held in an archive.

#5

Where you want this result and what DB is used is very important here. LO Base comes with a choice of using HSQLDB embedded or Firebird embedded. This is at the bottom of the main screen of Base. Where is also important.

#6

HSQLDB embedded is what I’m using. It would be most convenient to put it in NUGGET_NUM FROM tableALLEYS, but I don’t know at this point if that is possible. I could build another table to push NUGGET_NUM into as well.

#7

Sorry, I commented before I read the Answer, I’ll give this a go, and let you know. Thank You

#8

I understand ViewConcatFirebird.odb, THANK YOU Very Much. I need to get back with you on the MacroConcat.odb as I’m still working it out to understand and I need to read more about macro construction. I have a Macro already named onUpdateRecord that pushes the system timestamp when the form is submitted that might be conflicting? Here is my code since I don’t know how to add it as a file:

#9

Option explicit
Global ChangeType As String
Global iID As Integer
Global sName As String

Sub onUpdateRecord( oEvent as object )
’ to update date field with current date
dim oDataForm, oStatement, oResultSet as variant
if oEvent.Source.SupportsService( “com.sun.star.form.component.DataForm” ) then
if oEvent.Action = com.sun.star.sdbc.ChangeAction.UPDATE then
oDataForm = oEvent.Source
oStatement = oDataForm.ActiveConnection.CreateStatement
oStatement.EscapeProcessing = False
oResultSet = oStatement.executequery( “CALL CURRENT_SYSDATE” )
oResultSet.next
oDataForm.updateTimeStamp( oDataForm.FindColumn( “DATE_ECR_INITIATED” ), oResultSet.getTimestamp( 1 ) )
end if
end if
End Sub

#10

Digging deeper into your macro, I don’t understand some of the terms of format. When leveraging your macro into my DB, I get a compile error at [ oForm = ThisComponent.getDrawPage().Forms.getByName(“MainForm”) ] and I am having a hard time determining what I need to change. Can you tell me where I can find the definitions you used to create the MACRO? Assume I know nothing about the string above please, that way I will learn everything. I don’t think I need the ThisComponent.getDrawPage() because I already have a form named [frm_Originator], but what is the rest of the string doing? Thanks

#11

@JustTryHarder,

Responded below my answer. This is where you should be responding. Responding here is to the question itself.

#12

Hello,

Just to give an example of one of many ways to do this, I created a VERY basic Firebird Base file (HSQLDB would be the same). Used the two fields and created a VIEW (ViewNewField) from this. The basis of the VIEW is the same ad used in the one Query.

Sample ------ ViewConcatFirebird.odb

Any record added/modified/deleted will reflect correctly in the VIEW.

Edit 2020-01-17:

Using macros have put this combined field in the same record. Before the record is changed the type of record is checked for - new or existing. If existing the data is saved in global variables. After the record is updated the global variables are checked. If this was a new record the information of the MAX ID is retrieved and that data is used else the global data is used to update the field.

This information is not available in the form unless a refresh is done. Don’t see the need on entry form as this should not be a field to be modified.

HSQLDB sample ------- MacroConcat.odb

Edit 2020-02-07:

You have changed requirements from the original post. Original post ID started at 10000. In this sample it is 100000. This make the data types used different. Also the sample did not use mixed-case data names. Yours does. For the SQL strings this needs to be surrounded with Two sets of quotes.

Fixed Base file ----- 1581110826629044.odb

You won’t see the updated data until the form data is refreshed. Going back a record is not enough. The cache needs to be reloaded.

#13

BTW - @variables are set in a MySQL database.

#14

@JustTryHarder,

ThisComponent.getDrawPage().Forms.getByName("MainForm")

is necessary. The form name here is an internal name which can be found when editing a form. Libre macros are difficult to grasp without a lot of reading and testing. Data is not (mostly) all in one spot. Here is a start:

Documentation/Publications

See Chapter 13 - Getting Started With Macros under Getting Started with LibreOffice

and

See Chapter 9 - Macros under LibreOffice Base Handbook

And for a real head spinner see this PDF book from Andrew Pitonyak → Open Office Macros Explained

and

Andrew Base PDF

#15

I’m getting closer to solving this riddle, but alas, this riddler is a tricky one. I replicated your MacroConcat.odb to Bowling4Answers.odb manually so I could test/practice/learn (I did not copy and paste, I made a new table with new information). I also added a field to the ALLEYS table so that the field names are now ALLEYID, ALLEYNAME, LANES, and NUGGET_NUM. I tried to replicate the form ALLEYS, making sure to copy each Control Property exactly like yours, but when I open my manually replicated form ALLEYS, I get an error :

BASIC runtime error. An exception occurred Type: com.sun.star.lang.WrappedTargetRuntimeException Message: Could not retrieve column value: The cursor points to before the first or after the last row… image description

Then I dragged your form ALLEYS into Bowling4Answers.odb and renamed it ALLEYS_too, and it works without error!! I feel like I’m close, but not close enough.

#16

After clearing the error and Populating fields using form ALLEYS (following the process >> Last Record, New Record, Add data to ALLEYNAME, Add data to LANES, Save Record), does load into the ALLEYS table, but pressing the Refresh Button, brings up the error again.

BTW, I tracked down "Database Programming - OpenOffice.org Base and Basic by Roberto Benitez (2008) and have dug through chapter 9 and 13 of the LOBH. I feel like I’m getting closer to having it work the way I think it should… but this error has got me stumped… for many days now. Thanks for your help!

#17

BTW, If I have the ALLEYS Table open at the same time as my form ALLEYS and press refresh after saving the record from the form , the NUGGET_NUM isn’t getting populated until the next record has been saved…

#18

Which isn’t what I had in mind…

#19

Your Form ALLEYS_too works perfectly populating NUGGET_NUM as it should.

#20

@JustTryHarder,

Sorry but not certain how to respond to your comments. There are a lot of statements but no specific questions. You do state having a problem with Bowling4Answers.odb but I am at a loss since I have nothing to look at. If you post a copy (no personal/confidential information in it) I can examine it. Just Edit your question and use the toolbar icon → paperclip (upper left of edited question). This will allow you to attach a sample.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.