Web Databases

How to create a database driven website by using web databases?

Daffodil DB: Web Database

By Parveen

What is a Web Database?

A web database is a database for the internet. You can get access to your data via the internet from anywhere in the world. Or, you can create a database driven website by using such databases. A web database stores large amount of information in an organized format that is easily accessible from scripting languages (like PHP).

Daffodil DB is a J2EE-certified, SQL-99 and JDBC standards compliant Java RDBMS. It is the first java database which is compatible with PHP. With the release of PHP extension module, Daffodil DB has joined the select list of Java enabled web databases.

Daffodil DB is an ideal for anyone who is looking for a web database. It can be embedded within any application and delivers high performance with minimum system resource usage - thereby striking the right balance between size, features and performance. At less than 3 MB, Daffodil DB is a small/compact database.

Daffodil DB can run on virtually any platform as it supports all platforms for which a Java Virtual Machine (JVM) is available. These include Windows, Linux, Solaris and UNIX. With the help of Daffodil DB, developers can build applications for any platform and these can be compiled and delivered on all other major platforms.

PHP (PHP Hypertext Preprocessor) is an open source server-side scripting language that is particularly suited for web development and can be used to generate dynamic web content.

PHP scripts are embedded within web pages along with HTML, similar to other web scripting languages such as Microsoft's ASP or Sun Microsystems's JSP. Like ASP and JSP, PHP runs on the web server when a page is requested via HTTP, rather than on the Web browser or other client.

PHP lets you insert instructions into your Web pages that your Web server software (be it Apache, Personal Web Server, or whatever) will execute before sending those pages to a browser that requests them. PHP is easy to use for web development because it has been designed from the onset for the web environment. PHP has many built-in functions that make web programming simpler, so that programmers can focus on the logic of programming without wasting precious development time.

PHP as a language has its own way of doing things, and at the same time, has borrowed features from other languages. But for many people, the main reason for learning a scripting language like PHP is because of the interaction with databases it can offer. Moreover, PHP is cross platform i.e. it allows you to use/develop your solution on multiple OSs. PHP is one of the fastest growing server side scripting languages around and you need to add it to your current arsenal of Perl, ASP, JSP, JavaScript, VBScript, Java...

Daffodil has come up with a PHP extension module that makes Daffodil DB/One$DB compatible with PHP. It is a database extension module driver that is required to work with Daffodil DB in PHP. This extension module uses the capabilities of a Java database and extends it to your web infrastructure.

By using this extensive module, PHP developers can create sophisticated data-driven web applications, having Daffodil DB as a web database, in a fraction of the time and with reduced cost.

PHP extension for Daffodil DB provides a simple, yet effective means for connecting to Daffodil DB from within PHP. The JVM is created using JNI invocation interface and everything runs in-process.

This PHP Extension module will boost open Source activities around Daffodil DB. Moreover, open Source stack -Linux, Apache, One$DB, and PHP; all being open source products, will give an ultimate combination to web development community.

PHP is platform independent, Daffodil DB is platform independent. PHP is free; so as Daffodil DB (open source version).PHP is open source; Daffodil DB has an open source version. So What about using the powers of two most promising technologies, PHP and Daffodil DB, together?

The capabilities of Daffodil DB & PHP can make up what must be the best blend for data-driven Web sites on the globe. Users can use PHP and the Daffodil DB database to access/store information on the web and include it into their website. By using PHP as a front-end and Daffodil DB as a back-end web database, users can benefit from huge savings on the licensing costs of commercial alternatives.

Here are some practical examples where PHP and Daffodil DB, used together, can do wonders

Apart from all above mentioned applications, there are some more areas where users can use Daffodil DB-PHP 'dynamic-duo'.

To know more about PHP visit http://www.php.net

For more information about Daffodil DB log on to http://www.daffodildb.com

Please log on to http://cvs.php.net/co.php/pecl/daffodildb for more information on PHP and Daffodil DB.

Read more: http://www.daffodildb.com/web-database.html

