How to set up IronPython in an Intranet

For general instructions first consult CodePlex. I'll just stress the fact that the minimal prerequisites are the Microsoft .NET Framework Version 2.0 Redistributable Package (x86).

Though there are people around who cry for .msi installers, I'm very happy that we do not need those and I hope you 'll never depend on installers for IronPython. It is very nice just to unpack some files, using different releases at the same time, to get rid of them by just deleting one directory and in an Intranet you just need to know where the local guru maintains the current IronPython Files.

Just to save some typing of full pathnames it is a nice thing to have a special directory C:\bin for example (note my UNIX background) and add this to the PATH variable.

Than after unpacking some IronPython Releases to their the default locations
C:\IronPython-1.0.1
and
C:\IronPython-1.0

and putting the following two command-files into the C:\bin directory

------ ipy.bat --- this will always customized to refer to the newest release
@echo OFF
C:\IronPython-1.0.1\ipy.exe -X:TabCompletion %*

------ ipy1.0.bat ---
@echo OFF
C:\IronPython-1.0\ipy.exe -X:TabCompletion %*

and having installed CmdHere, starting any IronPython Script say myscript.py
is as simple as

• locate the script using the Explorer
• Open a cmd-shell using CMDHere
  
• and type: ipy myscript.py

To compare the behavior with the last but one release just type

• ipy1.0 myscript.py
  

Im lokalen Intranet machen wir das natürlich noch eine Spur einfacher. Alle die mitspielen wollen, installieren das Microsoft .NET Framework Version 2.0 auf ihren Kisten und richten sich ein Verzeichnis für Command-Skripte ein (Vorschlag: C:\bin), welches dem Pfad hinzugefügt wird.
Die aktuelle IronPython Version steht im Netzwerk auf einem intern bekannten Verzeichnis zur Verfügung.[hier im blog steht der Pfad definitiv nicht] Einige lokaler Erweiterungen sind dort unter lib abgelegt und können somit direkt importiert werden.
Der einzelne muß nur noch seine Kopie von ipy.bat in sein C:\bin bringen und auf das zentrale IronPython Verzeichnis angepassen.

Für ServiceRechner außerhalb des Intranets auf denen der jeweils benötigte Datenbankzugriff sowie das Framework 2.0 installiert ist, muß für Oracle eventuell noch der Databaseprovider installiert werden. Sonst ist lediglich das IronPython Verzeichnis auf den Rechner zu kopieren und wenn man den kompletten Pfad zur ipy.exe ausschreibt, dann braucht man nicht einmal Pfade zu verändern.

Danach können wir mit IronPython Skripten die Aufgaben lösen, bei denen uns die üblichen Bordmittel und auch die üblichen Zusatztools im Stich lassen. Typisches Besispiel sind hier die üblichen Längenbeschränkungen die einem die Ausgabe von langen Strings abschneiden.
Zu den Programmen die ihrer Aufgabe unvollkommen erledigen gehören besonders bcp, dts, SQL*Loader und natürlich mein besonderer Liebling SQL*PLUS.

SET SERVEROUTPUT ON SIZE UNLIMITED

This is one off the really nice features of release 10. Even better you need release 10 only on the client side. I was really surprised when I connect to a 9.2 server and there it worked as well, I would had bet that it depended on the server too.

PS.: I'm using now the Firefox 2.0 for spell checking.

Yet Chrismas ?

# -*- coding: cp1252 -*-


allowed in IronPython v1.0.1 and no word in the v1.0.1 Release Notes. This was one off the major annoyances when trying to reuse old Python scripts. I only discovered it by chance when I was collecting material for my planned pitfall section.

C:\D\_svn\trunk\examples\python>ipy simple_text_demo.py
228
u'abc\xe4'
u'abc\xe4'
abcä

C:\D\_svn\trunk\examples\python>ipy10 simple_text_demo.py
Traceback (most recent call last):
File mscorlib, line unknown, in .ctor
File mscorlib, line unknown, in .ctor
TypeError: Value cannot be null.
Parameter name: encoding

C:\D\_svn\trunk\examples\python>

You find the example here. Many thanks to the IronPython developers. This saves me a lot of time.

Comming soon

Abstract:

• something about the Python scripts I've written in the past
  
• the advantages of switching to IronPython
• initial problems when applying IronPython to real tasks
  
• the audiance I will reach with this blog

Zusammenfassung:

• Ich erkläre kurz die Art von C-Python Skripten, die ich bisher geschrieben habe
  
• warum mir IronPython eine Fülle neuer Möglichkeiten eröffnet
• welche Anfangsprobleme beim Umstieg auf IronPython auftraten
• wen ich in diesem Blog ansprechen möchte

