Archive for August, 2013

Configuring Oracle SQL Developer for Microsoft SQL Server and MySQL

Configuring Oracle SQL Developer for Microsoft SQL Server and MySQL

Most of DBAs I know, use TOAD for doing daily tasks, but I prefer Oracle SQL Developer. In my opinion, it has 3 important advantages against TOAD:

  • It’s platform-independent: Although I use Windows on my laptop now, I’m a big fan of Linux and Solaris, and I don’t like being dependent on a specific OS. Thanks to Java, Oracle SQL Developer is platform-independent.
  • It supports multiple databases: You can use SQL Developer for Microsoft Access, Microsoft SQL Server, MySQL, TimesTen, DB2 and (of course) Oracle.
  • It’s extendible: Yes I know that there are not too much documents about it, but it’s extensible. For example, check FourthElephant’s extensions for SQL Developer: http://www.fourthelephant.com/sqldeveloper/download/

TOAD has a better interface because of using native Windows components but you can get used to SQL Developer if you spend time on it. Anyway, this blog has nothing to do with comparing SQL Developer and TOAD. I’ll just show how to configure SQL Developer to connect Microsoft SQL Server and MySQL.

You need to download and install the required (and supported) JDBC drivers to make Oracle SQL Developer connect MySQL and Microsoft SQL Server.

For MySQL download the J/connecter from http://www.mysql.com/products/connector/

For Microsoft SQL Server download the jTDS from http://sourceforge.net/projects/jtds/. The JDBC driver which is published by Microsoft doesn’t work with Oracle SQL Developer, at least I couldn’t make it work.

After you download the JDBC drivers, unzip them and then open prefences window in SQL Developer ( tools >> preferences ):

To add JDBC driver for Microsoft SQL Server, click “add entry…” button and find “jtds-1.2.5.jar” in the file open dialog.

To add JDBC driver for MySQL, click “add entry…” button and find “mysql-connector-java-5.1.17-bin.jar” in the file open dialog.

You need to restart SQL Developer to make these new JDBC drivers active. Then you can create a connections for Microsoft SQL Server, Sybase (because JDTS supports Sybase) and MySQL:

that’s it.

Advertisements

Use Oracle’s FREE SQL Developer as a Microsoft SQL Server GUI on OSX

Use Oracle’s FREE SQL Developer as a Microsoft SQL Server GUI on OSX

I’m on a Mac, my company uses a SQL Server, and since there is not native SQL Server Enterprise manager client I was forced to find a workaround. One common solution is to have aVirtual Machine running windows but to me that approach feels like using a sledgehammer to pound in a finishing nail.  Thanks to this post on stackoverflow I am able to use Oracle’s FREE SQL Developer application to connect to Microsoft SQL Server Instances on my MacBook Pro running Lion.  I’ve used SQL Developer for Oracle development for a number of years now so the learning curve was pretty small for me but anybody who is familiar with SQL Server Enterprise Manager should be able to find their way around pretty easily, however there are a few things to be aware of which I’ll discuss a bit later.

Installation

Before you can install SQL Developer you’ll need to make sure that you have the JDK installed on your machine.  I have the Java for Mac Developer Preview installed on my machine from: https://developer.apple.com/downloads/.

Once the JDK is installed (you can check by opening a terminal and typing  javac -version ) the next step is to download and install SQL Developer from Oracle’s website here.  Once SQL Developer is installed you’ll need to get the jDTS plugin files from: http://sourceforge.net/projects/jtds/files/, at the time of this writing the most current version is 1.2.5.  Once the zip is download and extracted move the entire folder someplace where your account will have execute permissions on the jdsts-1.2.3.jar file.  I have a lib directory in my User folder where I keep all my third party .jar files.

You now have everything you need to get SQL Developer executing SQL queries, you just need to do some simple setup in SQL Developer.  Open SQL Developer and as of version 3.0 go to Tools -> Preferences  and expand the “Database” node.  Next click on Third Party JDBC Drivers click on “Add Entry…”.  From this menu browse to the /jdts-1.2.5-dist folder you copied in the step above and select jdts-1.2.5.jar (or whichever version corresponds to the version you downloaded) and click “OK”.

Configuration

SQL Server Connection Tab

Once you have the plugin installed exit out to the main SQL Developer IDE and add a new connection by either: clicking on File -> New -> Database Connection or by clicking on the green + icon in the “Connections” tab.   You should now see a “SQLServer” tab where you can enter the connection information for your SQL Server.

Using SQL Developer to Query Microsoft SQL Server Databases