About the Author
This article has been contributed by (Mr.) Parveen Aggarwal, Technical Consultant to DSL India (http://www.daffodildb.com). With more than 6 years of industry experience in Java and allied technologies, he has an in-depth understanding of J2EE, J2ME and database management systems. Parveen is currently working on the concept of data-archiving in embedded databases. He can be contacted at [email protected]

Microsoft CRM integration: Oracle database access from MS CRM

By Boris Makushkin

Today's article topic is customization possibility demonstration for user web interface of Microsoft CRM. As an example we'll use MS CRM integration with ASP.Net application, accessing customer data access, when customers are stored in Oracle 10g database. Let's begin:

1. First, let's create the table to store customer information in Oracle database. We'll use web application iSQL for table metadata manipulation:

2. Table is now created and contains four fields: CUSTOMER_ID, FIRST_NAME, LAST_NAME и ADDRESS. Fill it with text data:

3. Now we'll work with data access to Oracle database from ASP.Net application. We should download from Oracle site http://www.oracle.com Windows Instant Client. We don't have to install it just unpack all the files in the directory of your choice, for example c:\\\\\\\\oracle and set environmental variable TNS_ADMIN, pointing to this directorty.

4. In c:\\\\\\\\oracle directory (or where TNS_ADMIN point out) create file tnsnames.ora as following (change host and service names):
ORCL1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oraclehost.youtdomain.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL1)
)
)

5. Make correction to windows registry to have MS SQL Linked Server work properly withOracle OLE DB Provider. In the hive KEY_LOCAL_MACHINE\\\\\\\\SOFTWARE\\\\\\\\Microsoft\\\\\\\\MSDTC\\\\\\\\MTxOCI make these changes:

OracleXaLib = "oracleclient8.dll"
OracleSqlLib = "orasql8.dll"
OracleOciLib = "oci.dll"

6. Now let's create Linked Server in MS SQL Server 2000:

Note: in the Security tab we need to use security context with the credentials, having valid access to Oracle Database.

7. Linked Server is ready let's test it functioning open table list. We should see customer table there:

8. Now we'll create stored procedure for Oracle data access:
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
GO

CREATE PROCEDURE MyCustomersList AS

SELECT * FROM OPENQUERY(ORACLE, 'SELECT * FROM Customer')

RETURN

9. Next step is customizing Microsoft CRM using interface. We'll add customer list button into Quote screen toolbar. Edit isv.config:
quote>
ToolBar ValidForCreate="0" ValidForUpdate="1">
Button Title="Customers" ToolTip="Show customers list from Oracle DB" Icon="/_imgs/ico/16_convert.gif" Url="http://APOLLO/MyCustomersList.aspx" PassParams="1" WinParams="" WinMode="1"/>
/ToolBar>
NavBar ValidForCreate="0" ValidForUpdate="1">
/NavBar>
/quote>
Change Url to your host name.

10. To create ASPX page we'll use RAD for ASP.Net - WebMatrix:

11. Create new page for data access:

12. Change it's code to access our data:
Sub Page_Load(Sender As Object, E As EventArgs)

Dim ConnectionString As String = "server=(local);database=Albaspectrum;trusted_connection=true"
Dim CommandText As String = "EXEC MyCustomersList"

Dim myConnection As New SqlConnection(ConnectionString)
Dim myCommand As New SqlCommand(CommandText, myConnection)

myConnection.Open()

DataGrid1.DataSource = myCommand.ExecuteReader(CommandBehavior.CloseConnection)
DataGrid1.DataBind()
End Sub

13. Now we'll test our web application by calling it from MS CRM:

Happy programming, implementation, customization and modification! If you want us to do the job call use 1-630-961-5918, 1-866-528-0577, Europe: +49 231 4387600! [email protected]

