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

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