Select Default Database
If you have not used SQL Developer before there are a few things to be aware of.  Since it is primarily an Oracle IDE you have to think in a somewhat “Oracle” way when running queries on your database. Since Oracle is a schema-centric database server you’ll save yourself lots of typing if you set a default database for your current connection.   If you don’t you’ll have to type the fully qualified table name for each table you reference in a query (e.g. database.owner.tablename).  To do this right click on the database you’d like to use and click on “Select Default Database” in the context menu.  Once you do this you can do normal SELECT x FROM Table queries and leave out the fully qualified table name.  You’ll have to do this each time you connect to SQL Server.
SQL Developer will allow you to do many of the basic database tasks you can do in Enterprise Manager, however there are quite a few limitations. You can view your Table/View structure and data, you can also view most of the other objects in SQL Server like Stored Procedures and Functions.   SQL Developer will also allow you write DML (Data Manipulation Language) statements like Update, Delete, and Insert.   You can also write some DDL (Data Design Language) statements like ALTER TABLE.  However, the one huge shortcoming of SQL Developer is its inability to recognize the “BEGIN” keyword.  This means that any statement that uses BEGIN, like transactions, stored procedures, and functions will all generate a syntax error and not execute.One nice “benefit” of using SQL Developer is the “Format” context menu.  When I’m writing code I often get SQL statements in debugging code, however, its usually one long string, debugging this code in Enterprise Manager usually meant having to manually update format the SQL, however in SQL Developer I can paste the code into the SQL Worksheet, right click, and click on “Format” and have a nicely formatted SQL Statement.

Conclusion

While its not a complete replacement for Enterprise Manager I do find that SQL Developer will allow me to do many of the things I spend 90% of my time doing to my databases.  The best part is that this is a completely Free and somewhat mature solution.

Making Database Connections to Remote SQL Server 2008 using Oracle SQL Developer

Making Database Connections

Connect to Oracle and third-party databases from Oracle SQL Developer.

Oracle SQL Developer enables developers and DBAs to browse, create, and update data in a database. Before you can perform those actions, however, you must create at least one database connection —an Oracle SQL Developer object containing the information needed to connect to a specific database as a specific user.

This column explains how to make connections from Oracle SQL Developer to an Oracle Database and third-party databases. It also discusses authorization options for Oracle Database connections and explains the role of JDBC drivers in the connection process.

All the examples in this column require Oracle SQL Developer to be running on your local machine. For some examples, you need access to a running local or remote Oracle Database instance with the sample HR schema (available in the default database installation). Other examples require access to an Oracle Internet Directory server or a third-party database.

Basic Connections

You can connect to a local or remote Oracle Database instance by using the Basic connection type. Basic connections do not require any other Oracle software to be installed on your machine—you don’t need an Oracle home.

Right-click the Connections node in Oracle SQL Developer’s Connections Navigator, and click New Connection to open the New / Select Database Connection dialog box. All of this column’s connection examples start from this dialog box.

To create a basic connection for the HR schema, follow these steps:

1. Enter HR_ORCL for Connection Name . The connection name is an arbitrary alias; conventionally, it’s a combined username and database name.

2. Enter hr for Username , and enter the hr password in the Password field. (If you check the Save Password box, the password will be stored as an encrypted file on your local machine.)

3. Select Basic from the Connection Type list.

4. Provide information for the following settings:

  • Role: This is the set of privileges to be associated with the connection. Accept default for this connection.
  • OS Authentication: Leave this unchecked for this connection.
  • Proxy Connection: Leave this unchecked for this connection.
  • Hostname: This is the host system for the Oracle Database instance. Enter an IP address, a machine name, or localhost (when connecting to a database on the same machine as Oracle SQL Developer). The default is localhost .
  • Port: This is the listener port for the database. The default port for Oracle Database is 1521.
  • SID: This is the system identifier, such as orcl (the default for Oracle Database 10g and Oracle Database 11g) or xe (the default for Oracle Database 10g Express Edition).
  • Service name: This is the network service name of the database. Select either SID or Service name.

5. Click Test to validate the new connection. Figure 1 shows the dialog box after the connection has validated successfully.

6. If your test reports “Status: Success,” click Connect . Oracle SQL Developer will save the new connection, close the dialog box, and connect to the database. (If you click Save instead of Connect , you will be able to create more new connections in the dialog box before connecting.)

Now HR_ORCL appears in Connections Navigator, and you can expand it to browse the database.

TNS Connections

The TNS connection type is an appropriate option in any of the following circumstances:

