Saturday, July 19, 2008

Not working for EDB anymore

Well, I am no longer working for EnterpriseDB. It was fun while it lasted but it's over so I am moving on. I found a new job, locally. It's pure Oracle and I will get to use Real Application Clusters in a production environment. That's something I haven't done in the past so I am looking forward to it. It's also a java, .net and Oracle Forms shop and they are doing some interesting things with telecommunications and SMS.


From now on, I will only need to travel for conferences. No more trips to New Jersey. That's kind of a drag as I was almost at elite status on Continental. I have two more conferences this year and it just might be enough. I may even take a trip on my own just to get the miles. One of the trips is from Tampa to San Francisco, and back, so that will get me very close. The other is to Virginia and back.


I am a database geek and I will be keeping an eye on EDB just as I did in the past. I will probably post here at the EDB blog about as frequently as I have been (not often). Or, I may let this one die and just do any EDB posting on my postgres blog. That actually makes the most sense. I think I will cross post this one there and make this my last dedicated posting on this blog.


I also plan to keep up with Postgres, for personal knowledge, just as I do MySQL. I actually want to install GridSQL and see how it performs for a variety of different applications.


On the upside, I can now call EnterpriseDB Postgres Plus Advanced Server, PP AS, without marketing having fit. ;-) Heck, if I think of it as Advanced Server Software, I can call it the PP ASS. heh But I would never do that.


So, I can guess I can close out this blog now. Later.


LewisC




Technorati : , ,

Wednesday, June 4, 2008

EnterpriseDB Gets a New CEO

LewisC's An Expert's Guide To Oracle Technology


Looks like I have a new boss. Actually, it looks like my boss's boss has a new boss. ;-)


I'd like to personally welcome Ed Boyajian to EnterpriseDB. Looks like he has quite a bit of experience with OSS and monetizing software solutions. He's been an executive at Redhat for a long time. 6 years in his last position but in many other positions before that.


We (EnterpriseDB employees) have known this was coming for a while now. I knew Andy and the board were looking when I was hired. I just now learned the name of the chosen, though. It's always interesting in a company when executive leadership changes hands.


I like Andy (Astor, the ex-CEO) and I'm glad he's staying as the new executive VP of Business Development. I believe that is where his strength (i.e. past experience) lies.


I don't like to touch too much on people or activities where I work. Makes me kind of uncomfortable discussing what is normally private stuff. This is newsworthy though. Anyway, I'll leave this with a quote from the new CEO:



"EnterpriseDB is the worldwide leader in bringing the open source Postgres database to enterprises and developers," said Boyajian. "There is a growing gap between the expensive proprietary database products available today and the entry-level open source alternatives. The Postgres Plus product family fills that gap beautifully. The Postgres open source community is active and talented, and the company has an experienced and enthusiastic team and a compelling roadmap for the future. I am thrilled to have this opportunity to lead EnterpriseDB in its next phase of growth and success."



Exciting times ahead?


LewisC




Del.icio.us : , , ,

Thursday, May 29, 2008

Wavemaker Provides EnterpriseDB Support

EnterpriseDB News at Blogspot


Chris Keene, the CEO for Wavemaker just blogged that combining Wavemaker and EnterpriseDB just got easier. Wavemaker has a new version that has out of the box support for both Postgres and EnterpriseDB.


I wrote a couple of weeks ago about my first day of Wavemaker training. I still haven't found time to start day two but now that Wavemaker supports EnterpriseDB, I'll probably redo day 1 using Advanced Server instead of MySQL. I could have done it before since it already supported Postgres and EnterpriseDB via a manual configuration. But now I'll want to see how easy it is with the automatic support.


If you want a little bit more info on Wavemaker, I also posted about my first look with it.


Wavemaker is an open source AJAX GUI builder for almost any relational database. You can get it, for free, here.


Thanks,


LewisC




Technorati : , , , , ,

Tuesday, May 27, 2008

Learn EDB: Basic Encryption

LewisC's An Expert's Guide To Oracle Technology


An ITToolbox user recently asked a question on the EnterpriseDB discussion group, Oracle equilant UTL_I18N.STRING_TO_RAW IN ENTERPRISEDB.


