OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

[DACM] Upgraded to multi-mode database results
Goto page Previous  1, 2
 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
caravas
General User
General User


Joined: 05 Sep 2011
Posts: 31

PostPosted: Mon Apr 02, 2012 7:14 pm    Post subject: Reply with quote

Hi Folks,

Is it possible to setup a password and keep Base connecting? I'm trying doing that using HSQLB Data Manager GUI without success.. I mean, I can change the password for user SA, but then Base can't connect to the database.

Thanks!
Back to top
View user's profile Send private message MSN Messenger
dacm
Super User
Super User


Joined: 07 Jan 2010
Posts: 769

PostPosted: Mon Apr 02, 2012 10:04 pm    Post subject: Reply with quote

caravas wrote:
Is it possible to setup a password and keep Base connecting?

Yes.

Have you read and understood the following link from the tutorial?
Database user log-in (some details of the Base log-in function)
_________________
Soli Deo gloria
Tutorial: avoiding data loss with Base + Splitting 'Embedded databases'
Back to top
View user's profile Send private message
caravas
General User
General User


Joined: 05 Sep 2011
Posts: 31

PostPosted: Thu Apr 05, 2012 6:05 pm    Post subject: Reply with quote

Now I read and understood! That's crystal clear!

Thanks for your help and patience my friend!

Now I'm trying to figure out how to close the connection after users log in and log out, in way to protect passwords and access control...

Without closing openoffice...
Back to top
View user's profile Send private message MSN Messenger
caravas
General User
General User


Joined: 05 Sep 2011
Posts: 31

PostPosted: Mon Apr 09, 2012 5:02 pm    Post subject: Reply with quote

I've read out this forum for a solution to close a connection and protect user data and password between two sessions of the database at the same machine but different users. No success at all...
as a matter of fact, openoffice keeps the session running until it is totally closed. This keep your database vulnerable if you need to control every user and the data they are supposed to achieve from the database. I have several user's sharing machines along the network and I do need to control login and logout procedures for everyone of them. And I cannot close openoffice without the risk of losing some other document already loaded...
I find out a solution using a standalone form with the DataSource property empty, which is filled by macro during the login process. But, the Form ActiveConnection property does not change after loading process, keeping always null, and unable to show any data. After some hard work, I reached some success activating and deactivating the Form design mode. This forces the form to reload and update its ActiveConnection property. I'm not proud of doing that way, but it works at least...

Code:

Sub Logar
REM get user and password from dialog box
      oControl = oDlgEntra.GetControl("UserName")
      oControl1 = oDlgEntra.GetControl("UserPassword")
      oUser = oControl.Text
      oPassword = oControl1.Text

 Database = CreateUnoService("com.sun.star.sdb.DatabaseContext")
 oSource = Database.getByName("MyDb")
   
oSource.User = oUser
oConnection = oSource.GetConnection("" & oUser & "", ""& oPassword & "")
REM set base password
oConnection.Parent.Password = oPassword
oForm = ThisComponent.Drawpage.Forms.GetByName("MainForm")
oForm.DataSourceName = "MyDb"
oForm.Command = "SELECT * FROM ""MyTable"" WHERE ""MyColumn"" = " & "'" & oUser & "'"

Doc = ThisComponent

REM activate form design view to update ActivateConnetion
Controller = Doc.CurrentController
Controller.setFormDesignMode(true)
Controller.setFormDesignMode(false)

oDlgEntra.EndExecute() 

End Sub


I'm pretty sure this code can be improved so, if someone wants to, I would be grateful to know the result.

Thanks to all that helped me to get so far!
Back to top
View user's profile Send private message MSN Messenger
caravas
General User
General User


Joined: 05 Sep 2011
Posts: 31

PostPosted: Mon Apr 30, 2012 5:56 am    Post subject: Reply with quote

I woke up this morning and things wore nice, until my database could not open anymore...

During the Start Server process, it shows me the following messages:

SEVERE mydb.data getFromFile out of mem 3706576 java.lang.OutofMemoryError: Javaheapspace.
….
did not open:o= org.hsqldb:HsqlException: Out of Memory.

My start.bat was:

Code:
@echo off
set javapath=C:\Arquivos de programas\Java\jre6\bin\Java.exe
set jarpath=C:\Arquivos de programas\LibreOffice 3\Basis\program\classes\hsqldb2\hsqldb.jar
"%javapath%" -cp "%jarpath%" org.hsqldb.Server -database.0 file:mydb
pause
exit

I tried to change it adding the following option:

Code:
@echo off
set javapath=C:\Arquivos de programas\Java\jre6\bin\Java.exe
set jarpath=C:\Arquivos de programas\LibreOffice 3\Basis\program\classes\hsqldb2\hsqldb.jar
"%javapath%" -cp "%jarpath%" org.hsqldb.Server -database.0 file:mydb;[b]default_cached_table=true[/b]
pause
exit


It did not work at all. I know there is a question concerning java memory capabilities. By now, I am too concerned about losing months of work and can not see things clearly. Could someone save my life please?

Background information: Standalone forms with base and HSQLDB 2.2.8. Openoffice 3.3.3 on Windows XP.
Back to top
View user's profile Send private message MSN Messenger
caravas
General User
General User


Joined: 05 Sep 2011
Posts: 31

PostPosted: Mon Apr 30, 2012 7:14 am    Post subject: Reply with quote

Well,

This is mydb.script, it seems to miss the field property length "Conteudo" on Table "TabOficio". It was supposed to be 7500, I guess..

I also tried to set xmx256m on start.bat file. It did not make difference.

SET DATABASE UNIQUE NAME HSQLDB36E12CBD29
SET DATABASE GC 0
SET DATABASE DEFAULT RESULT MEMORY ROWS 0
SET DATABASE EVENT LOG LEVEL 0
SET DATABASE SQL NAMES FALSE
SET DATABASE SQL REFERENCES FALSE
SET DATABASE SQL SIZE TRUE
SET DATABASE SQL TYPES FALSE
SET DATABASE SQL TDC DELETE TRUE
SET DATABASE SQL TDC UPDATE TRUE
SET DATABASE SQL TRANSLATE TTI TYPES TRUE
SET DATABASE SQL CONCAT NULLS TRUE
SET DATABASE SQL NULLS FIRST TRUE
SET DATABASE SQL UNIQUE NULLS TRUE
SET DATABASE SQL CONVERT TRUNCATE TRUE
SET DATABASE SQL AVG SCALE 0
SET DATABASE SQL DOUBLE NAN TRUE
SET DATABASE SQL LONGVAR IS LOB FALSE
SET DATABASE TRANSACTION CONTROL LOCKS
SET DATABASE DEFAULT ISOLATION LEVEL READ COMMITTED
SET DATABASE TRANSACTION ROLLBACK ON CONFLICT TRUE
SET DATABASE TEXT TABLE DEFAULTS ''
SET DATABASE DEFAULT TABLE TYPE CACHED
SET FILES WRITE DELAY 0
SET FILES BACKUP INCREMENT TRUE
SET FILES CACHE SIZE 10000
SET FILES CACHE ROWS 50000
SET FILES SCALE 1
SET FILES LOB SCALE 32
SET FILES DEFRAG 0
SET FILES NIO FALSE
SET FILES NIO SIZE 256
SET FILES LOG TRUE
SET FILES LOG SIZE 10
SET DATABASE COLLATION "Portuguese"
CREATE USER SA PASSWORD DIGEST 'd41d8cd98f00b204e9800998ecf8427e'
CREATE SCHEMA PUBLIC AUTHORIZATION DBA
SET SCHEMA PUBLIC
CREATE CACHED TABLE PUBLIC."TabOficio"("Oficio" VARCHAR(15) NOT NULL PRIMARY KEY,"Data" DATE NOT NULL,"Remetente" VARCHAR(50) NOT NULL,"Destinatario" VARCHAR(75) NOT NULL,"Assunto" VARCHAR_IGNORECASE(150) NOT NULL,"Conteudo" VARCHAR_IGNORECASE NOT NULL,"Ano" VARCHAR(6) NOT NULL)
CREATE CACHED TABLE PUBLIC."TabMemo"("Memo" VARCHAR(15) NOT NULL PRIMARY KEY,"Data" DATE,"Remetente" VARCHAR(50),"Destinatario" VARCHAR(50),"Assunto" VARCHAR_IGNORECASE(150),"Texto" VARCHAR_IGNORECASE(6500))
ALTER SEQUENCE SYSTEM_LOBS.LOB_ID RESTART WITH 1
SET DATABASE DEFAULT INITIAL SCHEMA PUBLIC
SET TABLE PUBLIC."TabDestinatario" INDEX '3608456 0 210'
SET TABLE PUBLIC."TabOrgao" INDEX '1800608 0 29'
SET TABLE PUBLIC."TabCargo" INDEX '1766600 0 3'
SET TABLE PUBLIC."TabLotacao" INDEX '1760216 0 5'
SET TABLE PUBLIC."TabOficio" INDEX '2446456 0 683'
SET TABLE PUBLIC."TabRemetente" INDEX '1793296 0 4'
SET TABLE PUBLIC."TabMemo" INDEX '3038416 0 857'
SET TABLE PUBLIC."TabDestinatarioMemo" INDEX '3643184 0 31'
SET TABLE PUBLIC."RascunhoOficio" INDEX '2966352 0 2'
SET TABLE PUBLIC."RascunhoMemo" INDEX '3197520 0 2'
SET TABLE PUBLIC."TabAnexo" INDEX '3629008 0 4'
SET TABLE PUBLIC."TabAnexoMemo" INDEX '3656568 0 18'
SET TABLE PUBLIC."TabLocal" INDEX '3649128 0 1'
SET TABLE PUBLIC."ContAnexo" INDEX '3670408 0 2'
SET TABLE PUBLIC."ContAnexoMemo" INDEX '3687584 0 12'
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.YES_OR_NO TO PUBLIC
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.TIME_STAMP TO PUBLIC
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.CARDINAL_NUMBER TO PUBLIC
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.CHARACTER_DATA TO PUBLIC
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.SQL_IDENTIFIER TO PUBLIC
GRANT DBA TO SA
Back to top
View user's profile Send private message MSN Messenger
caravas
General User
General User