You have an Oracle client installed on your machine. You have access to many Oracle Database instances. You do not know the machine details of the system hosting the Oracle Database instance you want to connect to.
A TNS connection uses an alias entry from a tnsnames.ora file. Oracle SQL Developer uses only one tnsnames.ora file. You may have more than one on your local machine or want to use the tnsnames.ora file on a remote machine, so note that Oracle SQL Developer looks sequentially for the tnsnames.ora file in the following locations:

1. $HOME/.tnsnames.ora
2. $TNS_ADMIN/tnsnames.ora
3. /etc/tnsnames.ora (non-Windows systems)
4. $ORACLE_HOME/network/admin/tnsnames.ora
5. Registry key

On Windows systems, if a tnsnames .ora file exists but Oracle SQL Developer isn’t using it, create a TNS_ADMIN environment variable via Control Panel -> System -> Advanced -> Environment Variables , specifying the file’s location as the variable’s value.

Follow these steps to create a TNS connection in Oracle SQL Developer:

1. In the New / Select Database Connection dialog box, enter the same connection name, username, and password you used for the basic connection.

2. Select TNS from the Connection Type list. The GUI changes slightly to provide a list of all network alias entries available to you. Select an alias.

3. Click Test and Connect as before.

LDAP Connections

Oracle Internet Directory is a directory service that enables you to store and manage network service names centrally. With it, user identity information can be stored in a directory instead of in multiple databases. Oracle Internet Directory is an implementation of the LDAP directory service and a component of Oracle Identity Management. For information on how to install, set up, and configure Oracle Internet Directory, see the Oracle Internet Directory Administrator’s Guide.

To look up database services in Oracle Internet Directory and create an LDAP connection in Oracle SQL Developer, follow these steps:

1. In the New / Select Database Connection dialog box, enter a new connection name, username, and password for the database user.

2. Select LDAP from the Connection Type list.

3. Select a server from the LDAP Server list, which is populated with entries from an ldap.ora file (similar to the tnsnames.ora file). Alternatively, you can enter LDAP server details directly.

4. Fill in the LDAP Admin User and LDAP Admin Password fields.

5. Click Load to populate the DB Service list with all the database service entries from Oracle Internet Directory.

6. Select a service from the DB Service list, as shown in Figure 2.

7. Click Test and Connect as before.

Other Authentication Options

Alternatives to database authentication and LDAP authentication include operating system (OS) and proxy authentication. You can create connections in Oracle SQL Developer for users who authenticate with these mechanisms. OS authentication. With OS authentication, Oracle Database uses a database user’s OS login credentials to authenticate that user. The user doesn’t provide a username or password to access the database, and Oracle Database doesn’t store and manage the account password. Local OS authentication can be used when the client and the database server are on the same machine. Remote OS authentication is possible but is not considered secure.

To configure local OS authentication for a new user, first find the value of the OS_AUTHENT_PREFIX database initialization parameter in your system’s init.ora file. When you create this new user in the database, you must add this parameter value as a prefix to the OS username. The default value is OPS$, for backward compatibility with earlier database releases. (If the value is “”, the OS username and the database username are the same, so you don’t need to add a prefix to create the Oracle usernames.)

Establish a basic connection with the HR schema as the SYSTEM user. Execute the following from the SQL worksheet, using your database’s OS_AUTHENT_PREFIX prefix and substituting your own OS username for “sue”:

CREATE USER ops$sue IDENTIFIED EXTERNALLY;
GRANT Connect, resource to sue;

Now create a basic connection for this user from the New / Select Database Connection dialog box. Enter a connection name; select Basic for Connection Type ; fill in the Hostname and Port fields; select OS Authentication ; and provide a SID or Service name . Click Test and Connect as before.

Proxy authentication. Proxy authentication means that one JDBC connection acts as a proxy for other JDBC connections. Before you can create a connection that uses proxy authentication, you need a proxy user. In the following example, you create a new user named HR_PROXY_USER and connect that user through the existing HR user.

To create the new proxy user (HR_PROXY_USER) and grant the correct proxy authentication privileges, execute the following in the SQL worksheet:

CREATE USER HR_proxy_user IDENTIFIED BY <
                               password>;
ALTER USER HR_proxy_user GRANT CONNECT THROUGH HR AUTHENTICATED USING PASSWORD;

You also need to grant any other privileges required by the new user, such as CREATE SESSION.

Once your proxy user exists, you can create a new proxy connection for HR in Oracle SQL Developer. Select Proxy Connection in the New / Select Database Connection dialog box, and complete the details in the Oracle Proxy Connection dialog box, as shown in Figure 3.

