Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

HOW TO DO VACUUM IN VB FOR POSTGRES DATABASE - URGENT

Posted on 2003-11-26
7
Medium Priority
?
504 Views
Last Modified: 2013-12-26
Hi everyone,

I am using VB6, and PostGres Database. I would like to Vacuum the database by coding. Currently, I use PGADMIN II to do this task. I right click on the table and select Vacuum. How do I do this job by writing a VB code to do that...
0
Comment
Question by:neonlights
  • 4
  • 3
7 Comments
 
LVL 14

Accepted Solution

by:
Tommy Kinard earned 600 total points
ID: 9827391
How are you connecting to the database? with ODBC is it installed on a linux/microsoft server? or is it on a windows desktop?
Why not put Vacuum in the SQL statement?

Public cn As rdoConnection
SQL = "vacuum"
cn.Execute SQL, rdExecDirect

that works for me with Redhat Linux  7.2 server, Postgres 6.5 from a windows98se PC, using ODBC.

HTH
dragontooth

0
 
LVL 14

Expert Comment

by:Tommy Kinard
ID: 9827421
Note this will eventually cause a problem if more than one user is connected to the database so it would actually be better to run the vacuum during the night when the data has a better chance of not getting corrupt.

In Linux I have a cron setup to run each night at 3:00 AM

Just a few thoughts
dragontooth

0
 

Author Comment

by:neonlights
ID: 9858075
Thanks Drangontooth,

I am using all what you mentioned:

Redhat Linux  7.2 server, Postgres 6.5 from a windows98se PC, using ODBC.

This system is used by 10 or more people. But, there is a one controller (PC), it maintains the clean up. So, this controller can execute this statement? am I right?

Public cn As rdoConnection
SQL = "vacuum"
cn.Execute SQL, rdExecDirect

Can I use SQL word? and if I run this statement, will vacuum the database...I know it should,,,just wondering...
Thanks
Here is my points.
Do you know how to send wav file over phone line? if you know, I also post question on that. 250 points...let me know...
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:neonlights
ID: 9858109
Right now, I do vacuum usining PGADMIN II, right click on the table, and do vacuum. So, if I do this statement, do I have to mention what table? or it will vacuum all the tables? how this works?

Thanks
0
 
LVL 14

Expert Comment

by:Tommy Kinard
ID: 9858530
Thanks for the points and the Grade!

The SQL is dim as a string cn.Execute SQL, rdExecDirect tells the ODBC to execute this command weather it is a SQL statement or a stored procedure or an internal command.

LOL no I dn't know how to send a wave file over a phone line, sorry.

In the ODBC driver (in the connection string) there is a field which you specify what database you are loging on to. All of the tables will be vacuumed in that database.

If you need more help let me know
dragontooth

0
 

Author Comment

by:neonlights
ID: 9859328
You are welcome!.

So, if I do
Dim SQL as String
Public cn As rdoConnection
SQL = "vacuum"
cn.Execute SQL, rdExecDirect

do the job, and vacuum all the tables? wow,...cool

Thanks
0
 
LVL 14

Expert Comment

by:Tommy Kinard
ID: 9859508
This is a function I wrote to execute my SQLs etc.  The Conn is a boolean that I use to determine if the connection is active in which case I reuse the connection, otherwise I establish a connection.

I would suggest this:

Public SQL as String
Public cn As rdoConnection

Public Function ExecSql()
    On Error GoTo CnEh
    If Not Conn Then
            cn.CursorDriver = rdUseOdbc
'change the DSN to yours; you will also need to put in the database;server ip/name;userid(UID);password
            cn.Connect = "DSN=sched;DATABASE=" & Database & ";SERVER=10.0.0.1;PORT=5432;UID=;PWD=;READONLY=0;PROTOCOL=6.5;FAKEOIDINDEX=1;SHOWOIDCOLUMN=1;ROWVERSIONING=0;SHOWSYSTEMTABLES=1;CONNSETTINGS="
        cn.EstablishConnection
        Conn = True
    End If
    cn.Execute SQL, rdExecDirect
    cn.BeginTrans
    cn.CommitTrans
    On Error GoTo 0
    Exit Function
CnEh:
    Err.Clear
    On Error GoTo 0
End Function

dragontooth

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

782 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question