Access DB2/400 from IBM i (AS/400) PASE environment

[Jim S]

Hi

In a former (and somewhat current) life I was an AS/400 guy.  Our shop still uses the platform though most of my time is spent on Python and web2py now.

Recently Python became available and officially supported on IBM i and I'm trying to get web2py running there accessing the local DB2/400 database.  Using the local python on the system I can create a database connection to the local database doing this:

import ibm_db_dbi as db

conn = db.connect(database='*LOCAL')

I'm hoping to find an easy way to convert this into a connectstring for the DAL so I can have my database created there.  The SQL-flavor it should use would be the same as ODBC-flavored SQL.  

Can anyone give me a clue how to modify the DAL code to connect to my db?  Any pointers to other articles or links would really be appreciated.  It would be exciting for me to get this working since I could then show RPG developers how easy it would be to get an application on the web from the AS/400 using python/web2py.

-Jim

[António Ramos]

maybe

http://www.web2pyslices.com/slice/show/1474/calling-remote-program-on-db2-from-pythonweb2py

Sem vírus. www.avast.com

--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups "web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[António Ramos]

Im starting to learn RPG and also want to know how to web2py my Iseries 7.2....

[Jim Steil]

In this example he is connecting using a DB2 connector.  However, I'm hoping to use the ibm_db_dbi connector and don't know how to specify it on the dal connection.  He is using:

db = DAL('db2://DSN=MYDSN;UID=xxxxx;PWD=xxxxx', migrate_enabled=False)

I'm not sure what to put in place of the db2://DSN...

Anyone?

-Jim

You received this message because you are subscribed to a topic in the Google Groups "web2py-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/web2py/qrlN5TSSBgs/unsubscribe.
To unsubscribe from this group and all its topics, send an email to web2py+unsubscribe@googlegroups.com.

[DenesL]

Hi Jim

Python officially supported on i, finally!!
I wish this had happened a while back.

It seems that this would require a modified adapter in web2py,
sadly I have no access to a current 7.x box to test.

Denes

On Saturday, March 18, 2017 at 11:11:34 AM UTC-4, Jim S wrote:

In this example he is connecting using a DB2 connector.  However, I'm hoping to use the ibm_db_dbi connector and don't know how to specify it on the dal connection.  He is using:

db = DAL('db2://DSN=MYDSN;UID=xxxxx;PWD=xxxxx', migrate_enabled=False)

I'm not sure what to put in place of the db2://DSN...

Anyone?

-Jim

On Fri, Mar 17, 2017 at 7:38 PM, António Ramos wrote:

maybe

http://www.web2pyslices.com/slice/show/1474/calling-remote-program-on-db2-from-pythonweb2py

[Jim S]

Yes, support became available for Python 2 and Python 3 last summer.  Came out in Technology Refreshes.  They still have a long way to go getting more packages supported, but base support is there.  They also have a local dbapi connector to connect to a DB2/400 database which is what I'm trying to connect to with the DAL.

I don't know where to begin modifying an adapter, looking for help.

-Jim

[José Ricardo Borba]

Last summer? We are in summer!

--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups "web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

José Ricardo Borba

[Jim Steil]

Ok, last summer in the US.  I think it was in July of 2016 but not sure.

You received this message because you are subscribed to a topic in the Google Groups "web2py-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/web2py/qrlN5TSSBgs/unsubscribe.
To unsubscribe from this group and all its topics, send an email to web2py+unsubscribe@googlegroups.com.

[DenesL]

Last summer uh? Not keeping up with i news lately...

I looked it up as soon as I saw your post today, that is why I said it seems to need a modified adapter to map things thru ibm_db.
It might be fairly easy and I would like to help but the lack of a newer i box to test with doesn't help.

Denes

[Jim Steil]

Are you familiar with Aaron Bartell's work and litmis spaces?  spaces.litmis.com

There you can get a free PASE session on their boxes.  Use the code BETA when signing up.  Let me know if you have any questions.

-Jim

[António Ramos]

Iseries 7.x in the cloud

http://pub400.com/

tutorial about iseries using pub400