Connecting to Third-Party Databases

Oracle SQL Developer supports browsing and reviewing data and objects in Microsoft Access, MySQL, Microsoft SQL Server, and Sybase. It also offers a SQL worksheet for ANSI SQL commands to update or create objects for these databases. Users who want to migrate from third-party databases to Oracle Database can use the Oracle SQL Developer Migration Workbench. For any of these situations, you need to create a connection to your third-party database.

Installing third-party drivers. The correct third-party driver must be installed before you can create the connection. You can install third-party drivers either manually or by using Check for Updates ( Help->Check for Updates ). For manual installation, you can download supported drivers from the following locations:

  • MySQL JDBC driver, version 5.08: dev.mysql.com/downloads/connector/j/5.0.html.
  • jTDS driver, version 1.2 (required by Microsoft SQL Server and Sybase): source forge.net/project/showfiles.php?group_id=33291. (See jtds.sourceforge.net for information about this driver.)

Microsoft Access does not require an additional driver, because it uses a JDBC/ODBC bridge.

After downloading the driver you need, expand the driver binary Java Archive (JAR) file, which is typically inside the downloaded archive file:

  • The mysql-connector-java-5.0.8.tar.gz (or .zip) download for MySQL includes mysql-connector-java-5.0.8-bin.jar.
  • The jtds-1.2-dist.zip file for Microsoft SQL Server and Sybase includes jtds-1.2.jar.

Select Tools -> Preferences -> Database -> Third Party JDBC Drivers . Click Add Entry , and add your specific JAR file. Now you can create a connection for your third-party database.

Creating a Microsoft Access connection. To create a connection for Microsoft Access, follow these steps in the New / Select Database Connection dialog box:

1. Enter a connection name.
2. Click the Access tab.
3. Click Browse to locate the .mdb file you want to work with.
4. Click Connect.

You can now expand and browse the new Microsoft Access connection in the Connections Navigator.

Creating a Microsoft SQL Server, Sybase, or MySQL connection. To create a connection to a Microsoft SQL Server, Sybase, or MySQL database in the New / Select Database Connection dialog box, follow these steps:

1. Enter a connection name, username, and password.
2. Click the SQL Server, Sybase , or MySQL tab. Figure 4 shows the SQL Server tab.

figure 1
Figure 1: Basic database connection type
figure 2
Figure 2: LDAP connection with a list of database services
figure 3
Figure 3: Creating a proxy connection
figure 43
Figure 4: Connecting to Microsoft SQL Server

3. Choose one of the password authentication options (each of these connections offers you multiple choices).
4. Fill in the Hostname and Port fields. The default port is 1433 for Microsoft SQL Server, 5000 for Sybase, and 3306 for MySQL.
5. Click Choose database to populate the database list, and select the appropriate database from the list.
6. Click Test and Connect as before.
You can now browse your Microsoft SQL Server, Sybase, or MySQL database in the Connections Navigator.

Conclusion

Oracle SQL Developer provides an array of alternatives for connecting to Oracle and third-party databases. Support for both Oracle JDBC Type IV (thin) and Type II (thick) drivers lets you access Oracle Databases via basic, TNS, or LDAP connection types. You can make connections for Oracle Database users who authenticate via database, OS, or proxy authentication. Connecting to a third-party database from Oracle SQL Developer lets you work in that database and gives you a starting point for migrating to Oracle Database.

Using Oracle SQLDeveloper to access SQLServer

Using Oracle SQLDeveloper to access SQLServer

It is a pretty cool feature to use Oracle’s SQLDeveper 1.1 to access SQLServer.
The steps are:

  • Download jTDS (open-source SQLServer JDBC driver) from here. Unzip and extract the jtds-1.2.jar or whatever the latest version.
  • Start Oracle’s SQLDeveloper, Tools->Preferences->Database->Third Party JDBC Drivers. Click “Add Entry” and point to the jtds-1.2.jar
  • Create a new connection, choose SQLServer tab, type in hostname, port, username and password. It appears that the initial connection name has to be the same as the database and you can click the “Retrieve database” button. Once you found the database, you can rename the connection.

Try it out.
Of course, certain things don’t work. Like explain plan and auto trace.

Per comments below, please make sure jtds 1.2 is used. Apparently, 1.3 does not work.

Using *safari for Running our Tests on Safari Browser

Using *safari for Running our Tests on Safari Browser

After a lot of research I’ve found that only the following combination works for running our tests on Safari Browser:

