jdbc adapter, problem with using adapter service templates like insert/select

, ,
1

hi all,

we are using wm9.6 and jdbc adapter 6.5 (no fixes)
we are using ms sql server 2012

we have tricky situation, where our DB schema name is SQL keyword (like order), so insert adapter service fails when executed

insert into order.tbl.order.xxx() values ();

in the above statement, how to make adapter service (insert template) to encapsulate the order into like

insert into [order].tbl.order.xxx() values ();

any help is appreciated

thanks and regards
ajay kumar kasam

2

Could you share some screenshots including Table tab in adapter service, and execution error?

3

thanks for the response.

but i can’t share the logs. i mentioned all the details needed in the previous email. other information in logs is specific to my client and project.

my query is simple, while using the jdbc adapter service (insert or select template), when choosing the schema named as one the sql keyword, how to wrap the schema name inside so that sql statement processor can escape the sql keywords in and proceed with execution

alternatively i can go ahead and use the custom sql template, but i would like to know how can select template be used in my scenario.

4

Hi Ajay,

It seems template doesn’t support the way you mentioned. The code below would be used to generate the SQL, but don’t see any escape char.


public String getUserTableName(int index, String locale)
  {
    String defaultCatalog = Label.defaultCatalog.toString(locale);
    String defaultSchema = Label.defaultSchema.toString(locale);
    if (this.schemaNames.length < index) {
      return "";
    }
    StringBuffer name = new StringBuffer();
    if (!this.catalogNames[index].equals(defaultCatalog))
    {
      if (this.schemaNames[index].equals(defaultSchema))
      {
        if (!this.realSchemaNames[index].equals(""))
        {
          name.append(this.catalogNames[index]);
          name.append(".");
          name.append(this.realSchemaNames[index]);
          name.append(".");
        }
      }
      else
      {
        name.append(this.catalogNames[index]);
        name.append(".");
        name.append(this.schemaNames[index]);
        name.append(".");
      }
    }
    else if (!this.schemaNames[index].equals(defaultSchema))
    {
      name.append(this.schemaNames[index]);
      name.append(".");
    }
    name.append(ConnectionInfo.sqlQuote(this.tableNames[index]));
    return name.toString();
  }
5

thank you.

anybody else can suggest, how to overcome this situation

6

Hi Ajay,

which driver (version) for MS SQL are you using?

The latest driver I know of is sqljdbc_4.1.5605.100 (available from Microsoft/MSDN)

Usually the table selector in the templates has an option “current.catalolg”.“current schema”, pointing to the default schema of the user configured in the jdbc connection.

When selecting this, there should be no need to escape the schema name for distinguishing from table names or other key words.

As there have been quite some issues dealing with MS SQL Databases, please consider applying JDBC_6.5_Fix51 or upgrading to JDBC Adapter 9.0 (and apply Fix5 to it).

Another option might be to consider renaming the schema.

What is the exact error message when the service fails?

Regards,
Holger

7

I agree with Holger on below option which you don’t need to change even when you are migrating code from one environment to other.

Usually the table selector in the templates has an option “current.catalolg”.“current schema”, pointing to the default schema of the user configured in the jdbc connection.

You better try above option and let us know if you see any challenges.

Thanks,