Basically, Sreenivas asked which functions in EnterpriseDB could be used to implement dbms_crypto, hex_to_raw, string_to_raw, etc. I believe he is using EnterpriseDB Postgres Plus Advanced Server which is the product that gives Oracle Compatibility. The short answer to his question is that right now, there are no compatibility functions for those. The long answer is that you can re-implement that functionality using native PG functionality.


If you look at Sreenivas's message you can see how his existing code works. I posted a simple example in response which I am reposting below. The PG docs suggest that you use PGP based encryption rather than what they call raw encryption. I think it depends on exactly what you're doing, personally. Anyway, raw encryption was closer to what Sreenivas was doing so that was what I based my example on.


I've used DBMS_CRYPTO in Oracle quite a bit but this is my first use of encrypt/decrypt in PG. If you have any suggestions for improving it, I'd like to hear them.


Hi Sreenivas,




I saw your post on the edb forum but and planned to write a blog entry on this topic.


The thing is that there isn't a one to one translation in EDB yet. The easiest thing is to rewrite your procedure and use built-ins that are available.




It is recommended that you use the PGP function in postgres as opposed to the raw encryption functions for better security. However, raw encryption more closely matches what you are trying to do. Below is an example of using raw encryption with AES.


You don't need to convert to hex as you'll be using bytea which is easily converted from and to a string. If you really need international support, check out the pg decode function (which is different from Oracle's decode). http://www.postgresql.org/docs/current/static/funct ions-string.html




Here is a very simple example that you can use to build your procedure:



declare

original_data text := 'I am going to be encrypted';
data bytea;
cipher bytea;
crypto_type text;
encrypted_data bytea;
final_string text;

begin

-- conversion to bytea, could use cast too
data := original_data;

--set cipher key
cipher := 'not a strong key, use pgp instead';

-- select type: aes or blowfish (bf)
crypto_type := 'aes';

-- encrypt the data
select encrypt(data, cipher, crypto_type) into encrypted_data;

dbms_output.put_line('Encrypted: ' || encrypted_data );

-- decrypt the data
select decrypt(encrypted_data, cipher, crypto_type) into final_string;

dbms_output.put_line('Decrypted: ' || final_string );

end;

Hope this helps,


LewisC




Del.icio.us : , , ,

Thursday, May 15, 2008

Wavemaker Training Day 1

LewisC's An Expert's Guide To Oracle Technology


I mentioned the other day that I was playing with a new GUI builder from an EnterpriseDB partner, Wavemaker. Wavemaker, in addition to providing the software, provides some training that is downloadable from the web site.


This training is a two day basics course in powerpoint format. I would guess that this is what they use for instructor led training and they just made this available to the public. Anyway, I decided to see how good the training was which in turn kind of tells me how easy wavemaker is to learn.


Day 1 includes 8 powerpoint presentations:



  • Introduction

  • Wavemaker Architecture

  • Wavemaker Installation

  • Wavemaker Studio Overview

  • Page Designer Basics

  • Wavemaker Data Model

  • Service Components

  • Data Widgets


Seeing as how I was already making some simple pages with the tool, I blew through the intro, the architecture and installation. It's interesting stuff, though.


I snagged a screenshot of the wavemaker runtime from the powerpoint presentation. You can get all the details if you download the training zip file.


wavemakerarch


You can see the explicit separation of UI from logic from data. JSON is the middle tier communication and hibernate accesses your data. You don't have to write any of this logic. Wavemaker Studio generates all of this for you.


Interesting in this architecture is that everything that can provide data is treated as a service. More on that below.


I was able to finish the Wavemaker Studio Overview and Page Designer Basics presentations with just a little effort. I hit some difficult parts and the powerpoint doesn't have a tremendous amount of text but the tool is easy enough to use that I was to struggle through and get it completed. The training builds an application and each section builds on the last section.


When I go to the Datamodel stuff I was completely lost. The training said to use the sample database but it didn't have any description for how to get it or if it was already loaded. It had no information about how to connect or anything.


I did a search on the Wavemaker page and realized I had a duh moment. Directly below the powerpoint zip that I downloaded are several other files, including:



Duh. The supporting MySQL script creates the sample database. The training exercise book has all of the text that I noticed was missing from the powerpoint. Every step in the exercises is detailed and explained. I felt like Patrick on Spongebob. ;-)


BUT, even though I didn't have the text, I was able to use the studio and create some basic screens in the first exercises. That either means I'm smart or the tool is easy to use. I'll leave it for you to decide which it is.


Using my newly found instructions, I loaded up my sample database. I already had MySQL installed so it was just a matter of running the script. That went flawlessly and I finished that lesson easily.


The next lesson was Service Components. Here is something that I didn't think was all that big of a deal at first but it turns out to be a very nice architectural decision. When I created my data model, it automatically turned that into a series of services. I didn't really see the need for the extra step. However, a web service is a service. A java class is a service. A javascript can be a service. Etc.


What that means is that the data interface is completely separate from the logic to use that data. That means data can be decoupled and changed at any time. You can start with a direct connection to a database and in the future change the service to a web service. No need to change the app. Or what if you have a need for extreme performance? You can build some classes for say, look up tables, and use those as service for certain components. You can always change to a web service or database.


That's also nice for disconnected development. You can build your UI without ever seeing your database. As long as you know the model, you can temporarily implement it as a web service or java classes. It's an interesting concept and I plan to find time to play with it.


The last step was adding some data widgets (like lists and grids). That is also as easy as drag and drop. You go into your properties and attach widgets to services and that's how you map your data.


At the end of the first day's worth of training, I ended up with a somewhat complete application (albeit a very simple one). The training really doesn't take a full day. I think I finished this in a couple of hours and that's with a lot of playing around with the tool.


first_proj


I didn't follow all of the instructions as far as making it pretty. This is pretty much just where I dragged and dropped the widgets. It still looks pretty good, though. With some experience, this screen could probably be created in 10 or 15 minutes.


I want to finish day 2 and then I want to convert the sample database from MySQL to EnterpriseDB. I can use the EnterpriseDB Migration Studio to migrate the data and it should be a minimal change for my app. Just choose the new connect string and hibernate dialect and regen the mapping. I'll keep you posted.


LewisC

Monday, May 12, 2008

Bind Variables in Postgres Plus Advanced Server

Bind variables are used to ease code maintenance and to save memory and processing on the server. When you save memory and processing power, you improve the overall performance of the server. The inner details of how this saves memory has been enumerated in other places on the web. This article is designed to help developers users bind variables when running queries against Postgres Plus Advanced Server.


What are bind variables?


The easiest way to think of a bind variable is to consider it just another variable. Instead of it being a variable to be used by your application (and whatever language you happen to be using), think of it as a variable to be used by SQL. Better yet, think of it as a parameter to be used by SQL.


Why do you use parameters in your functions and procedures? Take a look at this very simple SPL procedure:



PROCEDURE start_program IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello World');
END;

Now, that procedure works just fine. When the program starts, it calls start_program and it displays a message that the program is starting. Now we decide to add a message just before the program ends:



PROCEDURE end_program IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Goodbye World');
END;

This works fine technically but violates the rules of modularity in coding. We can parameterize the message so that we can reuse a single procedure and reduce the maintenance workload we have.



PROCEDURE print_message
(p_message IN VARCHAR2)
IS
BEGIN
DBMS_OUTPUT.PUT_LINE(p_message);
END;

Now when the program starts, we just call print_message('Hello World'); and when the program ends, we call print_message('Goodbye World');


If you've been programming any amount of time, the reasons for doing this are more than obvious. You get a large maintenance bonus by maintaining fewer lines of code. You might even get a performance bonus (depending on the language) by using less memory.


So, using a bind variable in a database is like using a parameter to a procedure. Only, instead of you doing the maintaining, the database does less maintenance. If you send a bunch of nearly indeitical queries to the server like:



SELECT last_name, first_name
FROM employees
WHERE employee_id = 101;

and



SELECT last_name, first_name
FROM employees
WHERE employee_id = 102;

There are two queries in memory. If you select 10000 employees (which a batch program might easily do), that's a whole lot queries that are basically duplicates. The database has to keep track of those and that takes time and memory. The database already has plenty to do keeping track of permissions, users, log files, etc. Why give it more to do?


The way to write a query with bind variables changes depending on the language and tool. The most common that you might see are named variables beginning with a colon (:) or a question mark (?). EnterpriseDB's SPL uses named variables with a bind for dynamic SQL, as does it's toolset.


Binds in SPL


In general, you don't need to use bind variables in PL/SQL. PL/SQL binds up your variables for you. The only times you need to do it manually are when you are manually parsing SQL using "EXECUTE IMMEDIATE".


If I am using EXECUTE IMMEDIATE, I would write a query like this:



DECLARE
v_empno emp.empno%TYPE;
v_query_string VARCHAR2(2001);


v_name emp.ename%TYPE;

v_job emp.job%TYPE;
BEGIN


v_query_string :=
'SELECT ename, job
FROM emp
WHERE empno = :e_id';


v_empno := 7876;


EXECUTE IMMEDIATE v_query_string
INTO v_name, v_job
USING v_empno;


DBMS_OUTPUT.PUT_LINE('Name: ' || v_name ||
', Job: ' || v_job );


END;

Example:



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.


QL> set serveroutput on
SQL> DECLARE
2 v_empno emp.empno%TYPE;
3 v_query_string VARCHAR2(2001);
4 v_name emp.ename%TYPE; v_job emp.job%TYPE;
5 BEGIN
6 v_query_string :=
7 'SELECT ename, job
8 FROM emp
9 WHERE empno = :e_id';
10 v_empno := 7876;
11 EXECUTE IMMEDIATE v_query_string
12 INTO v_name, v_job
13 USING v_empno;
14 DBMS_OUTPUT.PUT_LINE('Name: ' || v_name ||
15 ', Job: ' || v_job );
16
17 END;
18
19 /


Name: ADAMS, Job: CLERK


EDB-SPL procedure successfully completed.


SQL>

You could actually rewrite this several different ways but this example shows the gist of it. Notice that the select list does not include any binds. Advanced Server knows to send the selected columns out as bind variables.


Also, notice that the name of the bind variable (:e_id) does not need to match the SPL variable (v_employee_id). If we do think of bind variables as parameters to SQL, that makes sense. SQL only cares about the data coming in and going back out. The calling program (Advanced Server in this case) is responsible for correctly mapping inputs and outputs. The SQL engine just agrees to send the data is the agreed upon order (left to right).


Binds in Java


Java is a bit more verbose but most of this code is connecting to the database. The parts in bold are the important parts.



import java.sql.*;
import oracle.jdbc.pool.*;


class prog2 {


public static void main (String args []) throws SQLException
{


String url = "jdbc:oracle:oci8:@//192.168.1.7:1521/ORCL";
OracleDataSource ods = new OracleDataSource();


ods.setURL(url);
ods.setUser("hr");
ods.setPassword("hr");


Connection conn = ods.getConnection();


PreparedStatement cmd = conn.prepareStatement
("SELECT first_name, last_name FROM employees WHERE employee_id = ?");



cmd.setString(1, "101");


ResultSet rs = cmd.executeQuery();


rs.next();


System.out.println ("Last Name: " +
rs.getString(1) +
", First Name: " +
rs.getString(2));


conn.close();


}
}

Binds in C#


C# is about as verbose as Java but remember that most of this code is C# infrastructure code and creating the connection. The parts in bold is the pertinent code.



using System;
using System.Data.OracleClient;

namespace ConsoleApplication1
{
class Program
{
static void Main(string[] args)
{
OracleConnection conn =
new OracleConnection(
"User id=hr;Password=hr;Data Source=remoorcl"
);
conn.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;

String query = "SELECT last_name, first_name " +
"FROM employees " +
"WHERE employee_id = :e_id";

cmd.CommandText = query;

int vEmpId = 101;
OracleParameter prm = new OracleParameter(":e_id", vEmpId);
cmd.Parameters.Add(prm);


OracleDataReader dataReader = cmd.ExecuteReader();

dataReader.Read();

Console.WriteLine("Last Name: " +
dataReader.GetOracleString(0) +
", First Name: " +
dataReader.GetOracleString(1));

conn.Close();
}
}
}

Summary


This post got much larger than I intended. Anyway, the important part is that it really is not complicated to use bind variables and I think it is much less complicated than trying to string together pieces of text and needing to quote char data and format date data. A bind variable is just a parameter that allows SQL to reuse statements. This helps performance and reduces memory consumption as well as making your code more maintainable.


LewisC








Technorati : , , , , ,

Thursday, May 8, 2008

DBMS_PIPE & DBMS_ALERT In EnterpriseDB


LewisC's An Expert's Guide To Oracle Technology

The latest release of the EnterpriseDB database, Postgres Plus Advanced Server (AS) 8.3, includes additional Oracle compatibility features. Two of those new features are DBMS_PIPE and DBMS_ALERT. Old Oracle hands will recognize those two packages that are used for interprocess communication. This post is a quick tutorial on using these two features. If you've used them in Oracle, you would have no problem using them in AS.

DBMS_ALERT

DBMS_ALERT allows you to send an alert to any processes that are registered to hear (listen) that alert. The listening process can register to hear multiple alerts. When any alert comes that matches the listen list, the receiving process will take action. A process can block, i.e. freeze, while waiting for an alert or it can continue processing and periodically poll for alerts. This is accomplished via a timeout parameter.

The first step in alerting is to register to hear an alert. This is done via the register (DBMS_ALERT.REGISTER) process. Once the process is registered it can check for a specific alert (DBMS_ALERT.WAITONE) or it can check for any registered alert (DBMS_ALERT.WAITANY). When a process is no longer interested in the alerts, it can deregister from a single alert (DBMS_ALERT.REMOVE) or it can deregister from all alerts (DBMS_ALERT.REMOVEALL).

To send an alert, a process will call DBMS_ALERT.SIGNAL. This raises the alert and registered listeners are notified.

Here is a simple example using two EDB*Plus sessions.

SQL> set sqlprompt "Sess1> "
Sess1>

Sess1> exec dbms_alert.register('alertme');

Sess1> set serveroutput on
Sess1> declare
2 v_status integer;
3 v_message varchar2(200);
4 begin
5 -- wait for one second and return
6 dbms_alert.waitone('alertme', v_message, v_status, 1);
7 dbms_output.put_line('Message: ' || v_message || ', Status: ' || v_status );
8 end;
9 /
Message: , Status: 1
    EDB-SPL Procedure successfully completed.
Sess1>

In this case, I had it wait for just a single second. Since I had not sent a message it timed out (status = 1).

Now I'll wait 240 seconds and send a message from a second session.

In session 1:

   Sess1> declare
2 v_status integer;
3 v_message varchar2(200);
4 begin
5 -- wait for 240 seconds and return
6 dbms_alert.waitone('alertme', v_message, v_status, 240);
7 dbms_output.put_line('Message: ' || v_message || ', Status: ' || v_status );
8 end;
9 /

In session 2:

   SQL> set sqlprompt "Sess2> "
Sess2> exec dbms_alert.signal('alertme', 'This is an alert!');

EDB-SPL Procedure successfully completed.

Sess2> commit;

COMMIT completed.

Sess2>
Back in session 1:
   Message: This is an alert!, Status: 0

EDB-SPL Procedure successfully completed.

Sess1>

Note the commit. It is required.

DBMS_PIPE

DBMS_PIPE also allows you to send data to other sessions in the database. You pack a buffer with a message or messages and then send the message. Unlike an alert, the session listening for the message pulls it off and other sessions will not see it. Think of DBMS_ALERT as a multi consumer queue and DBMS_PIPE as a single consumer queue. That's not an entirely technically correct analogy but it's close.

Like an alert, a pipe has a name. With a pipe though, you may have public or private pipes. In Oracle, a public pipe may be read and written to by any session in the database while a private pipe can only be written to and read from a session connected as the same user as the creator. In AS, any session can read any pipe.

