Database Table Title Names Greater than 30 Characters (Magic xpi 4.9)
Why can't the Magic xpi Studio connect to database tables whose title names are greater than 30 characters?
When you define a Database resource, you can either select the relevant table in the resource definition phase, or via the Database Wizard, or by typing the SQL statement manually.
If you select the table in the resource definition phase, and the table/column title is more than 30 characters in length, you will not be able to use it later.
|
| Table selection in the resource definition phase can enhance performance during design time. For example, if your Database includes many tables or columns, and you do not select the table in the resource definition phase, each step configuration that accesses that Database will take a significant amount of time. This situation can be avoided if you select the table in the resource definition phase. |
ODBC Databases
If you are using an ODBC database and the table name is larger than 30 characters, the following error will appear in the Resource screen:
"Current logged user does not have privileges on table xxxx columns. Selection is cancelled."
MSSQL Databases
If you are using an MSSQL database and the table name is larger than 30 characters, the following error will appear in the Data Mapper window:
"Information cannot be retrieved for database table of xxx. Check that you have the required permission or that the SQL statement is correct."
For both databases, there are two possible options for working around this issue:
-
Do not select the table in the resource definition phase. You should only do this in the Database Wizard phase, when configuring the step, or manually when typing the SQL statement.
-
You can define a view with a shorter name inside the Database that points to the large title name table. You should do this for each large title name table.
For example:
Create longnames for tables: (f1 int , f1234567890123456789012345678901234567890 char(10))
Create shortnames for views: (f1 , f2 )
AS
Select f1,f1234567890123456789012345678901234567890 from longnames
You should use this view instead of the original one. This type of view supports all Data Manipulation Languages (DMLs).
