Calling a Packaged Procedure via .Net

Someone recently posted a question in the Postgres Forums about how to call a package in Advanced Server. He also had a question about how to call a packaged procedure in a specific schema.

Here is my reply to the question:

You can't actually execute packages. You execute the procedures or functions in them. Here is some code to execute a procedure in a package. I am using public as the schema name. If you want to execute a procedure in a user's schema, just replace public with the user's name.

using System;
using System.Data;
using EnterpriseDB.EDBClient;

namespace ConsoleApplication2
{
    class Program
    {
        static void Main(string[] args)
        {
            EDBConnection conn =
               new EDBConnection(
                     "Server=localhost;" +
                     "Port=5444;" +
                     "User Id=enterprisedb;" +
                     "Password=enterprisedb;" +
                     "Database=edb");

            conn.Open();
            EDBCommand cmd = new EDBCommand();
            cmd.Connection = conn;

            int vEmpId = 7876;
            String query = "public.emp_admin.fire_emp(:e_id)";
            cmd.CommandText = query;
            cmd.CommandType = CommandType.StoredProcedure;

            EDBParameter prm = new EDBParameter(
                             "e_id", 
                             EDBTypes.EDBDbType.Integer);

            cmd.Parameters.Add(prm);

            cmd.Parameters[0].Value = vEmpId;    

            cmd.Prepare();

            cmd.ExecuteNonQuery();

            conn.Close();
        }
    }
}

.Net enterprisedb

EDB Tip #4: Database Links in EDB

LewisC's An Expert's Guide To Oracle Technology

This tip relates to the latest version of EnterpriseDB's Advanced Server (AS) product. This is the commercially licensed database that provides Oracle compatibility. I have spoken quite a bit about what that compatibility entails but the gist of it is that the compatibility is code compatibility. The latest version of AS offers quite a few new features that I will be writing about over time. Today I am writing about the Oracle compatible db links.

To understand database links in Oracle, read my entry here on ittoolbox, Database Links: A Definition in Plain English. That entry will give you a good overview of what a link is and what Oracle does with links. This is a hugely useful feature. Pulling data from remote databases can be incredibly important to many applications.

AS provides DDL syntax that is very close to Oracle syntax and DML syntax exactly like Oracle syntax. In addition, you can choose to create links from AS to Oracle or links from AS to other AS instances or even to Postgres instances.

The AS syntax to create a link:

CREATE [ PUBLIC ] DATABASE LINK name
  CONNECT TO username IDENTIFIED BY 'password'
  USING { libpq 'host=hostname port=portnum dbname=database' |
    [ oci ] '//hostname[:portnum]/database' }

Let me break that up just a bit by comparing it to Oracle's syntax:

CREATE [ SHARED ] [ PUBLIC ] DATABASE LINK dblink
  [ CONNECT TO
    { CURRENT_USER
    | user IDENTIFIED BY password
      [ dblink_authentication ]
    }
  | dblink_authentication
  ]
  [ USING 'connect_string' ] ;

Oracle has an additional keyword, SHARED. This keyword allows multiple users to share a database link. AS does not currently support this syntax.

In both instances, you can choose to create a public or a private link. A public link can be accessed by anyone in a database, a private link can only be accessed by the creator.

dblink is the name of the link. A database link in AS may be any valid name.

In both cases, you define who to connect as and the password for that user. Oracle offers an additional option to connect as CURRENT_USER. AS does not offer that keyword at this time.

In Oracle's syntax, the dblink_authentication is tied to the SHARED database link type.

Both databases have the USING keyword but this is where AS differs most from Oracle.

In AS, when you use USING, you chose to connect to an Oracle database (OCI) or a postgres database (LIBPQ), which would include an AS database. This keyword, OCI or LIBPQ, tells AS which library to use to connect to the remote database. The syntax is mostly the same for both types of database: server, port and database name.

Some examples (I will use EDB*Plus to run the examples):

If I wanted to create a private database link from my local AS server to an AS database (in this case, the same database), I would use this syntax:

CREATE DATABASE LINK aLocalAsDb
CONNECT TO enterprisedb IDENTIFIED BY edb
USING libpq 'host=localhost port=5444 dbname=edb'

Connected to EnterpriseDB 8.3.0.12 (localhost:5444/edb) AS enterprisedb

EDB*Plus: Release 8.3 (Build 14)
Copyright (c) 2008, EnterpriseDB Corporation.  All rights reserved.

SQL> drop database link alocalasdb;

Database Link dropped.

SQL> CREATE DATABASE LINK aLocalAsDb
  2    CONNECT TO enterprisedb IDENTIFIED BY edb
  3    USING libpq 'host=localhost port=5444 dbname=edb'
  4  /

Database Link created.

SQL> select 123 from dual@alocalasdb;

   ?COLUMN?
-----------
        123

SQL>

Creating and using a link to Oracle is very similar. I have an Oracle XE database. I will create a public link that connects to the HR user. I would use this syntax:

EDB*Plus: Release 8.3 (Build 14)
Copyright (c) 2008, EnterpriseDB Corporation.  All rights reserved.

SQL> conn enterprisedb/edb
Connected to EnterpriseDB 8.3.0.12 (localhost:5444/edb) AS enterprisedb

SQL> create public database link oraxe
  2  connect to hr identified by hr
  3  using oci '//localhost:1521/xe';

Database Link created.

SQL> select 123 from dual@oraxe;

   ?COLUMN?
-----------
        123

SQL> select count(*) from employees@oraxe;

               COUNT
--------------------
                 107

SQL>

And that's just about it. At this time, you cannot use a database link to call a remote procedure. That is planned for the future.

Also, if you connect to an 11g database, you may need to quote your password if you use a mixed case password in Oracle. 11g allows users to have mixed case passwords.

Because AS is using OCI to connect to the Oracle database, you must have an Oracle client installed and correctly configured on the AS server to create a database link to Oracle. You may use the instant client or the full client. Your ORACLE_HOME must also be correctly set. It can also be set in an AS configuration file.

Take care,

LewisC

Technorati : enterprisedb, postgres, postgres plus

Combined Forums for Postgres and EnterpriseDB Issues

In the past, your basic support for postgres has been mail groups. I've never been a big fan of mail groups. You have to subscribe to the group and usually you get tons of stuff you don't really want. Worst of all, you either have to sort through it or filter it to a folder. I never seem to be able to keep up with the stuff I am interested in due to the clutter.

EnterpriseDB has had forums for a while but recently combined the postgres and advanced server forums into a single location. GridSQL also has it's own forum. You can ask pretty much any question you might have. The forums are monitored by EnterpriseDB employees and there are also some very knowledgeable users out there.

LewisC

forums enterprisedb