A pipe can be created with the DBMS_PIPE.CREATE_PIPE function. If you put a message on a pipe that has not been created using CREATE_PIPE, the database will automatically create an implicit public pipe. An implicit pipe will free up when there are no messages left in the buffer. An explicit pipe will free up only when DBMS_PIPE.REMOVE_PIPE is called.

Another difference from an alert is that a pipe can send data type specific messages. A pipe can send a varchar2, date, number or raw message. You put the message in the buffer via the DBMS_PIPE.PACK_MESSAGE procedure. This procedure also identifies the data type. You pull a message off using DBMS_PIPE.UNPACK_MESSAGE. Once you've packed a message on the buffer, you can call DBMS_PIPE.SEND_MESSAGE to send it. Before unpacking, you must call DBMS_PIPE.RECEIVE_MESSAGE. You can put multiple messages in the buffer before sending it.

To determine the data type of a message, you would call DBMS_PIPE.NEXT_ITEM_TYPE in between calls to DBMS_PIPE.UNPACK_MESSAGE. The return value from the NEXT_ITEM_TYPE call is an integer value: 0 = no more data, 9 = number, 11 = varchar2, 13 = date and 23 = raw.

Here is a very simple example to send a varchar2 and a date message from one session to another. I am not using DBMS_PIPE.CREATE_PIPE so I am creating an implicit public pipe. I am reusing Sess1 and Sess2 sessions from before.

   Sess1> declare
2 v_status integer;
3 v_date date;
4 v_char varchar2(100);
5 begin
6 v_date := sysdate; -- sysdate = May 5, 2008
7 v_char := 'This is a char message.';
8 dbms_pipe.pack_message(v_char);
9 dbms_pipe.pack_message(v_date);
10 v_status := dbms_pipe.send_message('thepipe', 0);
11 dbms_output.put_line('Status: ' || v_status );
12 end;
13 /
Status: 0

EDB-SPL Procedure successfully completed.
Sess1>
   Sess2> declare
2 v_status integer;
3 v_date date;
4 v_char varchar2(100);
5 v_data_type integer;
6 begin
7 v_status := dbms_pipe.receive_message('thepipe', 0);
8 v_data_type := dbms_pipe.next_item_type;
9 dbms_pipe.unpack_message(v_char);
10 dbms_output.put_line('Data Type: ' || v_data_type ||
11 ', Message: ' || v_char);
12 dbms_pipe.unpack_message(v_date);
13 dbms_output.put_line('Date: ' || to_char(v_date) );
14 end;
15 /
Data Type: 11, Message: This is a char message.
Date: 05-MAY-08

EDB-SPL Procedure successfully completed.
Sess2>

These functions are useful when you need them, especially for notifications to applications running outside the database. You can get additional information about these packages and others in the EnterpriseDB Oracle Compatibility Guide.

LewisC

Thursday, April 24, 2008

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();
}
}
}


Wednesday, April 23, 2008

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 : , ,

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



Wednesday, March 26, 2008

EnterpriseDB Open Sources GridSQL

I wrote about GridSQL and EnterpriseDB a while back. EnterpriseDB bought GridSQL (from Mason who now works at EnterpriseDB) and has been working to improve it. Yesterday, they announced that they are open sourcing it.

GridSQL is really for data warehousing but can be applied to some OLTP/reporting combination databases. What it does is take a query and spread it out to run in parallel against a set of cheap PCs. There is a controller machine, called a coordinator, that keeps track of what is running and where.

Because there is a central point of failure (the coordinator), it is not a high availability solution. But, also because of the coordinator, you can hot swap, add or remove nodes while users are accessing the data. Very cool technology.

LewisC






Friday, March 7, 2008

A New EnterpriseDB Webinar

I'm doing a "Best Practices on Database Migration" webinar at ITToolbox.com March 12.

I'll talk about ways to make your life easier when doing database migrations.

LewisC



Monday, February 11, 2008

Open Source Data Integrator Apatar Announces Partnership with EnterpriseDB

Apatar is a provider of open source software tools for the data integration market. Apatar provides data integration to the companies by offering provides support, training, and consulting services for its integration software solutions.