https://www.youtube.com/watch?v=vTOC0YXs9Ts

You received this message because you are subscribed to the Google Groups "web2py-users" group.

To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscribe@googlegroups.com.

[DenesL]

Litmis only has Python-3.4.4

On Monday, March 20, 2017 at 10:08:07 AM UTC-4, Jim S wrote:

Are you familiar with Aaron Bartell's work and litmis spaces?  spaces.litmis.com

There you can get a free PASE session on their boxes.  Use the code BETA when signing up.  Let me know if you have any questions.

-Jim

[DenesL]

Thanks Ramos. Python 2.7.11 at pub400.

Can you guys provide a save file to FTP to pub400? they are running v7r3m0.

Denes

On Monday, March 20, 2017 at 10:23:34 AM UTC-4, Ramos wrote:

Iseries 7.x in the cloud

http://pub400.com/

tutorial about iseries using pub400

https://www.youtube.com/watch?v=vTOC0YXs9Ts

[Jim Steil]

I've got 2.7 in my space even though it is labeled 3.4.4.

and you can use the python3 command to get into python3

-Jim

[António Ramos]

just sharing my search of python and iseries ( looks like bottle framework is supported by ibm...)

https://www.gateway400.org/documents/Gateway400/Handouts/GetStartedWithPythonOnIBMi2016.pdf

https://www.youtube.com/watch?v=-fbrQwBKQ_I&t=929s

You received this message because you are subscribed to the Google Groups "web2py-users" group.

To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscribe@googlegroups.com.

[Jim Steil]

Yes, I knew bottle was available.  But, I don't like this from their slides:

"We ship a slightly-modified version of the open-source bottle.py"

And of course, I want to run web2py!

[António Ramos]

PLEASE SHARE AS SOON AS YOU DO IT !

:)

[Jim Steil]

I need a DAL adapter for the ibm_db_dbi connector and have no idea where to even begin.  It's on the back-burner for now but will pick it up again when I have some free time.

-Jim

[DenesL]

I tried running web2py on PUB400 but I get

  File "/QOpenSys/QIBM/ProdData/OPS/Python2.7/lib/python2.7/threading.py", line 736, in start
    _start_new_thread(self.__bootstrap, ())
thread.error: can't start new thread

Does web2py work on litmis?.

Denes

[Jim Steil]

The error I'm getting on litmis has to do with pysqlite.  I was going to jump on and get you my error message, but seems I can't connect right now.

-Jim

[DenesL]

Could you detail how you install and start web2py on litmis?.

[Jim Steil]

Pretty sure I downloaded the zip file from github and extracted it.  Then started with command line options specifying IP of 0.0.0.0 and port.  Port can be determined by clicking on the More Info icon on the on your Spaces page.

Also, here is the error I'm getting with the Welcome app:

-Jim

[Jim S]

And then if I try to install pysqlite I get:

[usrxkcon@SPACES]~/web2py% easy_install pysqlite==2.7.0                         

Searching for pysqlite==2.7.0                                                   

Reading http://pypi.python.org/simple/pysqlite/                                 

Best match: pysqlite 2.7.0                                                      

Downloading https://pypi.python.org/packages/81/08/4de886dc16aaf99b5466c2b8b7aa4

d1b79fd52f557ae52c04a293b712389/pysqlite-2.7.0.tar.gz#md5=a8ca3c5426efc56c0d0e18

e41b9ebe4c                                                                      

Processing pysqlite-2.7.0.tar.gz                                                

Running pysqlite-2.7.0/setup.py -q bdist_egg --dist-dir /tmp/easy_install-0TAVEK

/pysqlite-2.7.0/egg-dist-tmp-nB2v6w                                             

warning: no files found matching 'doc/*.txt'                                    

unable to execute xlc_r: No such file or directory                              

error: Setup script exited with error: command 'xlc_r' failed with exit status 1

-Jim

[DenesL]

The instruction is to use a git clone command as follows

[usro5gha@SPACES]~% git clone --recursive https://github.com/web2py/web2py.git  

Cloning into 'web2py'...                                                        

fatal: Unable to find remote helper for 'https'