Mac OS + Safari Browser -> This will work without any problems.
Windows 7 + Safari Browser -> This may run the tests but the final result will fail though the test executed without any fails. Hence not suggested.
What happened when I tried Running Selenium RC Tests on Windows 7 OS + Safari Browser:
1. I disabled the Pop-Up blocker in Safari Browser as  shown below:
2. Copied the Safari Folder path in C:/Program files as shown below:
3. Click on ‘Start’ Button, Right Click on the ‘Computer’ option in the start Menu and select ‘Properties’ option as shown below:
4. Click on ‘Advanced System Settings’ option and ensure that the ‘System Properties’ dialog is displayed.  In the ‘Advanced’ tab of ‘System Properties’ dialog select ‘Environment Variables’ button as shown below:
5. Ensure that ‘Environment Variables’ sub dialog is displayed, select the Path variable and click on ‘Edit’ button as shown below:
6. Ensure ‘Edit User Variable’ sub dialog is displayed and add a semi colon at the end of the existing statements followed by pasting the above copied path in step 2 into the Variable Value field and click on ‘OK’ button as shown below:
7.  Click on ‘OK’ buttons on the remaining ‘Environment Variables’ and ‘System Properties’ dialog.
8.  In Eclipse IDE, I’ve replaced *iexplore with *safari and Ran the Selenium RC Test using JUnit Test option. Unfortunately the Test didn’t Run.
9. So I have then replaced *safari with *safariproxy as shown below:
 10. Start the Selenium Standalone Server
11. Run the Test using JUnit Test option and observe that the Test Ran this Time in Safari.
12. Though the Selenium Test executed successfully, the Test has resulted as Fail though there are no failures as shown below (i.e. com.thoughtworks.selenium.SeleniumException: ‘undefined’ is not an object is displayed as error )
 Watch the below video:

Click here to watch the video.

Download this Project:

Click here to download this project and import into Eclipse IDE  on your machine.

NOTE : After lots of research activity its found that Running Selenium RC Tests on Safari Browser of MAC OS X machine will work without any errors. I am not going to explain this practically on MAC machine. Please try yourself.

Using *iexplore for running the Selenium RC Automation tests on Internet Explorer Browser

Using *iexplore for running the Selenium RC Automation tests on Internet Explorer Browser

*iexplore is used to run the Selenium RC Automation Tests on Google Chrome Browser.

Lets Implement This:

1. Replace *firefox with *iexplore in the project explained in our previous post as shown below:

2. Start the Selenium Standalone Server
3. Save and Run the ‘Class15.java’ file by selecting the ‘JUnit Test’ option
4. Observe that the following Script Error is displayed as shown below:

5. We got this Script Error as we are not running the Eclipse IDE in Administrator mode. How to resolve this Script error and Run the Selenium RC Automation Test on Internet Explorer will be explained in the  next steps. In order to run the Eclipse IDE in Administrator mode please follow the below steps.
6. Close the Eclipse IDE by clicking on ‘X’ button as shown below:

7. Ensure that the Eclipse IDE is closed and open the Folder where you have downloaded the Eclipse IDE as shown below:

8. Right click on the ‘eclipse.exe’ file and select ‘Run As Administrator’ option as shown below:

9. Click  ‘Yes’ button if the User Account Control dialog asking the User “Do you want to allow the following program …”  is displayed as shown below:

10. Click ‘Ok’ on the ‘Workspace Launcher’ dialog as shown below:

11. Ensure that the Eclipse IDE is now launched (i.e. in Administrator mode this time).
12. Start the Selenium Standalone Server
13. Save and Run the ‘Class15.java’ file by selecting the ‘JUnit Test’ option and observe that the Test is Run in Internet Explorer without any Script errors this time as shown below:


Watch the below video:

Click here to watch the video.

Download this Project:

 Click here to download this project and import into Eclipse IDE  on your machine.

Using *googlechrome for running the Selenium RC Automation Tests on Google Chrome Browser

Using *googlechrome for running the Selenium RC Automation Tests on Google Chrome Browser

*googlechrome is used to run the Selenium RC Automation Tests on Google Chrome Browser.

Lets Implement This:

1. Replace *firefox with *googlechrome in the project explained in our previous post as shown below:

2. Start the Selenium Standalone Server
3. Save and Run the ‘Class15.java’ file by selecting the ‘JUnit Test’ option and ensure that Selenium RC Automation Test is Run in Chrome Browser as shown below:

Watch the below video:

Click here to watch the video.

Download this Project:

Click here to download this project and import into Eclipse IDE  on your machine.

%d bloggers like this: