Solved

HOW TO DO VACUUM IN VB FOR POSTGRES DATABASE - URGENT

Posted on 2003-11-26
7
480 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 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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
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 process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

744 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now