but it obviuosly fails.
I have never used the git version of web2py, any ideas?.

[Jim Steil]

I don't believe https is supported for git.  Or, at least it wasn't for some time.  You can try cloning with ssh, but I seem to recall that failing as well.

-Jim

[Jim S]

FWIW - I successfully started web2py on IBM i tonight.  

Three things I need to work on to make better

1.  Install doesn't seem to work with git clone - maybe something I'm doing wrong but I had to clone to my workstation then zip and upload

2.  I'm using SQLite for the db but would really like to use DB2/400.  Looking for pointers on how to build a connector for the DAL.

3.  https configuration.  Again, I don't think there are any real issues here, I just need to spend the time to get it working.  I'm running on litmis spaces now and the problem has more to do with how to configure on their system than how to get web2py to work with it.  It shouldn't be an issue if you're running on your own system.

Hopefully more to come.

-Jim

[António Ramos]

Maybe Massimo will read this and give us some hints on how to make  DAL use the ibm_dbi

Regards

Sem vírus. www.avast.com

2017-03-17 21:06 GMT+00:00 Jim S <ato....@gmail.com>:

Hi

In a former (and somewhat current) life I was an AS/400 guy.  Our shop still uses the platform though most of my time is spent on Python and web2py now.

Recently Python became available and officially supported on IBM i and I'm trying to get web2py running there accessing the local DB2/400 database.  Using the local python on the system I can create a database connection to the local database doing this:

import ibm_db_dbi as db

conn = db.connect(database='*LOCAL')

I'm hoping to find an easy way to convert this into a connectstring for the DAL so I can have my database created there.  The SQL-flavor it should use would be the same as ODBC-flavored SQL.  

Can anyone give me a clue how to modify the DAL code to connect to my db?  Any pointers to other articles or links would really be appreciated.  It would be exciting for me to get this working since I could then show RPG developers how easy it would be to get an application on the web from the AS/400 using python/web2py.

-Jim

--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---

You received this message because you are subscribed to the Google Groups "web2py-users" group.

To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscribe@googlegroups.com.

[Jim Steil]

I would love to do some work on this if there was a mentor out there willing to help me get started.

-Jim

You received this message because you are subscribed to a topic in the Google Groups "web2py-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/web2py/qrlN5TSSBgs/unsubscribe.
To unsubscribe from this group and all its topics, send an email to web2py+unsubscribe@googlegroups.com.

[António Ramos]

having web2py DAL working with ibm_db would give web2py a good visibility from IBM because they often publish documents about open source techs and IBM

and being web2py such a good product i think IBM would also be in love as we ALL are!!!

regards

[Massimo Di Pierro]

We do have an adapter for db2:ibm_db_dbi did you try it? Anyway, in general adapter are defined in 

web2py/gluon/packages/dal/pydal/adapters

You can make your own adapter. The first step is identify which existing adapter to extend. You want to pick the one with the closest SQL dialect. Then you create (in a new adapter file) a new class that extends that adapter. db2:ibm_db_dbi for example is defined in db2.py as follows:

@adapters.register_for('db2:ibm_db_dbi')

class DB2IBM(DB2):

    drivers = ('ibm_db_dbi',)

    def connector(self):

        uriparts = self.ruri.split(";")

        cnxn = {}

        for var in uriparts:

            v = var.split('=')

            cnxn[v[0].lower()] = v[1]

        return self.driver.connect(

            cnxn['dsn'], cnxn['uid'], cnxn['pwd'], **self.driver_args)

and can be called as:

db = DAL('db2:ibm_db_dbi:dsn=....;uid=...;pwd=...')

[António Ramos]

@Jim any news on this ?

Sem vírus. www.avast.com

[Jim Steil]

Yes, I was able to get it to connect and the DAL created my tables, but not it won't access them because it is looking for lower case table names and evidently when the tables were created the names became upper case.  Since I'm working with this in a Litmis space I don't have full access to do all the work needed.  I do have a 7.3 IBM i box that I'm just getting setup with the new OPS PTFs and am planning on continuing my testing when that becomes available. I was planning on reporting back once I had that all working.

