Solved

HOW TO DO VACUUM IN VB FOR POSTGRES DATABASE - URGENT

Posted on 2003-11-26
7
498 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 14

Accepted Solution

by:
Tommy Kinard earned 150 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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

Technology Partners: 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

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…
Suggested Courses
Course of the Month6 days, 21 hours left to enroll

623 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