Port 25: The Open Source Community at Microsoft : Technical Analysis, Dev Center

Connecting Office Applications to MySQL and PostgreSQL via ODBC

MichaelF — Tue, 10 Apr 2007 18:05:00 GMT

In cases where business applications have been built on open source databases, it may be necessary to connect other Windows applications, such as Microsoft Access or Excel, to these databases for reporting or business intelligence purposes.

One potential application of this process is to use Excel as a front-end for data analysis. Data can be pulled from views or tables and then further analyzed, graphed, and the like. Even pivot tables can be used to create even more powerful reporting solutions.

This how-to walks through this process using Excel as an example application. Although in this example, the MySQL and PostgreSQL servers are running on Linux, the steps are no different if the software is running on Windows. These steps are:

1.         Setting up authentication
2.         Installing the ODBC Drivers
3.         Configuring the data source
4.         Importing the data.

MySQL on Windows: Configuration & Install

jcannon — Fri, 16 Mar 2007 19:02:00 GMT

This week, the lab looks at configuring and installing MySQL - an open source relational database management system which is typically used for web applications.

This paper will provide an overview of configuring & installing this software on Windows. MySQL does not have as many features as PostgreSQL, however, and one would expect it to perform substantially better than PostgreSQL on Windows because of its thread-based architecture (PostgreSQL uses a process-based architecture instead).

Note: This paper represents testing and documentation in a lab environment. User Account Control (UAC) is an essential security component to Windows and Microsoft does not recommend turning off UAC in production environments.

PostgreSQL on Windows: A Primer

MichaelF — Thu, 22 Feb 2007 21:06:00 GMT

This paper will cover the installation and initial configuration of PostgreSQL 8.2 on Windows up to a point where a database is created and plpgsql is installed in it. We assume an ability to walk through the install wizard in general, though screens that do require additional information will be covered (with screenshots). Important options in the postgresql.conf and pg_hba.conf will be covered, as will database creation in PgAdmin III.

Memtest

jcannon — Tue, 11 Jul 2006 18:32:00 GMT

*Updated* So, this weeks tech tip is about memtest, and yes, I am sure there are some that might scoff at this....But I think we have a tendency to loose sight of the basics. For instance, last week we had quite an interesting time debugging a problem that occurred intermittently and we where not able to find a way to consistently reproduce the problem. We ran through all kinds of things until we decided for grins and giggles to run memtest. And low and behold, it found memory errors. We replaced the memory and have not seen a recurrence of the problem.

We decided to pull this old but trusty tool back out of the stable. Special thanks goes to Kyle Adams and Stephen Zarkos who did a lot of the footwork on this one.

Memtest is a simple program that is designed for the x86 architecture. You would use it for things such as when hardware hangs or when your computer doesn’t boot at all. Either way, you could just grab memtest and throw it onto your computer.

Actually, there is not a hard and fast rule when to use it. There are two ways I would put it in the toolbox to use, and they have to do with methodology more than anything.

I have no clue what is wrong and I am completely out of ideas, I am just stabbing in the dark.
As a standard suite of checks and tests I do to debug a problem I will run memtest.

Honestly, I think number one is one that happens more in real life. A lot of people do not think that HW like memory will be causing any problems. They forget that often with memory it is not a black or white issue. It is not an all or nothing failure. It sometimes happens and sometimes it does not. I have never really seen a failure that I would say without question, that is memory!
You can get it here (Linux GPL, and windows version); http://www.memtest86.com/

(There is also a non GPL, but still free version for windows available here http://hcidesign.com/ I am not to familiar with how it works, but the web page gives you a lot on information)

One thing to note, this can run for a very long time, several hours in some cases.

GENERAL FEATURES
Memtest gives a user the ability to access the memory in an effort to pinpoint a problem in the memory itself. It uses a set of algorithms to check for consistency and errors in the placing of memory. The algorithms that are used by memtest to test the memory are the following:

Address test, walking ones, no cache
a. Fills in the address space with ones in a sequential order
Address test, own address
a. Puts the address of the test address in itself
b. Test for addressing errors
Moving inversions, ones and zeros
a. Checks the addresses using a series of ones and zeros
Moving Inversions, 8 bit pattern
a. Uses an 8 bit wide pattern to test for errors on “wide” memory
Moving inversions, random pattern
a. Creates a set of random numbers and its compliment, writes to address.
Block Move, 64 moves
a. Memory is initialized with 8 byte inverting patterns.
b. Moved every 4 MB
Moving inversion, 32 bit patterns
a. Shifts data patterns one bit for each successive address
Random number sequence
a. Writes a set of random numbers into memory
b. Checks the memory for consistency on the next pass
Modulo 20, ones and zeros
a. Uses the Modulo-X algorithm to check for errors not detected by inversions because of buffering
Bit fade test, 90 minute, 2 patterns
a. Initializes memory and then sleeps for 90 minutes
b. Checks memory after the 90 minutes is up

The point: applications still need error-free memory to execute correctly, especially today with application complexity increasing all the time. How do you replicate problems in your lab environment with such diverse environments across your network, or even more importantly, separate hardware from software failure?As always, comments/suggestions etc appreciated.

Hank Janssen

HOW-TO: CONNECTING SQL SERVER 2005 JDBC to JBOSS

admin — Wed, 14 Jun 2006 19:46:00 GMT

HOW-TO: CONNECTING SQL SERVER 2005 JDBC to JBOSS

We have been working in the labs regarding testing and integration of jBoss talking to a SQL server 2005 backend.

We have seen some questions on how to connect a SQL Server in the Windows environment to jBoss. So I wrote a little how-to that describes just that.

It turns out that it is actually very simple.

Essentially, all that’s involved with installing the driver is:

Download the jdbc driver from here; http://www.microsoft.com/downloads/details.aspx?familyid=e22bc83b-32ff-4474-a44a-22b6ae2c4e17&displaylang=en#filelist (Scroll to the bottom of the screen)
You can either download the Windows or Unix version. It does not really matter which one you use. For this example I use the Windows version.
Run the downloaded exe file; this will create a directory called Microsoft SQL Server 2005 JDBC Driver in the directory you downloaded the file to.
Copy the file Microsoft SQL Server 2005 JDBC Driver\sqljdbc_1.0\enu\sqljdbc.jar to your $JBOSS_HOME/server/default/lib
Set your data source of your application to use the driver;

<local-tx-datasource>

<jndi-name>DATASOURCENAME</jndi-name>

<connection-url>jdbc:sqlserver://DATABASE URL;databaseName=jboss;</connection-url>

<driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>

<user-name>DATABASE USERNAME</user-name>

<password>DATABASE PASSWORD</password>

</local-tx-datasource>

NOTE: Substitute DATASOURCE NAME, DATABASE-URL, DATABASE USERNAME and DATABASE PASSWORD for your local values.

And that is all there is to it. jBoss now should be able to connect to the SQL Server.

Special thanks to Dan Simonton one of our penguins who did a bunch of the testing on this.