Re: [IBMiOSS] IBM i python ibm_db_dbi example usage

You're completely correct, John. I should be more careful with my
terminology.

Each Litmis Space has three schemas that all belong to the *LOCAL database.

How do the other Spaces folks connect to the database?

Python on Litmis Spaces is new, so this group is the first to kick the
tires. As best I can tell it appears the ibm_db connect** function
requires the user/password to be specified (Node.js doesn't require this
and Ruby is slated to work the same).

**
https://github.com/ibmdb/python-ibmdb/blob/master/IBM_DB/ibm_db/ibm_db_dbi.py#L543

Shoot me a private email with your Litmis Spaces user profile and I will
reset your password.

Also, here's a sample script to start with. Notice the modified method for
importing ibm_db. I haven't researched Python's import resolution
practices, so this works for now. Also note, this ran fine the first time
but I am now getting a "Error occurred in SQL Call Level Interface
SQLSTATE=HY009 SQLCODE=-99999" error that I am working to resolve. It
seems others have had this same SQL error.
----conn.py----
import sys
sys.path.append("/QOpenSys/QIBM/ProdData/OPS/Python-pkgs/ibm_db/ibm_db-2.0.5.1-py3.4-os400-powerpc.egg")
import ibm_db

db_name = '*LOCAL'
schema = 'LFLYD_T'
username = 'xxxxx'
password = 'xxxxx'

try:
conn = ibm_db.connect(db_name, username, password)
except:
print("no connection:", ibm_db.conn_errormsg())
if conn:
result_set = ibm_db.exec_immediate(conn, "SET SCHEMA " + schema)
result_set = ibm_db.exec_immediate(conn, "CREATE TABLE CUSTOMER ( CUSNUM
NUMERIC(6, 0), LSTNAM VARCHAR(50) )")
result_set = ibm_db.exec_immediate(conn, "INSERT INTO CUSTOMER VALUES
(123,'Smith')")
result_set = ibm_db.exec_immediate(conn, "SELECT * FROM CUSTOMER")
while (ibm_db.fetch_row(result_set)):
result = ibm_db.result(result_set, 1)
print(result)
ibm_db.close(conn)
else:
print ('connection failed')




Aaron Bartell
litmis.com - Services for open source on IBM i


On Thu, Jul 7, 2016 at 5:12 PM, John Yeung <gallium.arsenide@xxxxxxxxx>
wrote:

On Thu, Jul 7, 2016 at 5:29 PM, Aaron Bartell <aaronbartell@xxxxxxxxx>
wrote:

I don't know what my database is, and I'm not sure if I know what my

password is.

Select the 'i' icon on your Spaces home page and you will see your user

and

three separate databases (schemas, libraries).

We have to be a little careful about terminology here. In an IBM
midrange context, "database" is not the same thing as "schema", though
it is pretty safe to use "schema" and "library" interchangeably.

A database at a typical IBM i shop contains many libraries, though I
suppose there is nothing preventing you from having a database which
contains just one library.

Are you saying that each of those three environments is its own
*database*, and that the names shown are the database names, as would
appear when entering the DSPRDBDIRE command at a native CL prompt?

Litmis Spaces operates off of passwordlessness (new word? :-) What I

mean

is we definitely have passwords for IBM i profiles, but they are randomly
generated and not stored. Instead we opted to go with ssh keys for all
authentication into the machine.

That's a perfectly reasonable arrangement. It's probably what I would
opt for if I were setting up a similar service. But it creates the
issue of how to retrieve a password so that it can be used as a
parameter to the ibm_db.connect() method.

How do the other Spaces folks connect to the database?

John Y.
--
This is the IBMi Open Source Roundtable (OpenSource) mailing list
To post a message email: OpenSource@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/opensource
or email: OpenSource-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/opensource.