Simple JSP JDBC Example
January 29, 2008 | Leave a Comment
I learned to program on Java, but have done little to exercise those skills in the last six years, preferring to leverage PHPs simplicity whenever possible. So... when I had a need to put together a simple JSP script that queried Oracle, I found myself scouring for a truly simple example to start from. Here's the example I put together:
-
<%@ page import="java.sql.*" %>
-
<%
-
try
-
{
-
//this is how you might get a POST or GET variable from the request to use
-
//String user_id = request.getParameter("some_var");
-
-
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@HOSTNAME:PORT:SID","USERNAME","PASSWORD");
-
if (rset.next())
-
{
-
out.println(rset.getString(1));
-
}
-
else
-
{
-
out.println("No records found");
-
}
-
rset.close();
-
stmt.close();
-
}
-
out.println("Exception");
-
}
-
%>
Certainly for your own purposes you would need to write a lot more code than this. Yet, if you seek a simple example just to gain your bearings, this should do it.
Tags: code, driver, java, jdbc, jsp, oracle, oracle thin, programming, query, sql, thin, web
Announcing Sun MySQL!
January 16, 2008 | Leave a Comment
Sun has officially acquired MySQL! As a huge fan and supporter of MySQL, I have personal commitment and obvious concerns about this. In my experience most things Sun does are bloated and slow to develop (Solaris, Java, Open Office). Yet, I remain hopeful because they do seem to respect and work positively with open source initiatives.
The most positive things can come out of this are improved marketing, better name recognition, and funds injected into a project. Maybe they can compete with Oracle now? Hey, maybe Sun will stop using Berkeley DB to back most it's existing products. I'll admit that upon first reading this my heart skipped a few beats, but as the pains subsided I think its all going to be OK.
Tags: database, java, lamp, mysql, openoffice, oracle, solaris, Sun, sun microsystems
ADOdb - PHP Database Abstraction
August 27, 2005 | 1 Comment
Anyone who has used more than one database (MySQL, PostgreSQL, SQL Server, Oracle, etc) with PHP realizes it is cumbersome. In fact the developers of PHP have recently realized this as well. In 5.1 they are planning to include PHP Data Objects (PDO). However, in the meantime I recommend ADOdb.
Unfortunately, PHP's database access functions are not standardised. Every database extension uses a different and incompatibile API. This creates a need for a database class library to hide the differences between the different databases (encapsulate the differences) so we can easily switch databases.
Example connection to mySQL:
-
$db = NewADOConnection('mysql'); // type of connection
-
$db->SetFetchMode(ADODB_FETCH_ASSOC); // sets the query results to come back as assoc arrays
-
$db->PConnect('mysql_db_host', 'some_user', 'some_pass', 'the_db'); // make the connection
Example connection to Oracle:
-
<?php
-
$db = NewADOConnection('oci8'); // type of connection
-
$db->SetFetchMode(ADODB_FETCH_ASSOC); // sets the query results to come back as assoc arrays
-
$db->PConnect('oracle_db_host', 'jpseudo', 'his_password', 'oracle_sid'); // make the connection
-
?>
Comparing the two examples it's clear how similar connecting to either database becomes. Also notice we're setting the fetch mode to associative so we get back friendly associative arrays. The following is an example of how to query and get back results:
-
<?php
-
$query = "SELECT * FROM tablename";
-
$res = $db->Execute($query);
-
while($row = $res->FetchRow())
-
{
-
// $row is an associative array, indexed by fieldname from the table
-
}
-
?>
This will get back a bunch of rows of data, You might want less, like a single row or single field of a single row.
-
<?php
-
$row = $db->GetRow("SELECT * FROM tablename WHERE id_field='single_value'"); // this returns an associative array of the single row
-
$a_field = $db->GetRow("SELECT a_field FROM tablename WHERE id_field='single_value'"); // this returns the value of single field of the single row
-
?>
More Resources:
ADOdb Database Abstraction Library for PHP Main Site
ADOdb Documentation
Alternate Abstraction Library: PEAR DB
Tags: adodb, database, database abstraction, databases, mysql, oracle, pdo, php, programming
Cheat Sheets
August 5, 2005 | 40 Comments
I love cheat sheets or euphemistiically, quick references. I find myself becoming quite familiar with something, then not using it for 6 months. After time away from something like that a cheat sheet is the best way of getting back up to speed quickly. Also, good cheat sheets sometimes show me features or functionality I wasn't previously familiar with. Consider this Cliff Notes for technology.
JavaScript, MySQL, mod_rewrite, CSS, and PHP(Thanks Casey)
VI (PDF)
Perl Regular expressions (PDF)
Java (PDF) (Not so quick...)
Update 2/20/06:
Prototype JS Library - from BorkWeb.
Still looking for a good one on Oracle and always looking for better ones. Also check this site for some more, though I'm not real excited about most of them.
Tags: cheat sheet, cheat sheets, cheatsheet, cheatsheets, cliff notes, css, development, java, javascript, mod_rewrite, mysql, oracle, perl, php, quick guides, quick reference, regular expressions, vi, web, web development