Re: Access to AS/400 data

On Fri, May 16, 2008 at 10:49 AM, Jim Steil <steil <at> mailbag.com> wrote:
> Carlos Hanson wrote:
>
> On Fri, May 16, 2008 at 8:14 AM, Michael Bayer <mike_mp <at> zzzcomputing.com>
> wrote:
>
>
> On May 16, 2008, at 10:55 AM, Carlos Hanson wrote:
>
>
>
> On Fri, May 16, 2008 at 6:13 AM, Jim Steil <steil <at> mailbag.com> wrote:
>
>
> Hi:
>
> Can anyone tell me if it is possible to access data on an AS/400
> through
> SQLAlchemy?
>
>   -Jim
>
>
> I'm connecting to an AS/400 using pyodbc, so I am sure that I can do
> it through SQLAlchemy.  If I have a chances to test it, I'll post my
> success.  But if you get an ODBC connection set up, the re should be
> no problem.
>
>
> well, "connecting" is just the beginning.  to take advantage of SQLA,
> you would also want an AS/400 dialect that knows how to render SQL in
> the way an AS/400 likes.  Im not familiar with anyone working on an AS/
> 400 dialect at the moment.   I only know of the DB2 dialect which is a
> separate project (but maybe ask on their list since they work for IBM).
>
>
> This is a good point. I have to create aliases to a file/member
> combination to select data. I guess I wouldn't expect SQLAlchemy to
> implement that by default, since most every other database uses
> tables.
>
>
>
> Are you saying that you had to create aliases to make them work with
> pyodbc?  I don't have to create aliases but it works fine for me.  I do have
> to qualify with a library name, but that isn't too painful.
>
>     -Jim

If you only have one member in a file, or you are only interested in
the first member, then you do not need an alias.  The application
running on our AS/400 was designed for multiple groups, each group
with its own member of a file, so I need the alias.  There may be some
cases where I don't actually need the alias, but it is too much effort
to make the determination.  It's easy to create and drop aliases.

Here is a brief example of what I'm doing:

>>> alias_map = {'library': 'FASFILES', 'file': 'LFAS310C', 'member': 'TSD2300'}
>>> sql_alias = 'CREATE ALIAS X%(file)s FOR %(library)s.%(file)s (%(member)s)'
>>> alias = sql_alias % alias_map
>>> alias

'CREATE ALIAS XLFAS310C FOR FASFILES.LFAS310C (TSD2300)'

>>> drop_alias = 'DROP ALIAS X%(file)s' % alias_map
>>> drop_alias

'DROP ALIAS XLFAS310C'

>>> query = 'select FA# from X%(file)s where FA# = 37250' % alias_map
>>> query

'select FA# from XLFAS310C where FA# = 37250'

>>> import pyodbc
>>> connection = pyodbc.connect('DSN=as400_64;UID=username;PWD=password;')
>>> cursor = connection.cursor()
>>> cursor.execute(alias)
0
>>> cursor.execute(query)
<pyodbc.Cursor object at 0x2b5194aef690>

>>> for row in cursor:
...   print row
...
(Decimal("37250"), )

>>> cursor.execute(drop_alias)
0
>>> cursor.close()
>>> connection.close()

--

-- 
Carlos Hanson

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To post to this group, send email to sqlalchemy <at> googlegroups.com
To unsubscribe from this group, send email to sqlalchemy-unsubscribe <at> googlegroups.com
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---