Run DDL statements from VB 1

[Andrzejek]

Is there a way in VB6 to run this (and other) DDL statement with ORACLE data base?
[tt]
SET DEFINE OFF;
CREATE SEQUENCE ANDY_SEQ
START WITH 0
MAXVALUE 999999999999999999999999999
MINVALUE 0
NOCYCLE
NOCACHE
NOORDER
NOKEEP
GLOBAL
[/tt]
This is not for any user, just myself. I would like to write a little VB program to help me with some regular tasks in Oracle, like Create a Trigger, add a field to a table, Set the field as a PK, etc. I am also open to do this in VB.NET


---- Andy

There is a great need for a sarcasm font.

 

[gmmastros]

I do this all the time with SQL Server. I use SQLOLEDB driver with ADODB connection object.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Andrzejek]

I use ADODB all the time, but with Driver={Microsoft ODBC for Oracle};

Could you share your code with connection string and how you execute the DLL statements?

Got connection string working with:[TT]
.ConnectionString = "Provider=OraOLEDB.Oracle;" & _
"Data Source=xxx;User Id=YYY; Password=ZZZ"[/TT]

---- Andy

There is a great need for a sarcasm font.

 

[gmmastros]

My connection string looks like this:

Provider=SQLOLEDB.1;Password=XXXXXXXX;Persist Security Info=True;User ID=USERNAME_HERE;Initial Catalog=DATABASENAME_HERE;Data Source=SERVERNAME_HERE

Public Sub ConnectToDatabase()
    If Not bConnected Then
        Set DB = CreateObject("ADODB.Connection")
        DB.Mode = adModeShareDenyNone
        DB.CursorLocation = adUseClient
        Call DB.Open(globalConnectionString)
        bConnected = True
    End If
End Sub

Public Sub Execute(ByVal SQL As String)
    
    Call ConnectToDatabase
    
    DB.CommandTimeout = 0
    
    Call DB.Execute("SET ANSI_NULLS ON; SET QUOTED_IDENTIFIER ON; SET ANSI_WARNINGS ON;")
    Call DB.Execute(SQL)
    
End Sub

I use this for a process I call "Update Database Structure". My app is distributed to hundreds of customers, each with multiple installations. Each customer can have several databases. When you successfully log in to my app (with a database selected), the app checks a config table that is a simple name-value pair. It checks for "database Version". If the database version is older than expected, the update database structure process occurs. This process loads SQL Statements from flat files and executes them against the database.

An example file is:

IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[AddressDeleteAddressMatch]') And OBJECTPROPERTY(id, N'IsTrigger') = 1)
	DROP TRIGGER [dbo].[AddressDeleteAddressMatch]
GO
CREATE Trigger [dbo].[AddressDeleteAddressMatch] On dbo.Address 
For Update, Delete
As
Set NoCount On

If Not Exists(Select 1 From Deleted)
	Return

Delete	AddressMatch
From	AddressMatch
	Inner Join Deleted D
		On AddressMatch.AddressId = D.AddressId

There are 1,000's of files to update the database structure. The process basically tears the db down to simple heap tables with no indexes, contstraints, functions, views, procedures etc... then it builds it back up again.

I've been doing this for the last 10+ years without too much difficulty. Hope it helps.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Andrzejek]

Let me digest it and try it out.
I'll get back if I am successful or not...

Edit: I am back, and I was able to create a Sequence!
So I should be able to do the rest as well

---- Andy

There is a great need for a sarcasm font.