About the Author
Boris Makushkin is Lead Software Developer in Alba Spectrum Technologies USA nationwide Oracle, Navision, Microsoft CRM, Microsoft Great Plains customization company, serving Chicago, California, Arizona, Colorado, Texas, Georgia, Florida, New York, Canada, Australia, UK, Russia, Europe and internationally ( http://www.albaspectrum.com ), he is Oracle, Unix, Microsoft CRM SDK, Navision, C#, VB.Net, SQL developer.

Microsoft CRM Integration with Navision overview for programmer

By Andrew Karasev

Microsoft Business Solutions CRM is very good budget alternative to Siebel and actually be-loved by Microsoft-oriented IT department system. Considering the fact that in many cases Microsoft CRM should somehow fit into and cooperate with existing legacy business applications, integration question maybe the first to place. It is probably good to mention that Microsoft Navision has its own CRM module (and Navision will probably have MS CRM Navision integration tool for MS CRM 2.0 at this moment we do not know when). We would like to give you, software developer highlight on how you allow Navision data lookup from Microsoft CRM screens: Account (which is customer), contact, lead.

We suppose that Navision is installed with native C/SIDE database, because in the case of MS SQL Server this topic doesn't make sense

Microsoft CRM web interface is built on .Net architecture and Microsoft CRM SDK is married with .Net. We suggest you to use Microsoft tools to connect to Navision database via ODBC.

· Native C/SIDE Database. If Navision sits in C/SIDE database, then create ODBC linked server to Navision via C/ODBC

· Configure web.config to enable Microsoft CRM customization

· Configure isv.config to place the button or navigation bar on one of your MS CRM customizable forms: Lead, Account, Contact, etc.

· Use MS CRM SDK sample code to come into Microsoft CRM Security realm

· Create separate Database on the same SQL server where Microsoft CRM is hosted and place heterogeneous stored procedures or views which will be pulling data from Navision C/SIDE linked server

· Program ADO.Net calls and returned dataset processing in Visual Sudio.Net (preferably C#.Net, because Microsoft CRM SDK is C# oriented)

· We probably should mention that isv.config buttons allow you to transfer GUID of the Contact, Lead or Account to your integrated web application

· Crystal Reports caution MS CRM has built-in Crystal Reports Enterprise, licensed for MS CRM data access only. So, if you like to integrate web Crystal report into your application you should resolve licensing issue and check with Microsoft on purchasing full version of Crystal Enterprise. The other way is to separate your Crystal web application and MS CRM by placing them on separate hardware

· Additional tip in your stored procedure use this directive: DBCC TRACEON(8765), which allows working with the data results of variable length returned by C/ODBC driver

Good luck with integration! If you want us to do the job - give us a call 1-630-961-5918 or 1-866-528-0577! [email protected]

About the Author
Andrew Karasev is Chief Technology Officer in Alba Spectrum Technologies USA nationwide Great Plains, Microsoft CRM customization company, serving clients in Chicago, Houston, Atlanta, Phoenix, New York, Los Angeles, San Francisco, San Diego, Miami, Denver, UK, Australia, Canada, Europe and having locations in multiple states and internationally ( http://www.albaspectrum.com ), he is CMA, Great Plains Certified Master, Dexterity, SQL, C#.Net, Crystal Reports and Microsoft CRM SDK developer. You can contact Andrew: [email protected]

Oracle 10G Development & Design: Heterogeneous Database Access

By Boris Makushkin

Oracle 10G Development & Design: Heterogeneous Database Access
By Boris Makushkin
Oracle 10G Development: Access from Oracle to Heterogeneous Data on Microsoft CRM database example

The designer of nowadays information system often faces difficult task of heterogeneous data access unification. Heterogeneous means that data maybe stored on different database platforms. Oracle Corporation provides developer with tools to address heterogeneous data access: Oracle Transparent Gateways and Generic Connectivity. Generic Connectivity gives you common solution to access database via ODBC and OLE DB mechanisms to FoxPro, Microsoft Access, etc. More interesting is second product - Oracle Transparent Gateways. Its components are created individually for each platform, resulting in more efficient and fast access and better performance. Currently line of products is the following:

Oracle Transparent Gateway for Informix available on Solaris, HP/UX
Oracle Transparent Gateway for MS SQL Server available on NT.
Oracle Transparent Gateway for Sybase available on Solaris, HP/UX, NT, AIX, Tru64
Oracle Transparent Gateway for Ingres available on Solaris, HP/UX
Oracle Transparent Gateway for Teradata available on Solaris, NT, HP/UX
Oracle Transparent Gateway for RDB available on Alpha OpenVMS
Oracle Transparent Gateway for RMS available on Alpha OpenVMS

The disadvantage is the fact that these products are not available for all platforms.

The goal of today's article is heterogeneous database access example from Oracle stored procedures to MS SQL Server 2000. The sample dataset will be pulled from MS CRM database. We'll use rich functionality of Oracle stored procedures with Java utilization to access Microsoft CRM quotes via cursors mechanism. Our sample will have two parts - first we'll model complete functionality of stored procedure in separate Java application and then we'll transfer the code into Oracle RDBMS.
Let's begin:

1. We'll create file CRMConnector.java, containing class definition to work with MS CRM data and returning dataset in the form of Java ResultSet it will become the skeleton of the stored procedure method:

package com.albaspectrum.util;
import java.sql.SQLException;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.PreparedStatement;
import oracle.jdbc.driver.OracleDriver;
import oracle.jdbc.driver.OracleConnection;

public class CRMConnector {
public static ResultSet getQuotes() throws Exception {
// Obtain connection to the databases
Class.forName(com.microsoft.jdbc.sqlserver.SQLServerDriver);
Class.forName(oracle.jdbc.driver.OracleDriver);
Connection mssqlConn = DriverManager.getConnection(jdbc:microsoft:sqlserver: //CRMDBSERVER:1433;DatabaseName=Adventure_Works_Cycle_MSCRM;SelectMethod=cursor, sa, password);
Connection oracleConn = DriverManager.getConnection(jdbc:oracle:thin:@ORACLEDBHOST:1521:ORINSTANCE, test, test);
//Connection oracleConn = new OracleDriver().defaultConnection();
// Turn off autocommit for Oracle connection
oracleConn.setAutoCommit(false);

// Create Oracle temp table
Statement oracleChkTempTableStmp = oracleConn.createStatement();
ResultSet rsCheckTmpTable = oracleChkTempTableStmp.executeQuery(SELECT COUNT(table_name) AS TempTableCounter FROM ALL_TABLES WHERE UPPER(table_name) = UPPER(TempQuotes'));
if (rsCheckTmpTable.next()) {
if (rsCheckTmpTable.getInt(TempTableCounter) == 0) {
Statement oracleStmt = oracleConn.createStatement();
oracleStmt.executeUpdate(CREATE GLOBAL TEMPORARY TABLE TempQuotes (QuoteNumber VARCHAR(100), QuoteName VARCHAR(300), TotalAmount NUMERIC, AccountName VARCHAR(160)) ON COMMIT PRESERVE ROWS);
oracleConn.commit();
oracleStmt.close();
}
}

rsCheckTmpTable.close();
oracleChkTempTableStmp.close();

// Fetch MS SQL Data to Oracle temp table
Statement mssqlStmt = mssqlConn.createStatement();
ResultSet rs = mssqlStmt.executeQuery(select QuoteBase.Name as QuoteName, QuoteBase.QuoteNumber as QuoteNumber, QuoteBase.TotalAmount as TotalAmount, AccountBase.Name as AccountName from QuoteBase, AccountBase where QuoteBase.AccountId = AccountBase.AccountId);
while (rs.next()) {
String quoteName = rs.getString(QuoteName);
String accountName= rs.getString(AccountName);
String quoteNumber = rs.getString(QuoteNumber);
double totalAmount = rs.getDouble(TotalAmount);
PreparedStatement insertOracleStmt = oracleConn.prepareStatement(INSERT INTO TempQuotes (QuoteNumber, QuoteName, TotalAmount, AccountName) VALUES (?, ?, ?, ?));
insertOracleStmt.setString(1, quoteName);
insertOracleStmt.setString(2, quoteNumber);
insertOracleStmt.setDouble(3, totalAmount);
insertOracleStmt.setString(4, accountName);

insertOracleStmt.executeUpdate();
insertOracleStmt.close();
}

oracleConn.commit();
rs.close();
mssqlStmt.close();
mssqlConn.close();

// Create any subsequent statements as a REF CURSOR
((OracleConnection)oracleConn).setCreateStatementAsRefCursor(true);
// Create the statement
Statement selectOracleStmt = oracleConn.createStatement();
// Query all columns from the EMP table
ResultSet rset = selectOracleStmt.executeQuery(select * from TempQuotes);
oracleConn.commit();
// Return the ResultSet (as a REF CURSOR)
return rset;
}
}

2. Some comments to this long-code method. In the first part of the method we are opening connection to MS CRM and Oracle database, where we'll store pulled dataset in temporary table - created for cursor building in the next paragraphs. Next we check the existence of the temporary table definition for quotes storing in Oracle database. If table doesn't exist - we execute its creation. DDL for the table is taken from QuoteBase definition in MS CRM database. The following is simple - we make selection from Microsoft CRM table and transfer the resulting data into Oracle temporary table. Important point is setCreateStatementAsRefCursor method call for resulting statement. ResultSet gives us the possibility to pull the data to form Oracle REF Cursor.

3. Just to remind you about temp table creation in Oracle. Temporary table, called also as global temporary tables are created in temporary table space of the user. Created once these tables exist until the moment its explicit deletion. But data in these tables live depending on the parameters givin at the table creation in the scope and time of user session (ON COMMIT PRESERVE ROWS) or in the scope and time of the transaction (ON COMMIT DELETE ROWS). Syntax to create temporary table: CREATE GLOBAL TEMPORARY TABLE tablename (columns) [ON COMMIT PRESERVE|DELETE ROWS]. we need unique ability, provided by temporary table temporary segments clearing upon the termination of the user session, considering the fact that table structure is similar for every user, but data is uniqu for each session. To get more detail information of the temporary table logic we suggest you to look at Oracle Database Concepts manual

4. Lets create small application to test CRM connector functionality:

package com.albaspectrum.util;

import java.sql.ResultSet;

public class TestORA {
public static void main(String args[]) throws Exception {
try {
ResultSet rs = CRMConnector.getQuotes();
while (rs.next()) {
String quoteName = rs.getString(QuoteName);
String quoteNumber = rs.getString(QuoteNumber);
double totalAmount = rs.getDouble(TotalAmount);
String accountName = rs.getString(AccountName);
System.out.println(quoteName + | + quoteNumber + | + accountName + |$ + totalAmount);
}
}
catch (Exception e) {
System.out.println(Exception: + e.toString());
e.printStackTrace();
}
}
}

5. Build the project in command prompt ( you may need to change paths for the components installed on your computer:
@echo off
set PATH=%PATH%;C:\\j2sdk1.4.2_06\\bin\\
set CLASSPATH=.;%CLASSPATH%;ojdbc14.jar

javac *.java
copy .class .\\com\\albaspectrum\\util\\.class

6. To make MS SQL JDBC driver functional, we place in the current catalogue these file msbase.jar, mssqlserver.jar, msutil.jar. For Oracle JDBC ocrs12.zip and ojdbc14.jar

7. To launch execution(do not forget the paths as mentioned above):
@echo off
set PATH=%PATH%;C:\\j2sdk1.4.2_06\\bin\\
set CLASSPATH=.;%CLASSPATH%;ojdbc14.jar;msbase.jar;mssqlserver.jar;msutil.jar
java com.albaspectrum.util.TestORA

8. We should see something like this:
C:\\...Documents\\AlbaSpectrum\\Articles\\Oracle-MSSQL-SP>run.cmd
QUO-01001-UN9VKX|Quote 1|Account1|$0.0

C:\\...Documents\\AlbaSpectrum\\Articles\\Oracle-MSSQL-SP>

9. Our connector works. It is time to create stored procedure on its base, but first we import our JAR and JAVA files into Oracle JVM:

loadjava -thin -user system/manager@oraclehost:1521:ORCLSID -resolve -verbose /tmp/OraCRM/*

10. Lets test classes loading in Oracle Enterprise Manager

11. Create stored procedure:

create or replace package refcurpkg is
type refcur_t is ref cursor;
end refcurpkg;
/

create or replace function getquotes return refcurpkg.refcur_t is
language java name com.albaspectrum.util.CRMConnector.getQuotes() return java.sql.ResultSet';
/

12. Test its work:
SQL>variable x refcursor
SQL>execute :x := getquotes;
SQL>print x

13. And our goal is achieved!

14. As a final task we can increase the connector performance using Batching Updates in Oracle. JDBC in this case builds the queue of the updates and executes actual update when we call the method ((OraclePreparedStatement)preparedStatemnt).sendBatch()

Happy developing and designing! If you would like us to do the job, give as a call 1-630.961.5918 or 1-866.528.0577 [email protected]

About the Author
Boris Makushkin is Lead Software Developer in Alba Spectrum Technologies USA nationwide Microsoft CRM, Microsoft Great Plains customization company, serving Chicago, California, Arizona, Colorado, New York, Texas, Georgia, Florida, Canada, Australia, UK, Russia, Europe and internationally ( http://www.albaspectrum.com ), he is Microsoft CRM SDK, Navision, C#, VB.Net, SQL, Oracle, Unix developer.