Connecting python to DB2 database

[DFS]

On 9/3/2021 9:50 AM, Chris Angelico wrote:
> On Fri, Sep 3, 2021 at 11:37 PM DFS <nospam at dfs.com> wrote:
>>
>> On 9/3/2021 1:47 AM, Chris Angelico wrote:
>>> On Fri, Sep 3, 2021 at 3:42 PM DFS <nospam at dfs.com> wrote:
>>>>
>>>> Having a problem with the DB2 connector
>>>>
>>>> test.py
>>>> ----------------------------------------------------------------
>>>> import ibm_db_dbi
>>>> connectstring =
>>>> 'DATABASE=xxx;HOSTNAME=localhost;PORT=50000;PROTOCOL=TCPIP;UID=xxx;PWD=xxx;'
>>>> conn = ibm_db_dbi.connect(connectstring,'','')
>>>>
>>>> curr  = conn.cursor
>>>> print(curr)
>>>
>>> According to PEP 249, what you want is conn.cursor() not conn.cursor.
>>>
>>> I'm a bit surprised as to the repr of that function though, which
>>> seems to be this line from your output:
>>>
>>> <ibm_db_dbi.Connection object at 0x000002154D2FED30>
>>>
>>> I'd have expected it to say something like "method cursor of
>>> Connection object", which would have been an immediate clue as to what
>>> needs to be done. Not sure why the repr is so confusing, and that
>>> might be something to report upstream.
>>>
>>> ChrisA
>>
>>
>> Thanks.  I must've done it right, using conn.cursor(), 500x.
>> Bleary-eyed from staring at code too long I guess.
> 
> Cool cool! Glad that's working.
> 
>> Now can you get DB2 to accept ; as a SQL statement terminator like the
>> rest of the world?   They call it "An unexpected token"...
>>
> 
> Hmm, I don't know that the execute() method guarantees to allow
> semicolons. Some implementations will strip a trailing semi, but they
> usually won't allow interior ones, because that's a good way to worsen
> SQL injection vulnerabilities. It's entirely possible - and within the
> PEP 249 spec, I believe - for semicolons to be simply rejected.


The default in the DB2 'Command Line Plus' tool is semicolons aren't 
"allowed".


db2 => connect to SAMPLE

db2 => SELECT COUNT(*) FROM STAFF;
SQL0104N  An unexpected token ";" was found following "COUNT(*) FROM STAFF".
Expected tokens may include:  "END-OF-STATEMENT".  SQLSTATE=42601

db2 => SELECT COUNT(*) FROM STAFF
1
-----------
          35
   1 record(s) selected.



But I should've known you can set the terminator value:

https://www.ibm.com/docs/en/db2/11.1?topic=clp-options

Option     :  -t
Description:  This option tells the command line processor to use a
               semicolon (;) as the statement termination character.	
Default    :  OFF


$ db2 -t

turns it on in CommandLinePlus - and the setting applies to the DB-API 
code too.