Oracle Heterogeneous Services(hsODBC)

Heterogeneous Services provide the ability to communicate with non-Oracle databases and services.

I will discuss, how to communicate with SQL Server 2005 from Oracle.

1. First of all, you should have a trusted username in SQL Server 2005 to connect to the SQL database.

If you don’t have it, do the following:

1.1 Connect to the SQL Server 2005 database.
1.2 In the Object Explorer, expand the Security folder, then Logins and create new login.

New Login...(Sql Server 2005)

1.3 In General, type Login name, choose SQL Server authentication, type password for user, uncheck User must change password at next login, choose English in Default language.

Login_New(SQL Server 2005)

1.4 In the Server Roles, public and sysadmin roles should be checked.

Login Properties_Server Roles(SQL Server 2005)

1.5 In the User Mapping, check the database for which this user will be owner(Note that this database should be the database from where you want to query data).

Login_Properties_User_Mapping(SQL Server 2005)

1.6 In Status.

Login_Properties_Status

1.7 Right click on the server in the Object Explorer window, choose properties, in Security, choose SQL Server and Windows Authentication mode.

Server Properties_Sequrity(SQL Server 2005)

1.8 Reload the server.

2. Now, when you already have a trusted user in SQL Server 2005. Let’s install ODBC driver for sql server on the computer where Oracle is installed.

2.1 Start -> Control Panel -> Administrative Tools -> Data Sources (ODBC), go to System DSN tab, click Add button.

ODBC Data Source Administrator_System DSN

2.2 Choose SQL Server, click Finish.

Create New Data Source

2.3 In the Name field, type some name, we will use it later as a SID_NAME. In Server field, type the IP of the server, where SQL Server 2005 database is installed.

Create a New Data Source to SQL Server(Data Source)

2.4 Choose second radio button, and type Login ID and Password for the user, which is a trusted user in SQL Server 2005(We have created it before, section 1).

Create a New Data Source to SQL Server

2.5 Check Change the default database to, and choose your database(Note this should be the database from where you want to query data).

Microsoft_SQL_Server_DNS_Configuration

2.6 Leave default settings.

Microsoft_SQL_Server_DNS_Configuration_2

2.7  This window shows you the summary, click Test Data Source button.

ODBC Microsoft SQL Server Setup

2.8 If you have indicated the correct settings, it should be successful.

SQL Server ODBC Data Source Test

3. Now, it’s time to configure in Oracle the following files: listener.ora, tnsnames.ora and %ORACLE_HOME%\hs\admin\inithsodbc.ora.

3.1 In listener.ora file add the following entries:

listenerradiustosql =
    (ADDRESS_LIST=
        (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522))
         (ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY))
)
SID_LIST_listenerradiustosql=
(SID_LIST=
   (SID_DESC=
     (SID_NAME=radiustosql)
     (ORACLE_HOME = C:\oracle\product\10.2.0\db_1)--Your oracle home
     (PROGRAM=hsodbc)
    )
)

Note: HOST must be localhost(127.0.0.1) not the computer IP, on where oracle is installed.
PORT is non-default port 1522, or choose the port which is free.
You should have two different listeners, one for Oracle itself and other one for radiustosql.

3.2 In tnsnames.ora file add the following entries:

radiustosql  =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522))
    (CONNECT_DATA=(SID=radiustosql))
    (HS=OK)
  )

3.3  Rename %ORACLE_HOME%\hs\admin\inithsodbc.ora to %ORACLE_HOME%\hs\admin\initradiustosql.ora and change its content to the following:

# This is a sample agent init file that contains the HS parameters that are
# needed for an ODBC Agent.

#
# HS init parameters
#
HS_FDS_CONNECT_INFO = radiustosql
HS_FDS_TRACE_LEVEL = OFF

#
# Environment variables required for the non-Oracle system
#
#set <envvar>=<value>

3.4 Reload both Listeners: Oracle listener and newly created listenerradiustosql.

–To start default listener

>lsnrctl stop
>lsnrctl start

