Showing posts with label database. Show all posts
Showing posts with label database. Show all posts

Sunday, August 30, 2015

[IT] Connecting PHP with Microsoft Access (.mdb or .accdb)




WHAT I USED:
1. XAMPP 2.5 (PHP 5.3.8)
2. Microsoft Access 2007


STEPS:
1. Prepare the database on Microsoft Access. Here, I used 2002-2003 format (.mdb).

(picture 1: a first  Microsoft Access 2007 interface)


(picture 2: preparing a new database named phptoacc.mdb)

(picture 3: preparing new table named testdb with its fields, testid and testname)


(picture 4: inserting two new records)

2. Save the database where the PHP can easily reach it. Here, I saved it on "access" folder inside "htdocs" folder of "xampp" folder.

(picture 5: where I stored my database)

3. Type the PHP script for trying to connect to the database.


<?php

$db = $_SERVER["DOCUMENT_ROOT"] ."/access/phptoacc.mdb";
if (!file_exists($db))
{
       die("No database file.");
}

$dbNew = new PDO("odbc:DRIVER={Microsoft Access Driver (*.mdb, *.accdb)}; DBQ=$db; Uid=; Pwd=;");
$sql = "select * from testdb";
$rs = $dbNew->query($sql);

while($result = $rs->fetch())
{
     echo $result[0].": ".$result[1]."<br />";
}


?>



I will explain each part of the script:

a. SELECTING DATABASE

$db = $_SERVER["DOCUMENT_ROOT"] ."/access/phptoacc.mdb";
if (!file_exists($db))
{
        die("No database file.");
}

The above script is used to validate whether the phptoacc,mdb exists on "access" folder.


b. CONNECTING TO THE DATABASE

$dbNew = new PDO("odbc:DRIVER={Microsoft Access Driver (*.mdb, *.accdb)}; DBQ=$db; Uid=; Pwd=;");

The above script uses the PDO driver to connect to Microsoft Access.



NOTE:
If the error message show "PDOException Could not find driver", you must check whether the PDO ODBC driver is installed or not. You can check it by executing:
<?php  phpinfo() ?>
 The output should show information like:

PDO

PDO supportenabled
PDO driversmssql, mysql, odbc, sqlite, sqlite2

pdo_mysql

PDO Driver for MySQLenabled
Client API versionmysqlnd 5.0.8-dev - 20102224 - $Revision: 310735 $

PDO_ODBC

PDO Driver for ODBC (Win32)enabled
ODBC Connection PoolingEnabled, strict matching


If an entry for PDO ODBC is not present, you will need to ensure your installation includes the PDO extension and ODBC drivers. To do so on Windows, uncomment the line extension=php_pdo_odbc.dll in php.ini, restart Apache (or XAMPP), and then try to connect to the database again. (source: http://www.sitepoint.com/using-an-access-database-with-php/)

c. THE SQL TO SHOW THE RECORDS

$sql = "select * from testdb";
$rs = $dbNew->query($sql);

while($result = $rs->fetch())
{
     echo $result[0].": ".$result[1]."<br />";
}

The above script is used to show the records stored on testdb table.



5. Execute the script.

(picture 6: the result on Google Chrome)

Sunday, August 17, 2014

Monday, March 18, 2013

[IT] Connecting JSP with SQL Server 2008 R2 Express (JDBC)

Here are the problems that I got when I tried to connect JSP (JavaServer Pages) with SQL Server 2008 R2 Express through JDBC (Java Database Connectivity):
  1. a. java.lang.ClassNotFoundException: com.microsoft.jdbc.sqlserver.SQLServerDriver
    b. 
    java.lang.ClassNotFoundException: com.microsoft.sqlserver.jdbc.SQLServerDriver
  2. Java Runtime Environment (JRE) version 1.7 is not supported by this driver. Use the sqljdbc4.jar class library, which provides support for JDBC 4.0.
  3. Login failed for user ...

The software and file that I used:
  1. Tomcat 7.0 (Apache Software Foundation)
  2. Java Runtime Environment (JRE) 1.7
  3. JDBC Driver 4.0 for SQL Server (sqljdbc.jar and sqljdbc4.jar)

I will explain the problems in the 'REFER TO PROBLEMS' section in the steps I'll explain.
Pre-requirement:
  1. Tomcat
  2. Java Runtime Environment (JRE)
  3. Microsoft SQL Server
  4. Any web-editing softwares


THE STEPS
  1. Download the Microsoft JDBC Driver for SQL Server to connect any Java programming-based applications with SQL Server. We can download it officially here (if the link is dead, search "Download Microsoft JDBC Driver for SQL Server").
  2. Extract sqljdbc_4.0.2206.100_enu.exe or sqljdbc_4.0.2206.100_enu.tar.gz. We'll get two core files, sqljdbc.jar and sqljdbc4.jar, that we need in folder sqljdbc_4.0\enu.
  3. Make sure that Tomcat's service status is 'stopped'.
  4. Copy both sqljdbc.jar and sqljdbc4.jar to Tomcat's lib folder (commonly: C:\Program Files\Apache Software Foundation\Tomcat 7.0\lib) 
  5. Copy either sqljdbc.jar or sqljdbc4.jar to JRE's ext folder (commonly: C:\Program Files\Java\jre7\lib\ext). If we use JRE 1.5 below, copy just sqljdbc.jar. If we use JRE 1.6 above, copy just sqljdbc4.jar.
  6. Start the Tomcat's service. We can officially connect to SQL Server 2008.

REFER TO PROBLEMS
  • java.lang.ClassNotFoundException: com.microsoft.jdbc.sqlserver.SQLServerDriver
  • java.lang.ClassNotFoundException: com.microsoft.sqlserver.jdbc.SQLServerDriver

    These problems could happen because:
    First, the Tomcat fails to 'recognize' the needed classes that are 'included' in 
    sqljdbc.jar or sqljdbc4.jar. These problems can be solved if we accomplish step 3 and 4 above.

    Second, the wrong usage of Class.forName(). Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver"); is for SQL Server 2000 and Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); is for SQL Server 2005 and above. Don't interchange them, pay attention to their differences. We must pay attention to the differences of the connection string in DriverManager.getConnection().

    Before we start to focus on the last problem, please type this following syntax and save it as JSP (.jsp) and display the result to browser.

    <%@ page contentType="text/html; charset=utf-8" language="java" import="java.sql.*" errorPage="" %>
    <%
    Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
    Connection con = DriverManager.getConnection("jdbc:sqlserver://localhost:1433;");
    Statement st = con.createStatement();
    %>
  • Java Runtime Environment (JRE) version 1.7 is not supported by this driver. Use the sqljdbc4.jar class library, which provides support for JDBC 4.0.

    This error can be solved through step 5.
  • Login failed for user ...

    If you encounter the same problem, do these following steps:
1. Open SQL Server Configuration Manager.
2. Make these following changes:



3. Open Server SQL Management Studio and connect to database engine.
4. Make new login.

5. Type the login name.

6. Select SQL Server authentication (you can type password--and remember it).
7. Select Server Roles.

8. Click OK.
9. Set the server properties:


10. Change server authentication:

11. Click OK.


Copy and paste these codes:
<%@ page contentType="text/html; charset=utf-8" language="java" import="java.sql.*" errorPage="" %>
<%
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection con = DriverManager.getConnection("jdbc:sqlserver://localhost:1433;user=evo");
Statement st = con.createStatement();
%> 

 Note: user=evo, evo is the login name you made.

If the result on the browser is blank page without error, you made it!
Powered by Blogger.