Most of the python I've done in the past just was the combination of Filesystem-Access and a lot of fiddling with regular expression (module re). Most of these scripts process SQL-Server scripts and produce some resturctured, beautified and well -wrapped scripts as output. A second kind helps me transforming scripts to Oracle.

I'm not using any sql-grammars, only heuristic aproches. When the sql is written in suitable format this aproch is goes rather far.

My standard editor for python is IDLE.

Speaking german most python scripts will include strings containing our german Umlaute (äöüßÄÖÜ) and some other characters as (§ µ °) which are not includes in the 7-bit Ascii half.

So I had to chose between starting the python-scripts with either

# -*- coding: cp1252 -*-

or using lots of unreadable escape sequences.

The sql scripts I have to process use a nice mixtures of OEM 850, cp1252 , utf8 and some were corupted by editing them using the wrong characterset. [That's just live].

Unicode or not can be detected automaticly. And for the rest one can take a pragmatic approch. Only a few additional character with ord() > 127 are in commen use in the german local and the sets of these codes from the two chartersets do not overlap.

Did you notice that I told nothing of database access from python. Well I just didn't try.

Database access is a fundamental part of my every days work. Anything from DAO ( help me forget all about VB3), ADO (VB6) und finally ADO.NET (C#). But without IronPython database access needs some additional components and I get no one else to use it.

IronPython = Python + integrated Database access + Windows.Forms

With IronPython I use exactly the same components as the C# devellopers.

The framework 2.0 is installed or comes any case, all what is missing is the download, unpacking and copying of the IronPython directory and they can start.

Ok I assume you have your databases allready working and if you are using Oracle you may have to get the ODP Database Provider for.Net.

In short: I intend to use IronPython mainly for scripting of .NET. I know about the way to access the python modules from a current C-Phython installation and I know about the FePython people, but I don't think I'll need their modules soon.

My intendet audiance are primarily DBAs of Oracle and SQL-Server Databases and of corse all python fans who are interessted in this topic.

In coming post I intend

• a minimal Installation HowTo (in german) for DBAs in .Net environments, which just need to add IronPython to their toolbox
  
• some small talk about the pitfalls I met switching from Python to IronPython. Most dealing with charactersets, unicode and €.
  
• explore the posibilities of accessing the databases by .Net (presumably a never ending story). Let's dive into ADO.NET
  
• some little wrappers around sql*plus and query analyzer so that I can use them for things they are suited for (and redirect their nasty error messages)
  

Meiki

Seem's to be rather usual to use one's pets for testing uploading of pictures.
Perherps I ought reduce the resolution of the picture.

So that's Meiki one of our two cats. Both knows exactly about keyboard and mice and where to sit to get attention.

Did by chance oneone find my svn ?
At python examples
is a C-Python module for reading Text Files stressing charset features and some test text files.
The transition to IronPython and some description follows soon.

Mühsam ernährt sich das Eichhörnchen

The blogs titel means that it takes just more time then intended to make this blog comfortable for me, but nevertheless progress takes place.

• following Mark's comments meanwhile I succceded in creating a project at http://code.google.com
• after some initial problems with TortoiseSVN 1.4.0 which didn't show up when I tried it again (mystery) the nice littel iconsextensions show up in the explorer
• home page of the Project is still empty, but browsing the files, momentary there is only one works.
• I don't give a link right know, as I'm sure you can find it anyway
• If you find it please notice that the single sql-statements are committed automaticly and there is no way to rollback. I really have the impression, that some things, for example changing the connection is far to easy and intensiv use of the command history might lead to confoundings between sandbox and the real work.
  
• The current practical use is copying complete CLOB fields to the spool file.

If you Stumbled in here looking for some solutions, better come back in some weaks. Well I know you too needed it yesterday.

For the moment I'm just begining and any help to make this blog more usable is welcome. Perhaps the use of some spellchecker's.

As I try to focus this blog to the rather special topic of IronPython in connection with either one of the two Databases ORACLE or SQL-Server, I'll post most of the database related articals in english, which just wasn't any sort of fun for me at school.

Trials to write in german an use the google language tools for the translation are unsatisfactorily. [ Sag ich doch gerade. Mal sehen was www.logosdictionary.org sagt ] Ahh unsatisfactory sounds better.

So please drop decent note, when "I' m comletely on the woodway with my english", i.e. if I could be completely misunderstood.


Bye bye for the moment, I just have to look at some other blogs

EMPTY_CLOB() really required?

Help my PL/SQL works - [ ja darf es denn das?]

Contrary to all advises I don't try to initialise CLOB Fields with empty_clob().
And not wanting to use SQL*Loader (need to many distinct files). I use code like the following:

drop table tmp_test_bk1;

set escape off
set escape '\'

create table tmp_test_bk1 (id number(10), wert clob);

insert into tmp_test_bk1 (id) values (1);

declare
v_wert clob;
begin
v_wert := '

...
and many, many more lines, giving more than 64000 Bytes
inspite of the limit of 4000 Bytes
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1019631214472
....
'
;
update tmp_test_bk1 set wert = v_wert where id = 1;
end;
/

select length(wert) from tmp_test_bk1;

using a script which some real data , using
Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
I get no Problems but the following result:

LENGTH(WERT)
------------
64206

Finding no current tools able to display long multiline contents in databases without some clipping, caueses me to build something in Iron Python for Oracle and for SQL-Server

In a few days I'll put the IronPython Code in some repository, so that you don't have to fiddle around with the old copy&waste [I really mean waste] technologie, so be patient.

Just have to prepare the usual disclamers:
Rauchen schadet Ihrer Gesundheit
Wegen der Nebenwirkungen fragen sie ihrern Arzt oder Apotheker
[translation to englisch wanted]
meanig that you can freely use the code examples but absolutely at your own risk.
And I have to organise the folder structure. Restructioning of repositories is no fun, so I'll better do it right in the beginning.

Rollback;

Hello Catherine - hello Mark

Ich brauch mal ne kleine Pause - I just need a little rest
- puting pictures of my cats is obviously an item for my takslist for blog design

but the real ToDo list looks more like this

1. create an project at http://code.google.com/hosting/ -- done
   
2. invest a little time in blog presentation. I don't want to win a price, I just need a painless way to get the information in (and for the readers to get it out).
3. Find out about the correkt use links,
   
4. and labels in post.
5. find out how to get notified about new comments to my postings
6. relax for a while
7. hope to meanwhile find some links with help for novice bloggers
8. Review the IronPython Code which evolves currently. (Focus is a painless way to display complete CLOB Fields in Oracle or Text Fields in SQL-Server.)
9. Review again for the completeness of the errorhandling and dispose() and close() call
10. Think abount some gui interface are there other way's to avoid OEM-Character-Code?
11. Ask some wise man, why they can't just offer an ACP Codepage for cmd.exe
12. Ask Tom wheather there is a sane reason for not storing the original Select star from anything view definition.
13. Find out if there is some search engine which can find "select * from" Oracle

As with any real live ToDo-List the last points have lower priority and perhaps I never get to them.

A minimal sqlplus clone in IronPython

Hier folgt der Code für ein einfaches IronPython Programm, welches interaktiv die Eingabe von Sql -Statements und das Ausführen auf Oracle Datenbanken ermöglicht. Ich verwende den ODP, offenbar ist er bei Oracle 10g XE dabei, denn ich habe ihn nicht extra installiert. Außerdem beziehe ich mich auf hr das bekannte demo Schema.

Nach dem Start könnte eine Beispielsession wie folgt aussehen:

C:\D\FePy\sqldb>ipy uifs_oda_public.py
Data Source: xe
Username: hr
password: hr

ServerVersion: 10.2.0.1.0

Connection state: Open
hr@xe -->>
select table_name
from user_tables

> select table_name
from user_tables



TABLE_NAME REGIONS

TABLE_NAME LOCATIONS

TABLE_NAME DEPARTMENTS



und nun der Python Code:

------------------------- uifs_oda_public.py -----------------------------------------

'''
Bernd Kriszio: 2006-11-05
'''
import clr
import sys
clr.AddReference("System.Data")
clr.AddReference("Oracle.DataAccess")

import System.Data
import Oracle.DataAccess.Client

class ConnectionProperties :
def set(self, string):
self._dict = {}
for i in string.split(';'):
k, v = i.split('=')
self._dict[k.lower()] = v
def __getitem__(self, key):
return self._dict[key]

def n2onoff(val):
if val:
return 'ON'
else:
return 'OFF'

class Connection:
def __init__(self, ConnectString = ''):
self._echo = 0
self._feedback = 0
self._prompt = '>'
if not ConnectString:
ConnectString = self.queryConnectInfo()
self.Open(ConnectString)

def queryConnectInfo(self):
dataSource = raw_input('Data Source: ')
user = raw_input('Username: ')
password = raw_input('password: ')
return 'Data Source=%s;User ID=%s;Password=%s' % (dataSource, user, password)


def Set(self, OptionString):
''' some resamblance to SQL*PLUS
Tablemode on|off|auto
'''
s = OptionString.upper().strip()
if s == 'ECHO ON':
self._echo = 1
elif s == 'ECHO OFF':
self._echo = 0
elif s == 'FEEDBACK ON':
self._feedback = 1
elif s == 'FEEDBACK OFF':
self._feedback = 0

def Show(self, OptionString):
''' when there are more options i'll work it out, meanwhile SHOW ALL
'''
print 'ECHO is ' + n2onoff(self._echo)
print 'FEEDBACK is ' + n2onoff(self._echo)


def Open(self, ConnectString):
self._ConnectString = ConnectString
self._ConnectionProperties = ConnectionProperties()
self._ConnectionProperties.set(self._ConnectString)
self._Connection = Oracle.DataAccess.Client.OracleConnection(self._ConnectString)
self._Connection.Open()

print '\nServerVersion: ' + self._Connection.ServerVersion
print '\nConnection state: %s' % self._Connection.State.ToString()

def Close(self):
self._Connection.Close()

## def getConnection(self):
## return self._Connection

def setCommand(self, query):
self._Command = Oracle.DataAccess.Client.OracleCommand(query, self._Connection)

def ExecuteReader(self):
self._Reader = self._Command.ExecuteReader()
## bei SELECT immer -1
## if self._feedback == 1:
## print self._Reader.RecordsAffected

def ExecuteNonQuery(self):
self._count = self._Command.ExecuteNonQuery()
if self._feedback == 1:
print '%d rows affected' % self._count

def ExecSql(self, query):
if self._echo:
print '> ' + query
try:
## if 1:
if query.strip()[:6].upper() == 'SELECT':
self.setCommand(query)
self.ExecuteReader()
self.print_Reader()
elif query.strip()[:5].upper() == 'EXEC ':
statement = query[5:]
print 'statement %s' % statement
proc = statement.split(' ')[0]
print 'proc %s' % proc
elif query.strip()[:5].upper() == 'DESC ':
## print '--> desc'
statement = query[5:]
self.desc(statement.upper().strip())
else:
## print '--> else'
self.setCommand(query)
self.ExecuteNonQuery()
## if 0:
except StandardError, e:
if self._echo == 0:
print query
print "Fehler: ", e


def print_Reader (self):
rdr = self._Reader
if rdr:
anz = rdr.FieldCount
print
cnt = 0
while rdr.Read():
cnt += 1
for i in range(anz):
## print '%-30s %-30s%s' % (rdr.GetName(i), rdr.GetFieldType(i), rdr[i])
print '%-30s %s' % (rdr.GetName(i), rdr[i])
print
self._count = cnt
if self._feedback == 1:
print '%d rows affected' % self._count
rdr.Close()
rdr.Dispose()
self._Reader = None


def prompt1(self):
return '%s@%s -->>' % (self._ConnectionProperties['user id'], self._ConnectionProperties['data source'])

def ufi2(self):
''' multi line commands
I would like to use cancel ^C for cancel,
but KeyboardInterrupt works somewhat different in IronPython
'''
cmd = ''
print self.prompt1()
while 1:
a = raw_input()
try:
a_lc = a.lstrip().lower().split()[0]
except:
a_lc = ''
## work around. I would like to cancel input with ^C
if a_lc == 'cancel':
print 'input canceld'
cmd = ''
print self.prompt1()
elif a_lc == 'set':
self.Set(' '.join(a.lstrip().split()[1:]))
elif a_lc == 'show':
self.Show(' '.join(a.lstrip().split()[1:]))
elif a_lc == 'connect':
## print 'a:', a
p = ' '.join(a.lstrip().split()[1:])
## print 'p: ', p
try:
(up, dataSource) = p.split('@', 1)
except:
up = p
dataSource = self._ConnectionProperties['data source']

print up, dataSource

try:
user, password = up.split('/',1)
except:
user = up
password = raw_input('password: ')
connectString = 'Data Source=%s;User ID=%s;Password=%s' % (dataSource, user, password)
print '--> connect %s' % connectString
self.Close()
self.Open(connectString)
else:
cmd += a + '\n'
if cmd.lower() == 'quit\n':
break
if a == '' and cmd.strip():
## print 'calling: %s' % cmd
self.ExecSql(cmd)
else:
continue
cmd = ''
print self.prompt1()

## ----- Self test call -------------------------------------------------------------------------
if __name__=='__main__':

conn = Connection()
conn.Set('Echo on')
conn.Set('Feedback on')
conn.ufi2()
conn.Close()

begin IronPython; end;

Hello IronPython world - viele Grüße aus Deutschland

I plan to post here some notes, code examples and links concerning IronPython, databases as Oracle and SqlServer and their procedural extensions PL-SQL und T-SQL.

One idee is to build a minimal clone of SQL*PLUS in IronPython.

Why just SQL*PLUS? At the positive side it is rather dynamic

Var r ref cursor
exec something_vital(:r)
print r

till mow, I found no simple way to display the returned resultsets in neither TOAD nor sql-developer.
And it displays cursor expressions in Select statements directly. Try the following using the hr schema:

select DEPARTMENT_NAME, cursor (select first_name, last_name from employees e where e.department_id = d.department_id) from departments d;

Are they parsing this statement by themself.
Sql-Developer throws an error and my atempts via Ironpython and Oracle.DataAccess.Client aren't better.

At the other side, there are some handicaps with this tool, lets try to ignore them and do it better.