Naming Conventions for JDBC

For general information, see SAS Names and Support for DBMS Names.

Because JDBC is an application programming interface (API) rather than a database, table names and column names are determined at run time. Most SAS names can be up to 32 characters long. SAS/ACCESS Interface to JDBC supports table names and column names that contain up to 32 characters. If DBMS column names are longer than 32 characters, SAS truncates them to 32 characters. If truncating a column name would result in identical names, SAS generates a unique name by replacing the last character with a number. DBMS table names must be 32 characters. SAS does not truncate a longer name. If you already have a table name that is greater than 32 characters, it is recommended that you create a table view.

The PRESERVE_TAB_NAMES= and PRESERVE_COL_NAMES= options determine how this interface handles case sensitivity, spaces, and special characters. (For information about these options, see LIBNAME Statement for Relational Databases.)

This example specifies PostgreSQL as the DBMS.

libname x jdbc driverclass="org.postgresql.Driver"
   URL="jdbc:postgresql://<myserver>:<myport>/<postgres-database>" user=myuser 
   password="mypwd1" 
   classpath="C:\Progam Files\SASHome\AccessClients\9.4\DataDrivers\jdbc\postgresql"
   preserve_col_names=no;

data x.a;
   xAxis=1;
   yAxis=2;
run;

PostgreSQL stores identifiers in lowercase unless the identifier is quoted. This example would therefore produce a PostgreSQL table named a with columns named xaxis and yaxis.

If PRESERVE_COL_NAMES=YES, then PROC SQL puts identifiers in quotation marks and names are preserved. When you use quotation marks, PostgreSQL becomes case-sensitive.