–To start non-default listener

>lsnrctl stop listenerradiustosql
>lsnrctl start listenerradiustosql

4. Connect to Oracle database and create database link.

4.1
–To create database link

create database link radiustosql
connect to m identified by itsPassword using 'radiustosql'

–To check if it works, run the following:

SELECT *
FROM dual@radiustosql

–Output

DUMMY
--------
X
Advertisements

JavaScript: Why to put semicolons after each expression

By JavaScript standard it is not obligatory to put semicolons after each expression when there is only one expression on a line. Parser automaticaly puts it after line. For example:

var a, b
a = 7
b = 8

will be parse it as:

var a, b;
a = 7;
b = 8;

But not putting semicolons is not considered a good coding style and therefore sounds question: “why to put semicolons if it is not obligatory?”. The answer is that there are some cases that not putting semicolon breaks code execution flow or even worse – there is compilation error. I do not speak when there are two or more expressions, I mean:

return
true

this will be parsed as:

return;
true;

and undefined will be returned instead of true. Compilation error occurs, when jumping or breaking a label:

break
label

These are too simple examples, but there is shown why to put semicolons after each expression.

Retrieving data from Active Directory to SQL Server 2005

We will use Visual Basic script to retrieve data from Active Directory. This script will pass data to the SQL Server procedure, which will insert it to the database table.

Just for Note: I used WinNT in visual basic instead of LDAP, and it was smaller script than this one, which I have written below:). But when I was trying to run this script error code: 8000500D was arising, error was indicating to the department property… Unfortunately, I couldn’t find the solution how to retrieve department property using WinNT, that is why I decided to write code by LDAP(code is written below).

So let’s start.

1. Connect to the database.

–First of all let’s create a table, which will hold this information.

