November 29, 2002
DCOracle2 on Windows
I've just installed the DCOracle2 DB-API module for Python on my laptop. It was quite a challenge. I like to think of myself as quite a savvy Python user, well installer really.
I will install at least one or two modules a month to test them out, or because I need them to perform a particular task. For my Oracle coding I have been mainly using cx_Oracle but I wanted to test some recent work against as broad a canvas as possible, hence DCOracle2.
I grabbed the .zip file from the web and unzipped it to a temporary directory. I then tried to follow the usual convention of python setup.py install but that didn't work well at all. After some mucking about and not very much help from the documentation here is how to install DCOracle2 on a Windows system (example using Python 2.2 on Windows2000);
- Download the latest zip file from the web and save to a temporary directory (in my case C:\Temp).
- Edit line 63 of the file install.py and append and not version == "2.2" to the end of the if statement.
- Start up a command line window and change directory to your temporary directory, then type python install.py (note the difference to standard distutils practice).
- You should now have a directory under your temporary directory called DCOracle2. Take this directory and copy it to your site-packages directory (on my machine this is C:\Python22\Lib\site-packages).
DCOracle2 is now installed and should be available to you.
Posted by Andy Todd at 11:49 AM | Comments (5)
November 28, 2002
XML in Python
Richard is having a few problems with XML in Python. Sadly I have no encouragement to give. Just a comment that the book Python and XML, which I am currently reading, isn't much better than the standard documentation.
I have spent half of my reading time scratching my head or searching for half hidden definitions of terms. I will attempt a full review when I actually manage to finish it, but so far my impressions aren't good.
Posted by Andy Todd at 05:12 PM | Comments (0)
November 22, 2002
Just a quickie
I had a need to quickly get a screen print from my computer this morning. Thanks to the folks at Download.com I got my sticky mits on Screen Print 32.
I must say I'm quite impressed. It is free for personal use, gives me full control over the whole process, including which parts of the screen to capture, the format of the resulting file (including png - the graphics file format of all right on software dudes) and uses about a micron of memory. Well done folks.
Posted by Andy Todd at 10:13 AM | Comments (0)
November 21, 2002
Oracle v SQL Server, Part 2
My next challenge was to load some data into a table from a flat file (in this case a comma delimited file). In Oracle you create the table in your SQL tool of choice and then load the data using SQL*Loader.
You can do this in SQL Server too. Create the table in SQL Query Analyzer and then load your flat file using bcp. But this is Windows, so there must be a wacky visual doo-hicky to do the job for you, and there is. Say hello to Data Transformation Services, a.k.a DTS. Which is a wizard. Which is nice.
I like DTS, but I have found one drawback. If you load data from a flat file it does not allow you to specify which table to load it into. It just uses the name of the source data file as the name of the table to load the data into.
This caught me out the first time I used it but I soon adapted to the new world order. If the table already exists in the database you specify then DTS dutifully tries to load the data into it. Obviously if the structure of the table is wrong you get errors, but this method does allow me to pre-create my tables. I just have to rename my data files.
Posted by Andy Todd at 01:40 PM | Comments (0)
November 20, 2002
PL/SQL vs Transact-SQL, Part 1
Converting the tables and associated objects was a breeze, not so the little snippets of code I wrote.
I'm slowly going through my PL/SQL code and converting it to Transact-SQL. I'll make notes here as I go. The first thing to note is that in Oracle there is a real difference between plain SQL and PL/SQL, in SQL Server there isn't really any separation. This means that in T-SQL you end up with a series of statements, but in Oracle you have to structure your PL/SQL and keep it seperate from any pure SQL (DML or DDL) operations.
First, declaring variables. PL/SQL forces you to declare variables in the scope of the block in which they are used, because the structure of the atomic unit of code is;
DECLARE
-- local variable declarations
BEGIN
-- code statements
END;
In T-SQL the atomic unit of code is the individual statement so you can declare local variables wherever you like. An easy conversion is just to replace the declare section of your PL/SQL block with a series of DECLARE statements in T-SQL. But, in T-SQL you cannot set a value in a local variable when you define it, you must do this in a seperate statement. So something like;
l_date DATE := to_date('31-JUL-02');
becomes;
DECLARE @l_date DATETIME;
SET @l_date = '31-JUL-02';
More will be forthcoming, as and when I find it. If I have made any mistakes, please feel free to correct me via the comments. As a side note, I would love to write this in Python but that is ruled out for the same reason as Oracle, the client doesn't have it.
Posted by Andy Todd at 04:27 PM | Comments (0)
Oracle v SQL Server, Part 1
I recently needed to develop an application in a hurry at work, so I built the database in Oracle. However, the work is now going to be ongoing and the client uses SQL Server.
So I am converting my tables and associated scripts. As an Oracle bod for many a year this new fangled technology is all a bit different, but strangely familiar. I am going to use this blog to record those little differences as and when I find them.
First, DDL. When creating tables in SQL Server data types are subtly different.
- VARCHAR2 should be replaced by VARCHAR
- NUMBER should be replaced by DECIMAL (or NUMERIC)
- NUMBER columns with no digits after the decimal point can be converted to INT (or BIGINT)
- DATE should be replaced by DATETIME
- SQL Server has a MONEY (and associated SMALLMONEY) data type, Oracle doesn't. Use at your own risk.
- CLOB should be replaced by TEXT
- BLOB should be replaced by IMAGE
A minor point, but if you are migrating some SQL scripts, REM isn't a valid comment marker in Transact-SQL, replace it with double dash ("--") or put C style comment markers (/* */) around whole blocks of comments.
Posted by Andy Todd at 03:17 PM | Comments (5)
November 06, 2002
The Well-Formed Web
Not a lot happening around here at the moment. I'm concentrating on reviewing and updating (where necessary) the gadfly documentation.
In the meantime can I suggest you wander over to the Well-Formed Web a new web site from Joe Gregorio. I point it out because its an interesting idea, and should be realisable in Python. Also because I've been thinking about web and application architectures, courtesy of my job, and this hit my train of though at an interesting angle.
Posted by Andy Todd at 01:51 PM | Comments (0)
November 04, 2002
Create csv files from Oracle
After resolving the issue mentioned eariler I successfully massaged my data. I then needed to extract the data into a convenient interchange format. To create comma seperated values from Oracle in the past I have written table specific queries in SQL*Plus and hacked the resulting output file into a reasonable format.
Not this time though. I resolved to write a generic csv creator and did just that in about two minutes. Say hello to csvDump.py. If your browser has problems with displaying the text you can download the Python code here.
The only prerequisite is that you have the cx_Oracle db API driver installed. If you wish to use DCOracle2 then simply change lines 13 and 35, replacing 'cx_Oracle' with 'DCOracle2'.
Suggestions for improvement, feedback and patches are gratefully received.
Posted by Andy Todd at 04:33 PM | Comments (0)
Oracle Tips
I took my laptop home this weekend to do a little work. I was massaging some data for a client and thought I could get a head start by running some updates over the weekend.
As a job was running when I wanted to leave I simply unplugged the laptop from network and power and put it in my bag. Once I got home I opened it up again and the job carried on running. And running. And running. And running. In fact, it was still running at seven this morning as I prepared to leave home for work. So either my database was having trouble or I write the worst SQL in the world. Either was possible so I shut the machine down in a fit of pique and left for work.
When I got here I booted up, connected to the office network, and ran the job again. Which finished in three minutes. The moral of this story being that removing the computer running Oracle from the network is a bad thing. Even though I wasn't actually using any of the network features of the product. Weird. I'll have a look at the support boards when I get a chance.
Posted by Andy Todd at 09:36 AM | Comments (0)