Connection.set_current_schema() fails

[davis-junior]

Using ibm-db 2.0.5.9 from IBM i repo.

Connection.set_current_schema() fails with exception:

Statement Execute Failed: Variable HVR0001 not character, UCS-2 graphic, or UTF-16 graphic. SQLSTATE=42618 SQLCODE=-5024

Internally, it executes the prepared statement "set current schema = ?" with proper parameter, and that's where the exception is thrown. A workaround is to concatenate directly (improper).

Here's a few tests, just change "schema" to an existing one on the system. The first three fail with the above exception.

import unittest
import ibm_db
import ibm_db_dbi

class IBMDBTest(unittest.TestCase):
    schema = 'put_schema_here'

    def test_set_current_schema(self):
        '''Tests Connection.set_current_schema()
        '''
        conn = ibm_db_dbi.connect('DATABASE=*LOCAL')
        conn.set_current_schema(self.schema)
        self.assertEqual(conn.get_current_schema(), self.schema, 'Schema should be ' + self.schema)
        conn.close()

    def test_set_current_schema2(self):
        '''Tests Cursor execution of proper prepared statement
        '''
        conn = ibm_db_dbi.connect('DATABASE=*LOCAL')
        curs = conn.cursor()
        curs.execute("set current schema = ?", (self.schema,))
        self.assertEqual(conn.get_current_schema(), self.schema, 'Schema should be ' + self.schema)
        conn.close()

    def test_set_current_schema3(self):
        '''Tests direct driver execution using proper prepared statement form
        '''
        conn = ibm_db_dbi.connect('DATABASE=*LOCAL')
        stmt = ibm_db.prepare(conn.conn_handler, "set current schema = ?")
        ibm_db.execute(stmt, (self.schema,))
        self.assertEqual(conn.get_current_schema(), self.schema, 'Schema should be ' + self.schema)
        conn.close()

    def test_set_current_schema4(self):
        '''Tests direct driver execution using concatenated parameters
        '''
        conn = ibm_db_dbi.connect('DATABASE=*LOCAL')
        stmt = ibm_db.prepare(conn.conn_handler, "set current schema = '" + self.schema + "'")
        ibm_db.execute(stmt)
        self.assertEqual(conn.get_current_schema(), self.schema, 'Schema should be ' + self.schema)
        conn.close()

if __name__ == "__main__":
    unittest.main()

[jkyeung]

This issue still applies as of version 2.0.5.12-0.

[kadler]

The extended message text for SQLCODE -5024 is this:

 Message ID . . . . . . . . . :   SQL5024
 Message file . . . . . . . . :   QSQLMSG
   Library  . . . . . . . . . :     QSYS

 Message . . . . :   Variable &1 not character, UCS-2 graphic, or UTF-16
   graphic.
 Cause . . . . . :   Host variable or global variable &1 is not defined as
   character, UCS-2 graphic, or UTF-16 graphic. Host variables in a precompiled
   program or REXX procedure or global variables must be character, UCS-2
   graphic, or UTF-16 graphic if used as:
     -- The statement string in a PREPARE or EXECUTE IMMEDIATE statement.
     -- The table name in a DESCRIBE TABLE statement.
     -- The procedure name in a CALL statement.
     -- The server name, authorization name, or password in a CONNECT, SET
   CONNECTION, RELEASE, DISCONNECT, SET ENCRYPTION PASSWORD, or SET SESSION
   AUTHORIZATION statement.
     -- The special register value in a SET special register statement such as
        SET PATH, SET SCHEMA, or SET CURRENT DECFLOAT ROUNDING MODE.         
     -- The SQLSTATE value or signal information value in a SIGNAL statement.
     -- A descriptor name.
 Recovery  . . . :   Specify a variable that is character, UCS-2 graphic, or   
   UTF-16 graphic. Try the request again.

For some reason, the database is not handling the string properly when passed as UTF-8. It does seem to work if I hack it to pass as SQL_C_WCHAR (UTF-16) as noted from the message help, but that would be difficult to handle for just SET statements...

I did find that there is a connection attribute that does exactly what is wanted and works: SQL_ATTR_DBC_DEFAULT_LIB. Mapping that to SQL_ATTR_CURRENT_SCHEMA allows the existing code to work properly, though explicitly calling SET SCHEMA via cursor.execute() still doesn't.

Let me know if the set_current_schema() fix is enough and I can fix that up.