Joined: 05 Sep 2011
Posts: 31

PostPosted: Mon Apr 30, 2012 10:02 am    Post subject: Reply with quote

The file mydb.data is around 4.096kb of size. I had a backup file with 3.619kb and I just replaced it and things got back to normal...
This file can not grow up above 4mb???
Back to top
View user's profile Send private message MSN Messenger
caravas
General User
General User


Joined: 05 Sep 2011
Posts: 31

PostPosted: Mon Apr 30, 2012 11:00 am    Post subject: Reply with quote

Well,

I tried to change the big VARCHAR_IGNORECASE(7500) field to CLOB, but the size of the mydb.data got over 4mb and once again I had the same problem...
Please folks, any ideas??
Back to top
View user's profile Send private message MSN Messenger
caravas
General User
General User


Joined: 05 Sep 2011
Posts: 31

PostPosted: Mon Apr 30, 2012 4:37 pm    Post subject: Reply with quote

ok guys,

Sorry about my desperation..

It seems there was some problem on my script file..at least I suppose that.
I recovered the backup file from before hsqldb 2x upgrade, after shutdown compact, the data file has around 2mb of size. Things are working fine for while. I'm afraid of having the same problems as the data file grows up to 4mb once more...
Back to top
View user's profile Send private message MSN Messenger
dacm
Super User
Super User


Joined: 07 Jan 2010
Posts: 769

PostPosted: Mon Apr 30, 2012 11:08 pm    Post subject: Reply with quote

caravas wrote:
I woke up this morning and...Out of Memory Error: Javaheapspace.

Wow I haven't seen anything like that since testing huge (up to million record) batch INSERTS on an 'embedded database' with default Base settings.

Of note...you might want to 'Remark' the "pause" command in your server start-up file:
Code:

REM pause
exit


Otherwise, I'm not sure what you're encountering. As I recall, your database was upgraded to HSQLDB 2.x, but encountered some VARCHAR(size) issues during the upgrade as seen in earlier posts. Apparently, its still an issue, but I'm not convinced that there was any problem with your .script file overall (defaults apply when VARCHAR size is left blank). I've also experimented with Java heap settings (-Xmx256m, 1G, etc) but it doesn't seem to make a noticeable difference with over-sized CACHED tables even exceeding the 64mb default; it makes all the difference with huge MEMORY tables. But for an advanced user with critical data, I'm surprised that you refuse to maintain proper backups...even through free version-automation software or cloud-service as outlined in a post above on your behalf. Either way, database file size should not be an issue. My largest HSQLDB 2.x database is 208 MB (1.4 million contacts). The limit is 2TB per CACHED table with a 64TB LOB store.
_________________
Soli Deo gloria
Tutorial: avoiding data loss with Base + Splitting 'Embedded databases'
Back to top
View user's profile Send private message
caravas
General User
General User


