1. Chris Zakrewsky
  2. Valentina Studio
  3. Четверг, Май 31 2018, 07:46 PM
  4.  Подписаться через email
Hi,

I am trying to understand how I am supposed to use the SQL statement: "USE database-name" in context of both VStudio and VServer.

I am attaching three screen-shots to illustrate the problem:

The first one shows the list of databases which VServer reports to VStudio: 2 databases.
The second one shows that I can activate the first database.
The third one shows the problem: the second database can not be activated. Why?

Both should be open and available as per 'EarlyOpeningDatabases=1' right?

Cheers,
Вложения
Комментарий
There are no comments made yet.
Ivan Smahin Ответ принят
Actually, vServer's scheduler may open db (which event configured for) internally and close db (if it was opened by scheduler) after event execution.
There is no support for encrypted databases.
Комментарий
There are no comments made yet.
Chris Zakrewsky Ответ принят
You are saying that encrypted database can not be processed unless the user opens it.
This means that no events whatsoever can exist for an encrypted database because scheduler does not have credentials?
Are they not inherited when defining the event?

Because regardless that I was successfully logged in my database when I defined the event for it, the event itself is always stored in the Master and once I close connection to my database, the Master have to reopen it all by itself to apply the scheduled event afterwards. Interestingly it works for non-encrypted databases just fine, so how Master actually opens un-encrypted databases without explicit credentials?
Комментарий
There are no comments made yet.
Ivan Smahin Ответ принят
Assume some of dbs are encrypted - it is impossible to do all that things above like backup, reindex, diagnose without providing valid encryption key (which should be done in db.Open call). Another case is "permissions" - probably you have no rights to the dbs, but you want to deal with it doing backup and so on.
BTW, neither API nor SQL will not work, because it is still controlled that db was opened by this particular client first.

So the only way is to open databases first.
Комментарий
There are no comments made yet.
Chris Zakrewsky Ответ принят
About backup ALL -- may be this can be made as stored procedure?
with logic


BACKUP_ALL()
curs = SHOW DATABASES
for each db in curs
USE DATABASE db_name
BACKUP

this is pseudo - code.



Great minds think alike ;)
This is EXACTLY what I did, which ultimately ended up in sheer despair subsequently followed by a posting here in this thread.
Aforementioned "USE DATABASE" is simply not working when targeting registered but closed databases!
As there is no known way (for me) to open targeted databases from SQL (which is, BTW, the only way to implement the stored procedure on the server, right?), it effectively became a show stopper...


Maybe we can add parameter db_name to BACKUP DATATABASE db_name[\quote]


This would save the day, yes, but it must be able to work on closed databases as well. And the same goes for DIAGNOSE, DEFRAGMENT, REINDEX and COMPACT. These are typical tasks best suited to be implemented as an event owned by a Master but targeting everything else (i.e. whole database collection residing on a server administered by a Master).

PS: corrected botched formatting of my previous entry again, third time a charm?
Комментарий
There are no comments made yet.
Chris Zakrewsky Ответ принят
About backup ALL -- may be this can be made as stored procedure?
with logic

BACKUP_ALL()
curs = SHOW DATABASES
for each db in curs
USE DATABASE db_name
BACKUP

this is pseudo - code.



Great minds think alike ;)
This is EXACTLY what I did, which ultimately ended up in sheer despair subsequently followed by a posting here in this thread.
Aforementioned "USE DATABASE" is simply not working when targeting registered but closed databases!
As there is no known way (for me) to open targeted databases from SQL (which is, BTW, the only way to implement the stored procedure on the server, right?), it effectively became a show stopper...

Maybe we can add parameter db_name to BACKUP DATATABASE db_name[\quote]

This would save the day, yes, but it must be able to work on closed databases as well. And the same goes for DIAGNOSE, DEFRAGMENT, REINDEX and COMPACT. These are typical tasks best suited to be implemented as an event owned by a Master but targeting everything else (i.e. whole database collection residing on a server administered by a Master).

PS: corrected botched formatting of my previous entry
Комментарий
There are no comments made yet.
Chris Zakrewsky Ответ принят
About backup ALL -- may be this can be made as stored procedure?
with logic

BACKUP_ALL()
curs = SHOW DATABASES
for each db in curs
USE DATABASE db_name
BACKUP


this is pseudo - code.


Great minds think alike ;)
This is EXACTLY what I did, which ultimately ended up in sheer despair subsequently followed by a posting here in this thread.
Aforementioned "USE DATABASE" is simply not working when targeting registered but closed databases!
As there is no known way (for me) to open targeted databases from SQL (which is, BTW, the only way to implement the stored procedure on the server, right?), it effectively became a show stopper...

Maybe we can add parameter db_name to BACKUP DATATABASE [db_name][\quote]
This would save the day, yes, but it must be able to work on closed databases as well. And the same goes for DIAGNOSE, DEFRAGMENT, REINDEX and COMPACT. These are typical tasks best suited to be implemented as an event owned by a Master but targeting everything else (i.e. whole database collection residing on a server administered by a Master).
Комментарий
There are no comments made yet.
Ruslan Zasukhin Ответ принят
btw, Maybe we can add parameter db_name to BACKUP DATATABASE [db_name]
Комментарий
There are no comments made yet.
Ruslan Zasukhin Ответ принят
About backup ALL -- may be this can be made as stored procedure?
with logic

BACKUP_ALL()
curs = SHOW DATABASES
for each db in curs
USE DATABASE db_name
BACKUP


this is pseudo - code.
Комментарий
There are no comments made yet.
Ivan Smahin Ответ принят
Yes, you should open databases first and only then do anything against them.
Комментарий
There are no comments made yet.
Chris Zakrewsky Ответ принят
Ah, so USE DATABASE only works in scope of already opened by user databases!

This complicates my life immensely... I am trying to implement a global (VServer) based BACKUP DATABASE event which is database collection agnostic.
Your current implementation of BACKUP DATABASE does not take the database name as an argument because it only operates in the current context which must be established by USE DATABASE. There is no equivalent of BACKUP ALL DATABASES either.

In other words, it is impossible to make an on demand backup of all databases on the server in one swoop, the only possible way would be to establish a separate event with a very short expiration with an intention to be ran only once by the scheduler for each and every single one database ... just to make a complete backup of all databases on the server. Am I correct assuming this?
Комментарий
There are no comments made yet.
Ivan Smahin Ответ принят
EarlyOpeningDatabases is a server optimization to open db by vServer itself - without any user activity to speed up clients' db.Open() calls later.. But any client must do db.Open() anyway to work with db (including "USE DATABASE" statement).
Client's db.Open call will cause opening db (if not open) AND checking few things like correct encryption keys, permissions and so on... for particular client.
Комментарий
There are no comments made yet.
  • Страница :
  • 1


There are no replies made for this post yet.
However, you are not allowed to reply to this post.

Categories

Announcements & News
  1. 0 subcategories
Valentina Studio
  1. 2 subcategories
Valentina Server
  1. 4 subcategories
Valentina Database ADK
  1. 0 subcategories
Valentina Reports ADK
  1. 0 subcategories
Other Discussions
  1. 2 subcategories
BETA Testing
  1. 0 subcategories
Education & Research
  1. 0 subcategories