Apatar provides connectivity to EnterpriseDB, Oracle, MS SQL, MySQL, Sybase, DB2, MS Access, PostgreSQL, XML, InstantDB, Paradox, BorlandJDataStore, Csv, MS Excel, Qed, HSQL, Compiere ERP, SalesForce.Com, SugarCRM, Goldmine, any JDBC data sources and more.

The free Community Edition is available for download and provides plenty of functionality out of the box. There is a also an Enterprise Edition that provides some additional features. You can compare features in the feature matrix.

"We are pleased to support EnterpriseDB's world-class database solutions as part of our integration practice," said Renat Khasanshyn, founder and CEO of Apatar, Inc. "In today's enterprises, most data integration projects never get built. The ROI (Return on Investment) on the small projects is simply too low to justify bringing in expensive middleware. Apatar entered this market as an open source vendor bringing the ease of drag-and-drop data integration, including with EnterpriseDB databases, to the companies that previously found proprietary data integration, ETL, and EAI software tools expensive and difficult to implement."



Tuesday, February 5, 2008

EnterpriseDB CEO Named One of the “Top Leaders in Open Source Business”

EnterpriseDB's CEO, Andy Astor, was named one of 13 “Top Leaders in Open Source Business” by LinuxWorld Magazine. EnterpriseDB was also recently named one of the “Top 20 Companies to Watch in 2008" by Linux Magazine.

This is good news for open source and for people looking to save money on database license fees.

EnterpriseDB sells the open source based Oracle compatible EnterpriseDB Advanced Server as well as a packaged Postgres implementation. EnterpriseDB also offers consulting and training.




Tuesday, January 22, 2008

EnterpriseDB: The Definitive Reference for less than $25 USD

If you've thought about buying the book, EnterpriseDB: The Definitive Reference, you should check out A1Books.com. As of right now, 1/22/08, it's going for $24.80 USD (Brand New).

Shipping would depend on your location.

LewisC




Tuesday, January 15, 2008

Wednesday, January 9, 2008

Win a copy of my EnterpriseDB book

I've been thinking long and hard about how I want to give away a few copies of my book. I will be at the SOUG meeting Thursday, Jan 24th here in Tampa. If you're in the area, stop by. I'm going to have a drawing for a couple of copies that night.

That doesn't really help those people who aren't near by. So, I am going to ask 3 questions and the first two people to answer all three correctly will get a copy.

They won't be hard to answer but the answers might require a little work. I will ask the questions and explain how you will need to respond.

To get the questions, you need to follow me on twitter. You can find my twitter account in various places on the net, including in my blog at ITToolbox.com. You will need to be watching at 9am Eastern time, Jan 12, 2008. Follow and be watching for my questions. I will ask the questions and then explain what to do next.

This contest is open to anyone. If you are outside the US and you win, we can discuss the best way to get you your book.

I hope this is a fun process. That's what I'm shooting for anyway.

Let me know what you think.

LewisC



Thursday, January 3, 2008

My Book Has Been Printed

Well, it's taken over a year but it has finally arrived. I started writing the book back in Aug 2006. I finished in late Jan 2007 and the technical editor finished his work in March. Now, In Jan 2008, EnterpriseDB: The Definitive Reference is available. I'm glad I didn't wait for the movie. ;-)

It's kind of ironic. Just this morning, I posted that I am working on my second book. My wife called me at work and told me two boxes of books had arrived. 2008 is turning out to be a good year. I'll have some additional news in the near future.

Anyway, the book looks good. I've already found a few typos and grammar errors just skimming around. I like the font and the pages look really crisp. All in all, I have to say I am very happy that the book is out.

Check out some photos:

EnterpriseDB Book 016

EnterpriseDB Book 020

I think I want to give a couple away. Maybe some kind of contest for the blog readers. Any ideas of something fair? I am willing to pay shipping for a couple but that would only be here in the US. Overseas shipping gets expensive.

LewisC



Software Blogs - Blog Catalog Blog Directory Software blogs Top Blog Sites Blog Flux Directory Lewis Cunningham