[Solved] How to solve data loss in Base

Discuss the database features
Post Reply
joaofmateus
Posts: 33
Joined: Fri Mar 20, 2009 12:15 pm
Location: Lisbon - Portugal

[Solved] How to solve data loss in Base

Post by joaofmateus »

 Edit:  
I have long felt the need to have something at home that would allow me to manage the family budget.

As a former ACESS accountant and user I knew how to do this in an application to manage a database.
On a first try, I tried to do this in BASE, but since I did not have the necessary knowledge at the time, I ended up giving up and doing it in CALC (which, for me, is Openoffice's most successful application).

As I was hoping to know every month and every year, how much I spent on a certain item (food expenses, for example) demanded several spreadsheets with several links, etc., I never gave up and, at the expense of a lot of research, I managed to foot, a project in the Base that allows me to know, at any moment, what I intend, as you can see here.

http://aversenosentendemos.blogspot.pt/ ... idade.html

It turns out that, past just over a year ago and with only a few thousand records, I'm pretty much as referred to here:

https://scottishsceptic.co.uk/2017/12/0 ... lled-base/

That is, record losses happen, work days prove to be unproductive, I have decided, to give one more chance to Base, to try one more chance to BASE and myself and try the method, referred to here:

viewtopic.php?f=13&t=54182
1. I installed the server
2. I created the Database
3. In BASE I configured the MySQL connection
4. I imported the tables, the querys, the reports I could not (perhaps because they were created with Oracle Report Builder), the modules also did not but I built them and then I copied the codes
related)

Result:
The tables are there with their records
Forms work perfectly
The querys also work (select querys)
Hand-written reports also
But the old codes, which worked perfectly at the base, simply do not work

But this code, which in addition to the inserts is the basis of building all the information provided by the application, and without which the project is perfectly useless to me, does not work!

Here is the code:

Code: Select all

Sub Contabiliza()
Dim DatabaseContext As Object
Dim DataSource As Object
Dim Connection As Object
Dim Statement As Object
Dim ResultSet As Object
Dim oBarraEstado As Object
Dim co1 As Integer


'Inicia o processo de contabilização

'Msgbox "A iniciar o processo de Contabilização"


   'Referencia a barra de estado do documento activo
   oBarraEstado = ThisComponent.getCurrentController.StatusIndicator
   'Estabelece o texto inicial e  o limite da barra de progresso
   oBarraEstado.start( "Processo de Contabilização a decorrer.Aguarde...", 3 )
  

'Limpa todos os movimentos contabilisticos
'Primeiro Processo

'Esta Conexão só funciona em Bases de Dados Registadas
DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
DataSource = DatabaseContext.getByName("contabilidade")
Connection = DataSource.GetConnection("","")
'Connection=thisdatabasedocument.CurrentController.activeconnection   Com o meu  AutoExec dá erro nesta linha por isso necessário registar base de dados

Statement = Connection.createStatement()
ResultSet = Statement.executeQuery("DELETE FROM ""tMOVIMENTOS_CONTA"";")
'Estabelece o valor da barro de progresso
oBarraEstado.setValue( 1 )
'E o texto
oBarraEstado.setText ("Limpou os Dados Contabilísticos")
'Espera um segundo
Wait 1000

'Contabiliza os movimentos a Débito
'Segundo Processo

'Esta Conexão só funciona em Bases de Dados Registadas
DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
DataSource = DatabaseContext.getByName("contabilidade")
Connection = DataSource.GetConnection("","")
'Connection=thisdatabasedocument.CurrentController.activeconnection
Statement = Connection.createStatement()
ResultSet = Statement.executeQuery("INSERT INTO ""tMOVIMENTOS_CONTA""(""ID_MOV"",""COD_CONTA"",""DATA_MOV"",""DESC_MOV"",""DEBITO"",""CREDITO"")SELECT ""ID_MOV"",""CONTA_DEB"" AS ""COD_CONTA"",""DATA_MOV"",""DESC_MOV"",""VALOR"" as ""DEBITO"",0 AS ""CREDITO"" FROM ""tMOVIMENTOS"";")
'Estabelece o valor da barro de progresso
oBarraEstado.setValue( 2 )
' E o texto
oBarraEstado.setText ("Inseriu os valores a Débito")
'Espera um segundo
Wait 1000

