HOW TO DO VACUUM IN VB FOR POSTGRES DATABASE - URGENT

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...
neonlightsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Tommy KinardCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Tommy KinardCommented:
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
neonlightsAuthor Commented:
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

neonlightsAuthor Commented:
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
Tommy KinardCommented:
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
neonlightsAuthor Commented:
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
Tommy KinardCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.