Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Using CASE WHEN to find out if I'm connected to a SQL Server or Oracle

Answered Using CASE WHEN to find out if I'm connected to a SQL Server or Oracle

  • Tuesday, January 01, 2013 1:23 PM
     
      Has Code
    Sign In to Vote
    0

    Here is an interesting issue that I couldn't find answers anywhere for it...

    Suppose I have an application that could connect to either a SQL Server, or an Oracle database (I don't know which).
    Against this connection the application executes the same .sql script file, regardless of the database type used.

    The script to be executed is the same for both database types (standard ANSI DML statements), except for several values that should be different depending on the database type.

    So what I need is something like this:

    INSERT INTO MyTable
VALUES(CASE WHEN IsSQL THEN 'MSSQL Value' WHEN IsOracle THEN 'Oracle Value' END)

    Is there something I could use in the CASE statement (replacing the "IsSQL" and/or "IsOracle") which in terms of syntax could work in both databases, and would let me know which database type I'm connected to?

    Thanks!

    Eitan Blumin; SQL Server Consultant - Madeira SQL Server Services; http://www.madeira.co.il/author/eitan/

     

All Replies

  • Tuesday, January 01, 2013 1:36 PM
    Answerer
     
      Has Code
    Sign In to Vote
    0

    Eitan shalom

    Is that possible that IsSQL and IsOracle can be variable and you can assign them on the connection? Or even one variable like 

    INSERT INTO MyTable
VALUES(CASE WHEN @sql ='MSSQL' THEN 'MSSQL Value' 
                         WHEN @sql ='Oracle' THEN 'Oracle Value' END)

    >>>>I have an application that could connect to either a SQL Server, or an Oracle database

    How does your application differentiate Oracle and SQL Server connection strings?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

     
  • Tuesday, January 01, 2013 2:17 PM
    Moderator
     
      Has Code
    Sign In to Vote
    0

    One way to differentiate SQL Server database from ORACLE:

    SELECT sptCount=COUNT(*) FROM master.dbo.spt_values;

    In ORACLE you should get an error.

    Kalman Toth SQL 2008 GRAND SLAM
    New Book: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012


     
  • Tuesday, January 01, 2013 2:59 PM
     
      Has Code
    Sign In to Vote
    0

    You can have a variable @isOracle bit

    ans set the variable on connection or 

    by running a query

    SELECT @@VERSION --- This should give an error in case of Oracle

    and later insert the values as below

    INSERT INTO MyTable
VALUES(CASE WHEN @IsOracle = 0 THEN 'MSSQL Value' WHEN @IsOracle = 1 THEN 'Oracle Value' END)
    Regards
    Satheesh
     
  • Tuesday, January 01, 2013 3:06 PM
     
     Answered
    Sign In to Vote
    0
    Is it possible to store the values in a table and do INSERT ... FROM?  Set the tables up on each server, populate with different values, then it doesn't matter which server you are connected to.  Just an idea - it may not be possible depending on what you're doing.
     
  • Tuesday, January 01, 2013 3:13 PM
    Moderator
     
     Answered
    Sign In to Vote
    0
    I looked up several sites devoted to Oracle trying to find anything common we can use in both SQL Server and ORACLE, such as SERVERPROPERTY, for example. So far I found none. There are some common views in Oracle and SQL Server (check this page http://www.razorsql.com/articles/oracle_system_queries.html), however, the name column is named 'name' in SQL Server and object_name, view_name, etc. in ORACLE, so I am not sure if we can use this fact to our advantage.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked As Answer by EitanBlumin Tuesday, January 01, 2013 6:12 PM
    •  
     
  • Tuesday, January 01, 2013 3:29 PM
     
     
    Sign In to Vote
    0

    Thank you all for your suggestions!

    @Uri Diamant: Unfortunately no, I can't make any changes to the script upon connection.

    @Kalman Toth and @Satheesh Variath: Unfortunately I cannot afford to have errors coming up during executions. I need the script to be valid in both database types.

    @wBob: That's a good idea. I will check with my client whether it's possible. Thanks!

    @Naomi N: Yes that's what I found too, I was hoping that I missed something.

    Eitan Blumin; SQL Server Consultant - Madeira SQL Server Services; http://www.madeira.co.il/author/eitan/

     
  • Tuesday, January 01, 2013 3:49 PM
    Moderator
     
      Has Code
    Sign In to Vote
    0

    How about the following test:

    SELECT DISTINCT [COLLATION_NAME]
FROM [INFORMATION_SCHEMA].[COLUMNS]
WHERE COLLATION_NAME IS NOT NULL;
-- SQL_Latin1_General_CP1_CI_AS

    It is likely to be different for ORACLE default collation.

    Kalman Toth SQL 2008 GRAND SLAM
    New Book: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012




     
  • Tuesday, January 01, 2013 4:54 PM
     
     
    Sign In to Vote
    0

    @Kalman Toth: Thanks, that's also a good idea.
    However, my client's Oracle database doesn't seem to have this schema. Is it available only in certain versions?

    Eitan Blumin; SQL Server Consultant - Madeira SQL Server Services; http://www.madeira.co.il/author/eitan/


    • Edited by EitanBlumin Tuesday, January 01, 2013 4:55 PM
    •  
     
  • Tuesday, January 01, 2013 5:24 PM
    Moderator
     
     
    Sign In to Vote
    0

    Hi Eitan,

    Can you list what ANSI INFORMATION_SCHEMA views are available in the ORACLE DATABASE?

    Do you see anything in the schemas which would be different for Microsoft SS?

    Kalman Toth SQL 2008 GRAND SLAM
    New Book: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012


     
  • Tuesday, January 01, 2013 6:34 PM
     
     
    Sign In to Vote
    0

    Are you are working on developing a generic front-end and let the user decide what back-end they want to use? Or is the application expected to talk to both SQL Server and Oracle at the same time e.g. to make the legacy systems transparent to the user?

    If it is the former, then what happens if the user defines a connection string to a third product e.g. DB2?

    This URL lists quite an extensive comparison of various DBMS SQL implementations -
    http://troels.arvin.dk/db/rdbms/

    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.
    Thanks!
    Aalam | Blog (http://aalamrangi.wordpress.com)

     
  • Tuesday, January 01, 2013 10:20 PM
     
     
    Sign In to Vote
    0

    INFORMATION_SCHEMA comes from the ANSI standard, so it "should" be in Oracle. But, alas, something being in the standard does not mean that all vendors have implmented it. Else, Kalman's idea is probably the best one.

    One more idea is to look at the USER function - this is also ANSI, so it "should" be in Oracle. This assumes that you somehow can distinguish an Oracle user from an SQL Server user. If you can make the assumption to SQL Server is always by dbo or by a Windows user, there is something to look for.

    Then again, if you are not permitted to make decisions on client level, this will be very difficult. You will have to stick to the lowest common denominator and it will still be an uphill battle. Of course, it depends on what you want to do, but the mere fact that you ask how to in your script find out on which platform you are, indicates that you are doing things that are overly advanced. For instance, you cannot use variables in your script, since PL/SQL and T-SQL have different conventions for variables.

    I think your only option is to write your script to be entirely agnostic. And if that is not possible, it's time to tell the client that it's back to the drawing board.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
     
  • Tuesday, January 01, 2013 10:40 PM
     
     Answered
    Sign In to Vote
    1

    INFORMATION_SCHEMA comes from the ANSI standard, so it "should" be in Oracle. But, alas, something being in the standard does not mean that all vendors have implmented it.

    Confirmed. Found http://docs.oracle.com/cd/E11882_01/server.112/e26088/ap_standard_sql003.htm#i7719
    which says, point F021, Oracle does not have any of the views in this feature.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked As Answer by EitanBlumin Wednesday, January 02, 2013 8:28 AM
    •