×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Run DDL statements from VB

Run DDL statements from VB

Run DDL statements from VB

(OP)
Is there a way in VB6 to run this (and other) DDL statement with ORACLE data base?

SET DEFINE OFF;
CREATE SEQUENCE ANDY_SEQ
START WITH 0
MAXVALUE 999999999999999999999999999
MINVALUE 0
NOCYCLE
NOCACHE
NOORDER
NOKEEP
GLOBAL

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.

RE: Run DDL statements from VB

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

RE: Run DDL statements from VB

(OP)
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:
.ConnectionString = "Provider=OraOLEDB.Oracle;" & _
"Data Source=xxx;User Id=YYY; Password=ZZZ"


---- Andy

There is a great need for a sarcasm font.

RE: Run DDL statements from VB

My connection string looks like this:

CODE

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

CODE

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:

CODE

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

RE: Run DDL statements from VB

(OP)
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! thumbsup2
So I should be able to do the rest as well smile

---- Andy

There is a great need for a sarcasm font.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close