Anyone know if there is a quick way to tell the DAL to ignore the case of the table/field names when connecting?

-Jim

[ramstein74]

This topic is very important. Having web2py running in a top tech ibm machine is worthy of notice.

Any help on this ... PLEASE

Anyone know if there is a quick way to tell the DAL to ignore the case of the table/field names when connecting?

Enviado a partir do meu smartphone Samsung Galaxy.

-------- Mensagem original --------

De: Jim Steil <ato....@gmail.com>

Data: 27/04/17 13:20 (GMT+00:00)

Para: web...@googlegroups.com

Assunto: Re: [web2py] Access DB2/400 from IBM i (AS/400) PASE environment

To unsubscribe from this group and stop receiving emails from it, send an email to web2py+un...@googlegroups.com.

[Kiran Subbaraman]

Will this help; not that it directly addresses your problem... but... just wondering: https://github.com/web2py/web2py/issues/1034 . Am thinking the rname capability: http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#rname--Record-representation

________________________________________
Kiran Subbaraman
http://subbaraman.wordpress.com/about/

[DenesL]

Have you tried the rname parameter in the table definition?.
Denes

On Thursday, April 27, 2017 at 8:20:40 AM UTC-4, Jim S wrote:

Yes, I was able to get it to connect and the DAL created my tables, but not it won't access them because it is looking for lower case table names and evidently when the tables were created the names became upper case.  Since I'm working with this in a Litmis space I don't have full access to do all the work needed.  I do have a 7.3 IBM i box that I'm just getting setup with the new OPS PTFs and am planning on continuing my testing when that becomes available. I was planning on reporting back once I had that all working.

Anyone know if there is a quick way to tell the DAL to ignore the case of the table/field names when connecting?

-Jim

On Thu, Apr 27, 2017 at 6:48 AM, António Ramos wrote:

@Jim any news on this ?

Sem vírus. www.avast.com

[António Ramos]

Hello @Jim 

any news  on your  

DAL and ibm_db_dbi withing an iseries?

regards

[Jim Steil]

Nothing happening.  Got sidetracked and haven't looked at it for a couple months.

-Jim

[Jim S]

FWIW - I got web2py running on IBM i v7.3 (AS/400) using a sqlite database.  

Haven't had time to get back to trying the dal with DB2 for i.

-Jim

[António Ramos]

Strange no one is interested in this.. 

aparently is not a big issue to solve 

Can i offer a "big" coffee to have web2py core developers to look into this ?

I would surely use it in my company where we have an IBM Iseries V7.2

Also web2py needs  and deserves another price after the "2012 Technology of the year"

Regards

Sem vírus. www.avast.com

[Leonel Câmara]

The DAL supposedly has support for DB2 which you can use with these connection strings:

db2:ibm_db_dbi://DSN=dsn;UID=user;PWD=pass

db2:pyodbc://driver=DB2;hostname=host;database=database;uid=user;pwd=password;port=port

  

The problem is that very few people use it, so it wouldn't surprise me at all if it is broken right now.

  

If you guys want to use it, you need to start testing it, filing issues in pyDAL and even help solve the problems, and creating tests to make sure they do not reappear. Much like the people using Oracle did not long ago. I would love a big coffee but I don't have any machine running DB2 nor do I use it for anything, even so, I may be able to help if someone is actually filing issues and willing to test my potential fixes for me.   

  

Either way, there's no need to offer coffee to web2py core developers, web2py is open source and all of you are very good developers, so... Just scratch your itch and help everyone else in the process.

[António Ramos]

looking at the code ,using the ibm_db_dbi supplied by ibm, the connection string to IBM i db2 is this

'db2:ibm_db_dbi://DSN=dsn;UID=user;PWD=pass'

but this is for windows because of the dsn right?

what if i want to connect from inside my IBM i where i have an AIX system ?

i already have python 3.6 and pydal installed in my IBM i Aix

how do i connect to my library for example ar54?

from there then i will try to query my library tables.

any clue ?

Regards

António

--

Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups "web2py-users" group.

To unsubscribe from this group and stop receiving emails from it, send an email to web2py+un...@googlegroups.com.