Joined: 05 Sep 2011
Posts: 31

PostPosted: Tue May 01, 2012 6:06 am    Post subject: Reply with quote

Quote:
Of note...you might want to 'Remark' the "pause" command in your server start-up file:
Code:

REM pause
exit


I see your point and already changed my start.bat file.

Quote:
But for an advanced user with critical data, I'm surprised that you refuse to maintain proper backups...even through free version-automation software or cloud-service as outlined in a post above on your behalf. Either way, database file size should not be an issue. My largest HSQLDB 2.x database is 208 MB (1.4 million contacts). The limit is 2TB per CACHED table with a 64TB LOB store.


Thanks for the "advanced user", despite of I disagree with you Very Happy
Yes, shame on me for did not taking the necessary care about backups... Embarassed

I will perform some tests on my database to check if there is still any problem about getting over 4mb of data size. I will let you know the results.
Thanks again!
Back to top
View user's profile Send private message MSN Messenger
caravas
General User
General User


Joined: 05 Sep 2011
Posts: 31

PostPosted: Tue May 01, 2012 6:18 pm    Post subject: Reply with quote

Well,

I believe the missing length of the large field was the point:

CREATE CACHED TABLE PUBLIC."TabOficio"("Oficio" VARCHAR(15) NOT NULL PRIMARY KEY,"Data" DATE NOT NULL,"Remetente" VARCHAR(50) NOT NULL,"Destinatario" VARCHAR(75) NOT NULL,"Assunto" VARCHAR_IGNORECASE(150) NOT NULL,"Conteudo" VARCHAR_IGNORECASE NOT NULL,"Ano" VARCHAR(6) NOT NULL)

After I adjusted the field length to 7500, executed shutdown compact and upgraded to hsqldb 2.2.8, I burned out my database filling it up with about 10000 rows and still works fine. The data file got about 10mb.

I'll try it a litle bit more tomorrow...
Back to top
View user's profile Send private message MSN Messenger
dacm
Super User
Super User


Joined: 07 Jan 2010
Posts: 769

PostPosted: Tue May 01, 2012 9:53 pm    Post subject: Reply with quote

Well that's great work!

And yes, you're an advanced user, or at least a model user because you're willing to read until you comprehend, and then experiment until you find the answer.

Along those lines, it's good to know that ensuring VARCHAR fields have a size assigned before upgrade to HSQLDB 2.x is critical for trouble-free migration. I know the HSQLDB 2.x documentation mentions this very thing (as posted previously above), but now we know that the defaults (32768) don't necessarily apply in that case.
_________________
Soli Deo gloria
Tutorial: avoiding data loss with Base + Splitting 'Embedded databases'
Back to top
View user's profile Send private message
caravas
General User
General User


Joined: 05 Sep 2011
Posts: 31

PostPosted: Sat May 05, 2012 12:14 pm    Post subject: Reply with quote

Folks,

Performing some more tests, I observed a strange behavior on openoffice. After doing a big insert of rows (3000 lines), shutting down the server (clean shutdown), and starting server again I opened base file and things seemed to work fine. But, despite of I saw the available tables I was not able to see, by double-clicking, any data in there. It shows me the following message: software error. Socket error creation. The server was still running anyway.
But after closing and opening base again, things got back to normal..
I tried to do every step again and, using the windows task manager, I observed that the soffice.bin process remained running somehow in there and was using about 200.000kb of system memory. Even after closing all openoffice documents. After the soffice.bin process was closed, everything was normal. It only happens after a great insert of data...
Guess I need to improve acknowledgment on how openoffice, java and hsqldb uses the system available resources. I mean, where does the SQL instructions take place? On the server side? The client machines only receives the select results or they load all data available in the database tables? Is there a way of controlling this?
Other question is: can I close the session anyway?

Need a light!
Back to top
View user's profile Send private message MSN Messenger
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Goto page Previous  1, 2
Page 2 of 2

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group