'Contabiliza os movimentos a Crédito
'Terceiro Processo

'Esta Conexão só funciona em Bases de Dados Registadas
DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
DataSource = DatabaseContext.getByName("contabilidade")
Connection = DataSource.GetConnection("","")
'Connection=thisdatabasedocument.CurrentController.activeconnection
Statement = Connection.createStatement()
ResultSet = Statement.executeQuery("INSERT INTO ""tMOVIMENTOS_CONTA""(""ID_MOV"",""COD_CONTA"",""DATA_MOV"",""DESC_MOV"",""DEBITO"",""CREDITO"")SELECT ""ID_MOV"",""CONTA_CRED"" AS ""COD_CONTA"",""DATA_MOV"",""DESC_MOV"",0 as ""DEBITO"",""VALOR"" AS ""CREDITO"" FROM ""tMOVIMENTOS"";")
'Estabelece o valor da barro de progresso
oBarraEstado.setValue( 3 )
' E o texto
oBarraEstado.setText ("Inseriu os valores a Crédito")
'Espera um segundo
Wait 1000



'Finaliza a barra de progresso para devolver o controlo ao utilizador
   oBarraEstado.end()
Msgbox "Processo de Contabilização concluído"

End Sub


Does anyone know or are willing to help me in this?

I want to state three more things:

1. I'm not someone who walks here looking for technical support, just someone with an advanced user profile who uses what they know and can get for their own use and, as a rule, even shares what they know with whom they want help, as indeed can be seen here (http://aversenosentendemos.blogspot.pt/)
2. Those who have helped me to this day, both here and in others, know that I am deeply grateful to all who have helped me
3. Not being able to solve this problem on foot can, quite simply, lead me, against my will, to finally define the Base and, therefore, appeal to those who are behind its development, solve these problems that have been kept from updating to updating

My wishes for a Happy Christmas and a Good Year 2018 for all.


João Mateus
Amadora
Portugal
Last edited by joaofmateus on Tue Feb 27, 2018 2:26 pm, edited 1 time in total.
OOo 4.1.5 on MS Windows 10
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: HOW TO SOLVE DATA LOSS ON THE BASE

Post by UnklDonald418 »

Just saying “simply do not work ” isn't very helpful. What are the error messages you see? Debugging macro code can be difficult, and when the code is out of context the difficulty increases because we have no reliable way of testing it.
Based on what information you have given the macro code seems to be just a series of SQL statements executed via the API Statement Service.
The embedded version of Base supplied with the Open Office distribution is useful as a teaching tool but for a number of reasons it isn't really suitable for serious applications. Since Base is just a front end for an underlying database engine by moving to a split database model the data loss problem is minimized plus you can use a more recent database engine. In the Tutorial area of this Forum there are examples of how to use a more recent version of HSQLDB.
[Tutorial] Splitting an "embedded HSQL database"
[Wizard] Create a new 'split' HSQL 2.x database
or it appears you have chosen to use MYSQL.
The first place to look is to make sure the SQL statements are compatible with MYSQL.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
User avatar
Hagar Delest
Moderator
Posts: 32628
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

Re: How to solve data loss in Base

Post by Hagar Delest »

Else, aren't applications like Grisbi not suitable for your use?
LibreOffice 7.6.2.1 on Xubuntu 23.10 and 7.6.4.1 portable on Windows 10
joaofmateus
Posts: 33
Joined: Fri Mar 20, 2009 12:15 pm
Location: Lisbon - Portugal

Re: How to solve data loss in Base

Post by joaofmateus »

My problem seems to be solved.
I solved MySql and rebuilt the project as indicated here:
viewtopic.php?f=83&t=65980
and, two months ago everything seems to be working properly!
Thank you UncklDonald418.

PS: If the data loss problem seems so easy to resolve, why has the method not yet been adopted by default by the Base developers?
OOo 4.1.5 on MS Windows 10
Post Reply