Re: Oracle, ODBC and SqlServer
Date: 1997/05/23
Message-ID: <5m4lg9$916_at_gap.cco.caltech.edu>
TurkBear (jvgreco_at_primenet.com) wrote:
> I am using Power Objects and trying to set up a session that connects
> to a SqlServer database using ODBC - Other programs (Crystal Reports,
> etc) can do this OK) ... After defining the connect string it appears
> to connect, but does not show any Table Names ( yet, if I try to Add
> an Index, give it a table name,column name, index name [ in the dialog
> box that pops up) it correctly informs me that I can's since I am not
> the owner of the table - so it is connected to the database)
> ps the connect string is
> odbc:service_name;UID=dddddd;PWD=xxxxx;SRVR=nnnnnn;Database=mmmmmm
> ( the lower case letters are subs for the real thing;security you know
> :-) )
> Help,
> Thanks
> John Greco
I'm not sure if this applies to Power Objects, but I'll bet it does. OPO probably gets table names and columns names to display to the user from the Oracle System Catalogs. SqlServer was not kind enough to create the same views in their databases (how inconsiderate of them). Consequently, OPO cannot display Table Names, view names, etc.
However, since commands like CREATE INDEX do their own checking for tables, they will still work correctly.
So, you cannot see what tables, views, etc., exist in your database, but you can still use them.
The Open Client Adapter for OCA provides some scripts for creating Oracle style system-catalog tables from SqlServer catalog tables. If you have Developer/2000, you can find it on the CD (they will be installed in $(ORACLE_HOME)\odbc10\ somewhere, if you've actually installed the product). For your convenience, I've included the script at the bottom here.
Note: SqlServer requires AutoCommit mode to be set explicitly before doing any DDL. Therefore, you must put your database in AutoCommit mode before you create these tables. I recommend modifing this script and running in through ISQL, setting autocommit on first.
This scrips is written to run through SQL*Plus using the Oracle Open Client Adapter 2.0, which is not available yet. Therefore, you'll have to modify it by hand to run it through isql, but I have faith in you :)
- Art
- BEGIN script --
/* Copyright (c) 1993, 1997 by the Oracle Corporation */
/* -------------------------------------------------------------------- ** NAME SQS60BLD.SQL - SQL script to create Oracle data ** dictionary views on SQL Server database. ** ** MODIFIED ** aclarke 01/16/97 - Added ALL_SOURCE and ALL_ERRORS tables.** kmuniasa 03/21/96 - Modifed for SQL Server 6.0 based on 4.21 scripts
** -------------------------------------------------------------------- */ /* ** SQS60BLD.SQL ** ** ** BUILDS THE FOLLOWING TABLES/VIEWS FOR ORACLE TOOLS AND GRANTS ** PUBLIC SELECT PRIV. THESE VIEWS ARE NECESSARY FOR ORACLE TOOLS. ** ** ALL_USERS ** ALL_OBJECTS ** ALL_TABLES ** ALL_VIEWS ** ALL_SYNONYMS ** ALL_TAB_COLUMNS ** ALL_SOURCE ** ALL_ERRORS ** USER_USERS ** USER_SYNONYMS ** ACCESSIBLE_COLUMNS ** PRODUCT_USER_PROFILE
**
*/
DROP INDEX S2ODTMAP.IS2ODTMAP;
DROP TABLE S2ODTMAP;
DROP TABLE ALL_SYNONYMS;
DROP TABLE USER_SYNONYMS;
DROP TABLE PRODUCT_USER_PROFILE;
DROP VIEW ALL_USERS; DROP VIEW ALL_OBJECTS; DROP VIEW ALL_TABLES; DROP VIEW ALL_VIEWS; DROP VIEW ALL_SOURCE;
DROP TABLE ALL_ERRORS;
DROP VIEW ALL_TAB_COLUMNS;
DROP VIEW USER_USERS;
DROP VIEW ACCESSIBLE_COLUMNS;
DROP TABLE TOOL_MODULE;
DROP TABLE ALL_TRIGGERS;
CREATE TABLE S2ODTMAP(STYPE int, SNAME varchar(13), ONAME varchar(12)); INSERT INTO S2ODTMAP VALUES( 34, 'image', 'LONG RAW' ); INSERT INTO S2ODTMAP VALUES( 35, 'TEXT', 'LONG' ); INSERT INTO S2ODTMAP VALUES( 37, 'VARBINARY', 'RAW' ); INSERT INTO S2ODTMAP VALUES( 38, 'INTN', 'NUMBER' ); INSERT INTO S2ODTMAP VALUES( 39, 'VARCHAR', 'VARCHAR' ); INSERT INTO S2ODTMAP VALUES( 45, 'BINARY', 'RAW' ); INSERT INTO S2ODTMAP VALUES( 47, 'CHAR', 'CHAR' ); INSERT INTO S2ODTMAP VALUES( 48, 'TINYINT', 'NUMBER' ); INSERT INTO S2ODTMAP VALUES( 50, 'BIT', 'RAW' ); INSERT INTO S2ODTMAP VALUES( 52, 'SMALLINT', 'NUMBER' ); INSERT INTO S2ODTMAP VALUES( 56, 'INT', 'NUMBER' ); INSERT INTO S2ODTMAP VALUES( 58, 'SMALLDATETIME','DATE' ); INSERT INTO S2ODTMAP VALUES( 59, 'REAL', 'NUMBER' ); INSERT INTO S2ODTMAP VALUES( 60, 'MONEY', 'NUMBER' ); INSERT INTO S2ODTMAP VALUES( 61, 'DATETIME', 'DATE' ); INSERT INTO S2ODTMAP VALUES( 62, 'FLOAT', 'NUMBER' ); INSERT INTO S2ODTMAP VALUES(109, 'FLOATN', 'NUMBER' ); INSERT INTO S2ODTMAP VALUES(110, 'MONEY', 'NUMBER' ); INSERT INTO S2ODTMAP VALUES(111, 'DATETIMN', 'DATE' ); INSERT INTO S2ODTMAP VALUES(122, 'SMALLMONEY','NUMBER' );
CREATE INDEX IS2ODTMAP ON S2ODTMAP(STYPE, ONAME);
CREATE VIEW ALL_USERS AS
SELECT name USERNAME, uid USER_ID FROM sysusers;
CREATE VIEW ALL_OBJECTS AS
SELECT SU.name OWNER, SO.name OBJECT_NAME,
SUBSTRING('TABLE', 1,5 * (1-ABS(SIGN(ASCII(SO.type) - ASCII('U'))))) +
SUBSTRING('VIEW', 1,4 *
(1-ABS(SIGN(ASCII(SO.type) - ASCII('V'))))) +
SUBSTRING('PROCEDURE', 1,9 *
(1-ABS(SIGN(ASCII(SO.type) - ASCII('P')))))
OBJECT_TYPE,
SO.crdate CREATED, 'VALID' STATUS
FROM sysusers SU, sysobjects SO
WHERE SU.uid = SO.uid;
CREATE VIEW ALL_SOURCE as
SELECT SU.name OWNER, SO.name NAME,
SUBSTRING('TABLE', 1,5 * (1-ABS(SIGN(ASCII(SO.type) - ASCII('U'))))) +
SUBSTRING('VIEW', 1,4 * (1-ABS(SIGN(ASCII(SO.type) -
ASCII('V'))))) +
SUBSTRING('PROCEDURE', 1,9 * (1-ABS(SIGN(ASCII(SO.type)
- ASCII('P')))))
TYPE, 1 LINE, SC.TEXT TEXT
FROM sysusers SU, sysobjects SO, syscomments SC WHERE SU.uid = SO.uid
AND SO.id = SC.id;
CREATE TABLE ALL_ERRORS (
OWNER VARCHAR(30) NOT NULL, NAME VARCHAR(30) NOT NULL, TYPE VARCHAR(12) NULL,
SEQUENCE INT NOT NULL,
LINE INT NOT NULL,
POSITION INT NOT NULL,
TEXT VARCHAR(255) NOT NULL
);
CREATE VIEW ALL_TABLES AS
SELECT SU.name OWNER, SO.name TABLE_NAME
FROM sysusers SU, sysobjects SO
WHERE SU.uid = SO.uid
AND SO.type = 'U';
CREATE VIEW ALL_VIEWS AS
SELECT SU.name OWNER, SO.name VIEW_NAME
FROM sysusers SU, sysobjects SO
WHERE SU.uid = SO.uid
AND SO.type = 'V';
/* ** Note: ALL_TAB_COLUMNS will return all column names in uppercase ** because PL/SQL only supports case insensitive and expects ** the columns names to be in uppercase to verify columns ** within PL/SQL.
*/
CREATE VIEW ALL_TAB_COLUMNS AS
SELECT SU.name OWNER, SO.name TABLE_NAME,
UPPER(SC.name) COLUMN_NAME, S2ODTMAP.ONAME DATA_TYPE,
(22 * SIGN(CHARINDEX('NUMBER',S2ODTMAP.ONAME))) +
(SC.length * (1-SIGN(CHARINDEX('NUMBER',S2ODTMAP.ONAME)))) DATA_LENGTH,
SC.colid COLUMN_ID,
SC.length DATA_PRECISION,
NULL DATA_SCALE,
'Y' NULLABLE,
SC.cdefault DATA_DEFAULT
FROM sysusers SU, sysobjects SO, syscolumns SC, S2ODTMAP WHERE
SU.uid = SO.uid AND
SO.id = SC.id AND
SC.type = S2ODTMAP.STYPE;
CREATE VIEW ACCESSIBLE_COLUMNS AS
SELECT * FROM ALL_TAB_COLUMNS;
CREATE VIEW USER_USERS AS
SELECT name USERNAME, uid USER_ID, 1 CONNECT_PRIV, 1 RESOURCE_PRIV, 0 DBA_PRIV
FROM sysusers;
CREATE TABLE ALL_SYNONYMS (
OWNER char(30) NOT NULL, SYNONYM_NAME char(30) NOT NULL, TABLE_OWNER char(30), TABLE_NAME char(30) NOT NULL, DB_LINK char(128) );
CREATE TABLE USER_SYNONYMS (
SYNONYM_NAME char(30) NOT NULL, TABLE_OWNER char(30), TABLE_NAME char(30) NOT NULL, DB_LINK char(128) );
create table PRODUCT_USER_PROFILE
(
PRODUCT char(30), USERID char(30), ATTRIBUTE char(240), SCOPE char(240), NUMERIC_VALUE int, CHAR_VALUE char(240), DATE_VALUE datetime, LONG_VALUE int
);
create table TOOL_MODULE
(
OWNER char(30),
MODTYPE char(30),
MODNAME char(30)
);
create table ALL_TRIGGERS
(
OWNER VARCHAR(30) NOT NULL,
TRIGGER_NAME VARCHAR(30) NOT NULL,
TRIGGER_TYPE VARCHAR(16),
TRIGGERING_EVENT VARCHAR(26),
TABLE_OWNER VARCHAR(30) NOT NULL,
TABLE_NAME VARCHAR(30) NOT NULL,
REFERENCING_NAMES VARCHAR(87),
WHEN_CLAUSE text,
STATUS VARCHAR(8),
DESCRIPTION text,
TRIGGER_BODY image
);
/*
** Grant permission to public on all views
*/
GRANT SELECT ON S2ODTMAP TO PUBLIC; GRANT SELECT ON ALL_USERS TO PUBLIC; GRANT SELECT ON ALL_OBJECTS TO PUBLIC; GRANT SELECT ON ALL_TABLES TO PUBLIC; GRANT SELECT ON ALL_VIEWS TO PUBLIC; GRANT SELECT ON ALL_SYNONYMS TO PUBLIC; GRANT SELECT ON ALL_SOURCE TO PUBLIC; GRANT SELECT ON ALL_ERRORS TO PUBLIC;
GRANT SELECT ON ALL_TAB_COLUMNS TO PUBLIC; GRANT SELECT ON ACCESSIBLE_COLUMNS TO PUBLIC; GRANT SELECT ON USER_USERS TO PUBLIC;
GRANT SELECT ON USER_SYNONYMS TO PUBLIC; GRANT SELECT ON PRODUCT_USER_PROFILE TO PUBLIC; GRANT SELECT ON TOOL_MODULE TO PUBLIC;
GRANT SELECT ON ALL_TRIGGERS TO PUBLIC; SPOOL OFF
--Received on Fri May 23 1997 - 00:00:00 CEST