CREATE TABLE [ADUsersTable] (
[UserId]     [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY,
[Username]   [nvarchar] (1000),
[FullName]   [nvarchar] (1000),
[Department] [nvarchar] (1000)
)

Note: If IDENTITY(1,1)option is new for you, click here to clarify.

–Create procedure.

CREATE PROCEDURE ad_addProc
@Username   nvarchar(1000),
@Fullname   nvarchar(1000),
@Department nvarchar(1000)
AS
BEGIN
   INSERT INTO [ADUsersTable] ([Username], [FullName], [Department])
   VALUES(@Username ,@Fullname,@Department)
END

2. On the computer,  where SQL server is installed, create a text file, insert the following Visual Basic script and rename this file to AdUsers.vbs

Option Explicit
Dim oDomain
Dim MyConnection
Dim MyCommand
Dim par1
Dim par2
Dim par3
‘Domain is bg.ge
Set oDomain = GetObject("LDAP://dc=bg,dc=ge")
Set MyConnection = CreateObject("ADODB.Connection")
'Create SQL connection string,  to connect to the database.
MyConnection.Open "Driver={SQL Server};server(local);
database=DBname;uid=BOG0\mkupatadze;pwd=*****;Trusted_Connection=yes;"

Set MyCommand = CreateObject("ADODB.Command")
Set MyCommand.ActiveConnection = MyConnection
Set par1 = MyCommand.CreateParameter("@Username", 202, 1, 1000)
Set par2 = MyCommand.CreateParameter("@Fullname", 202, 1, 1000)
Set par3 = MyCommand.CreateParameter("@Department", 202, 1, 1000)
'Note that ad_addProc is the procedure created in SQL Server database.
MyCommand.CommandText = "ad_addProc"
MyCommand.CommandType = 4
MyCommand.Parameters.Append par1
MyCommand.Parameters.Append par2
MyCommand.Parameters.Append par3
RetrDataFromAD(oDomain)
‘Write the recursive function.
Sub RetrDataFromAD(oArray)
   Dim oADObject
   For Each oADObject in oArray
     ‘If error occurs, script will not be terminated.
     On Error Resume Next
     ‘Clearing  values of parameters
     par1.Value =" "
     par2.Value =" "
     par3.Value =" "
     Select Case oADObject.Class
       Case "user"
          ‘sAMAccountName is the username
          par1.Value = oADObject.Get("sAMAccountName")
          par2.Value = oADObject.Get("name")
          par3.Value = oADObject.Get("department")
          MyCommand.Execute
       Case "organizationalUnit" , "container"
          RetrDataFromAD(oADObject)
     End Select
    Next
End Sub
MyConnection.Close()

Double Click on that file and to see the result run the following query:

Select *
from [ADUsersTable]

Identity Columns

The syntax of the identity property is the following:

IDENTITY[(seed ,increment)]

Note: If you do not indicate seed and increment options, the default value for each of them will be 1. So IDENTITY(1,1) and IDENTITY is the same.

When the column is created by this property , numeric sequence will be created for you and this column will hold the sequential values, started by seed and incremented by increment parameter values.

For example:

–Create table by this property

CREATE TABLE [ADUsersTable] (
[UserId]     [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY,
[Username]   [nvarchar] (1000),
[FullName]   [nvarchar] (1000),
[Department] [nvarchar] (1000)
)

–Insert rows

INSERT INTO [ADUsersTable] ([Username], [FullName], [Department])
VALUES('MKUPATADZE' ,'MARIAM KUPATADZE','IT')

INSERT INTO [ADUsersTable] ([Username], [FullName], [Department])
VALUES('GBERIDZE' ,'GIORGI BERIDZE','IT')

–Query data

SELECT *
FROM [ADUsersTable]

–Result

USERID|USERNAME     |FULLNAME          |DEPARTMENT
 1    | MKUPTADZE   | MARIAM KUPATADZE | IT
 2    | GBERIDZE    | GIORGI BERIDZE   | IT

–Insert another row and rollback it, let’s see what happens

BEGIN TRAN

INSERT INTO [ADUsersTable] ([Username], [FullName], [Department])
VALUES('AGANDILIANI' ,'ANNA GANDILIANI','IT')

ROLLBACK TRAN

–Insert one more row

INSERT INTO [ADUsersTable] ([Username], [FullName], [Department])
VALUES('SCHELISHVILI' ,'SOPHO CHELISHVILI','IT')

–Query data

SELECT *
FROM [ADUsersTable]

–Result

USERID|USERNAME     |FULLNAME          |DEPARTMENT
 1    | MKUPTADZE   | MARIAM KUPATADZE | IT
 2    | GBERIDZE    | GIORGI BERIDZE   | IT
 4    | SCHELISHVILI| SOPHO CHELISHVILI| IT

As you can see rolling back the transaction does not reset the current value of the sequence.

–To see the current value of the sequence, run the following:

DBCC checkident(ADUsersTable)

My result

Checking identity information:current identity value '4',current column value '4'.

Inserting desired identity values

By default, you can’t indicate value during insert for the column which was created by IDENTITY property. If you try the following command:

INSERT ADUsersTable (UserId,Username,FullName,Department)
VALUES(3,'VDALAKISHVILI','VASIL DALAKISHVILI','IT')

–It will arise the following error

Cannot insert explicit value for identity column in table 'ADUsersTable'
when IDENTITY_INSERT is set to OFF.

–To solve this, set IDENTITY_INSERT to ON, insert rows and then set IDENTITY_INSERT to OFF

SET IDENTITY_INSERT ADUsersTable ON

INSERT ADUsersTable (UserId,Username,FullName,Department)
VALUES(3,'VDALAKISHVILI','VASIL DALAKISHVILI','IT')

SET IDENTITY_INSERT ADUsersTable OFF

Note: If you do not turn off IDENTITY_INSERT, then you will not be able to use the generated sequence. Every time you run the INSERT statement you will be forced to indicate value for USERID column.

–Query data

SELECT * FROM ADUsersTable

–Result

USERID|USERNAME      |FULLNAME           |DEPARTMENT
 1    | MKUPTADZE    | MARIAM KUPATADZE  | IT
 2    | GBERIDZE     | GIORGI BERIDZE    | IT
 3    | VDALAKISHVILI| VASIL DALAKISHVILI| IT
 4    | SCHELISHVILI | SOPHO CHELISHVILI | IT

–To see the current value of the sequence , run the following:

DBCC checkident(ADUsersTable)

–The result

Checking identity information:current identity value '4',current column value '4'.

Note: If the desired value is less then the current value of the sequence, the current value will not be affected. As in our case, we inserted value 3, the current value was 4, that’s why current value was not affected.

But if the desired value is more then the current value of the sequence, current value will be affected.

For example:

–Repeat the previous steps, but now let’s insert higher value than the current one

SET IDENTITY_INSERT ADUsersTable ON

INSERT ADUsersTable (UserId,Username,FullName,Department)
VALUES(7,'TMAISURADZE','TEMUR MAISURADZE','IT')

SET IDENTITY_INSERT ADUsersTable OFF

–Query data

SELECT * FROM ADUsersTable

–Result

USERID|USERNAME      |FULLNAME           |DEPARTMENT
 1    | MKUPTADZE    | MARIAM KUPATADZE  | IT
 2    | GBERIDZE     | GIORGI BERIDZE    | IT
 3    | VDALAKISHVILI| VASIL DALAKISHVILI| IT
 4    | SCHELISHVILI | SOPHO CHELISHVILI | IT
 7    | TMAISURADZE  | TEMUR MAISURADZE  | IT

–To see the current value of the sequence , run the following:

DBCC checkident(ADUsersTable)

–The result

Checking identity information:current identity value '7',current column value '7'.

That’s all. 🙂

JavaScript Closure Misunderstanding

Let’s list some well-known facts:

  • JavaScript blocks don’t have scope.
  • Only functions have scope.
  • Variable access speed depends on “distance” of scopes (in which scope is defined a variable and from which scope we want to retrieve it).
  • The furthest scope is global scope and it is considered as slowest scope (That is window object in browser implementation of JavaScript).
  • Closures are used to cache scope locally and make variable access faster (and for much more…).

Classical closure example is:

//without closure
window.singeltonObject = {/* properties of singleton object */};
getSingeltonObject = function(){
   return singeltonObject;
};
//with closure
getSingeltonObject = (function(){
    var singeltonObject = {/* properties of singleton object */};
    return function(){
          return singeltonObject;
    }
})();

Closure version looks difficult but it is very powerful (for example you can not modify your singelton object, it is closed in a local scope, while when you have defined it in global scope it is accessible from everywere). Les’t explain it:

//without closure
//global scope
window.singeltonObject = {};
getSingeltonObject = function(){
    //local scope
   return singeltonObject; //goes out of local scope in
                            //global scope, finds variable and
                            //reads it.
}
//with closure
//global scope
getSingeltonObject = (function(){
    //local scope1
    var singeltonObject = {};
    return function(){
          //local scope2
          //while definition of this function local scope1
          //is cached and singeltonObject will be found there,
          //it will not be searched in global scope
          //that's why it is faster and useful.
          return singeltonObject;
    }
})();

What happens when we have three or multiple deep closure? I mean, when we have such kind of code:

/**
*  @param {Number} personId
*  @return {Object}
*     Has method logPersonId that logs parameter
*/
getSingeltonObject = (function(){
    //scope0
    var singeltonObject;
    return function(personId){
          //scope1
          if(!singeltonObject){
               singeltonObject = {
                    logPersonId : function(){
                        console.log(personId);
                    }
               }
          }
          return singeltonObject;
    }
})();

At first glance everything is OK, but there is a bug:

getSingeltonObject(1).logPersonId(); // 1 will be logged
getSingeltonObject(123).logPersonId(); //still 1 will be
                              //logged instead of 123 :) why?

Because the function logPersonId is defined once, it caches the scope1 and at that time the parameter personId equals to 1. During the second call logPersonId is already defined and it has cached scope1 when personId was equal to 1 and it still “remembers” it. That is why in both calls value 1 was logged.

What is solution? scope0. We have to update personId in scope0 by creating a new variable in it (for example newPersonId) and assigning it personId everytime the getSingeltonObject is called. The code will look like this:

getSingeltonObject = (function(){
    //scope0
    var singeltonObject, newPersonId;
    return function(personId){
         //scope1
         newPersonId = personId;
          if(!singeltonObject){
               singeltonObject = {
                    logPersonId : function(){
                        console.log(newPersonId); //newPersonId
                    //will be found in scope0 and it will
                    //equal to parameter of getSingeltonObject
                    //function every time.
                    }
               }
          }
          return singeltonObject;
    }
})();

This was a little about JavaScript closure and its complexity.

What is orapwd?

Orapwd is the Oracle utility to create password file.  The syntax is the following:

orapwd file=file_name
password=password for SYS
[entries=number_of_users]
[force=Y/N]
[ignorecase=Y/N]
[nosysdba=Y/N]

Where,
file– is the password file name. If you do not indicate the full path, then file will be created in the current directory.
password-is the password for sys user.
entries– is the maximum number of users that can be granted sysdba or sysoper privileges.
force-if the value of this parameter is Y then the existing password file will be overwritten.
ignorecase– password will be case insensitive.

Note: parameters that are enclosed by ‘[‘ and ‘]’ are optional.

For example:

 orapwd file= pwdorcl password=sys entries=20

Explanation:

Password file, called pwdorcl.ora, will be created in the current directory. The password for sys user will be sys and maximum 20 users can be granted sysdba or sysoper privileges.

Note: Default location of the password file on Windows is %ORACLE_HOME%\database\ and name is pwd%ORACLE_SID%.ora ….. On Linux $ORACLE_HOME\dbs and name orapw$ORACLE_SID. If you do not consider this you will get error:  ORA-01017

In addition to password file creation:

The initialization parameter remote_login_passwordfile must be set to the appropriate value:

  • None: means that Oracle will behave like that the password file doesn’t exist. Which will cause that no privileged connections will be allowed over nonsecure connections.
  • Exclusive: means that the password file will be used with the only one database instance. Setting this value gives the ability to grant/revoke sysdba or sysoper privileges to/from users(Note that granting or revoking privs. causes the password file modifications). It also enables you to change password of SYS user with ALTER USER command. It is the default value.
  • Shared: It is used by multiple DBs, which are running on the same server, or with RAC. Setting this value prohibits you from changing the password file. If you try to change the password file generates the error. To make available to modify this file, first change this parameter to exclusive, modify file and change it back to the share value.

To see how many users are added to the password file run the following command:

select *
from v$pwfile_users

–My output is the following

USERNAME |SYSDBA |SYSOPER
SYS      |TRUE   |TRUE

Deleting password file

To remove the password file, first delete it and then set the initialization parameter remote_login_passwordfile to none. After that, the users that can authenticate by the operating system will be able to connect  to the database as sysdba/sysoper.

How to restore database in SQL Server 2005

I will discuss how to restore database in SQL Server 2005, by the very simple way.
So let’s start:

1. Run  SQL Server Management Studio.

2. In Object Explorer, choose Databases , right click and choose Restore Database…

Restore Database(SQL Server 2005)

3. In the new window, select the database name which you want to restore (my database name is MariamDB).

Destination for restore(SQL Server 2005)

4. On the same window, in Source for restore section, choose From device option, and click browse button.
Source for restore(SQL Server 2005)

5. In Specify Backup window, click Add button.
Specify Backup(SQL Server 2005)

6. Choose the destination where your backup(.bak) file exists, and click OK.
Locate Backup File(SQL Server 2005)

7. Picture should look like this:
Source for restore_Select the backup sets to restore(SQL Server 2005)

8. Check the row, which is showing your backup file information.
Source for restore_Select the backup sets to restore(checked)(SQL Server 2005)

9. Go to the options, and check Overwrite the existing database, and click OK.
Restore Database_Options(SQL Server 2005)

That is all! 🙂