This discussion is archived
6 Replies Latest reply: Feb 20, 2013 9:05 AM by jim.marion RSS

Accessing different database using PeopleCode

991212 Newbie
Currently Being Moderated
Hello Everyone-

I am trying to access SQL Server database which is on windows server, using People Code. For People Soft we are using Oracle 11g database on Unix box. Is there an option to connect to the SQL Server and get the data from the record ?

Regards,
  • 1. Re: Accessing different database using PeopleCode
    Michel Journeyer
    Currently Being Moderated
    There is an option. You'll need to install a Unix ODBC driver and create a database link in Oracle. Using this database link you can select data from records in the SQL server database. I'm not sure which driver is best for that, but Microsoft provide one and unixODBC:

    http://www.microsoft.com/en-us/download/details.aspx?id=28160
    http://www.unixodbc.org/
  • 2. Re: Accessing different database using PeopleCode
    991212 Newbie
    Currently Being Moderated
    Thank you Michel for your reply, do you mean creating database link between Oracle Database server on UNIX box to SQL Server database on Windows Box?

    How can we authenticate the user id from database level for Windows Authentication for SQL Server?
  • 3. Re: Accessing different database using PeopleCode
    Michel Journeyer
    Currently Being Moderated
    Yes that is exactly what I meant. You specify a user id and password when setting up the ODBC connection to the SQL server. You can create a new user id on the SQL server specifically for that goal. You can then give that specific user id access rights to several tables.
  • 4. Re: Accessing different database using PeopleCode
    991212 Newbie
    Currently Being Moderated
    Thank you Michel, have you done or been involved with similar issues previously, because in some forums it is stated that we cannot create a database link from Oracle to SQL Server we need to have a third party software to make it work, however we can create a link from SQL Server to Oracle database to get the data.

    If you have done it in the past did you use any third party software or its a direct link?
  • 5. Re: Accessing different database using PeopleCode
    Michel Journeyer
    Currently Being Moderated
    The third party software you are referring to are probably the drivers I have mentioned in a previous post. With the drivers you should be able to setup an ODBC connection to SQL server from an Oracle database. Personally I've never been involved in setting up such a thing, but I have seen it work this way.

    Check out the following blog:

    http://ubuntu42.blogspot.nl/2013/02/use-oracle-database-gateway-for-odbc-to.html

    This should be comprehensive enough to help you with setting it up.
  • 6. Re: Accessing different database using PeopleCode
    jim.marion Pro
    Currently Being Moderated
    An alternative to ODBC configuration is JDBC. You can find several posts on using JDBC with PeopleSoft to access different databases on my blog here: http://jjmpsj.blogspot.com/search/label/JDBC. My examples are Oracle specific. This example is JDBC generic: http://psst0101.wordpress.com/2008/07/29/writing-to-access-databases/

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points