Correct VBA Syntax for DB2 Command?

1
votes

I'm attempting to connect to DB2 through VBA. I have a connection established through the ODBC provider.

Here's the string of my command text looks like:

strCmd = "INSERT INTO mySchema.myTable (Text) VALUES ('Test')"

When I run this, I get the following runtime error:

[IBM][CLI Driver][DB2/NT64] SQL0204N "MYSCHEMA.MYTABLE" is an undefined name. SQLSTATE=42704

I have validated and verified that the schema and table exist in DB2. I have validated (by using another tool - IBM Data Studio - that the credentials have access and authority to write to this table.

Is my syntax wrong? Is there something I'm missing? If I don't add the "MYSCHEMA." in front of the table name, it presumes I want the "ADMIN" schema, which I don't (it doesn't even exist).

How do I successfully execute an insert command to DB2 LUW?

1
vbadb2
Perhaps the real table name has MiXeD CaSe. By default, Db2 always folds object names to uppercase, unless they are quoted. Try double quoting the schema name and object name. You can look in SYSCAT.TABLES to see the TABSCHEMA and TABNAME in their actual case in the database and your code should match this. - mao
@mao, Thank you. That is EXACTLY what the problem was. I suspect it's going to be easier to use UPPERCASE only when building my tables (the schema is already in all uppercase). I also found, through trial and error, that the same is true for column names in the DB2 table. Lesson learned; either build a function to put double quotes around each DB2 element, or name them using upper case only. Thank you again! - Scott D

1 Answers

1
votes

This is frequently asked.

Db2 automatically folds unquoted object names into uppercase.

This makes programming easier because being forced to quote objects is not so friendly.

It means that "myTable"."mySchema" is a different object than MYTABLE.MYSCHEMA.

So in general it is easier to configure your toolset to not quote object names when creating the objects, and so allow them to be folded to uppercase. It also allows subsequent queries to avoid having to quote table names and column names.

But sometimes you don't have a choice.