Options
DatabaseExampleSetWriter writing to schema other than default
Maven
I am trying to write an example set using the DatabaseExampleSetWriter node to a particular non-default schema in a MS SQL Server 2005 database. I have the appropriate JDBC driver, and can SELECT data out of the database with no problem. Furthermore, I can successfully write an example set to a table specifed by the "table_name" parameter. However, this only works for writing tables to the default schema (in SQL Server, the schema is "dbo").
I have several schemas defined in SQL Server for storing data related to different projects, and want to be able to specify both the schema and table name for RM to write to. E.g instead of "dbo.MyTable" I want to write to "MySchema.MyTable". In SQL I would just specify the schema name before the table name, separated by a period.
CREATE Table [MyTable] (by default, created in schema="dbo")
CREATE TABLE [MySchema].[MyTable] (created in schema="MySchema")
However, if I set table_name equal to "MyTable.MySchema" in DatabaseExampleSetWriter, the table is still created in the "dbo" schema, as the somewhat awkwardly named [dbo].[MySchema.MyTable]
Note that the square brackets act as a quoting character in SQL Server, and are optional in most situations. The only time they are requested is if you use a character in a table name that would ordinarily be invalid, such as the period between schema name and table name. RM or JDBC seems to be interpreting the entire value of table_name as a value to be quoted as just a table name, rather than accepting a fully qualified SQL table name (much like the difference between relative paths and absolute paths in a file system).
I tried quoting the schema and table name separately, setting table_name = "[MySchema].[MyTable]", but RM returned the following error:
Error in DatabaseExampleSetWriter
Database error occurred: Incorrect syntax near 'INTEGER'
The user I am connecting as has permissions to write to this schema, so I do not think this is security-related, but instead due to the interaction between RM and JDBC.
Is there a way to have RM write to particular schema, or to accept a fully qualified table name to write back to a SQL database?
Thanks,
Keith
I have several schemas defined in SQL Server for storing data related to different projects, and want to be able to specify both the schema and table name for RM to write to. E.g instead of "dbo.MyTable" I want to write to "MySchema.MyTable". In SQL I would just specify the schema name before the table name, separated by a period.
CREATE Table [MyTable] (by default, created in schema="dbo")
CREATE TABLE [MySchema].[MyTable] (created in schema="MySchema")
However, if I set table_name equal to "MyTable.MySchema" in DatabaseExampleSetWriter, the table is still created in the "dbo" schema, as the somewhat awkwardly named [dbo].[MySchema.MyTable]
Note that the square brackets act as a quoting character in SQL Server, and are optional in most situations. The only time they are requested is if you use a character in a table name that would ordinarily be invalid, such as the period between schema name and table name. RM or JDBC seems to be interpreting the entire value of table_name as a value to be quoted as just a table name, rather than accepting a fully qualified SQL table name (much like the difference between relative paths and absolute paths in a file system).
I tried quoting the schema and table name separately, setting table_name = "[MySchema].[MyTable]", but RM returned the following error:
Error in DatabaseExampleSetWriter
Database error occurred: Incorrect syntax near 'INTEGER'
The user I am connecting as has permissions to write to this schema, so I do not think this is security-related, but instead due to the interaction between RM and JDBC.
Is there a way to have RM write to particular schema, or to accept a fully qualified table name to write back to a SQL database?
Thanks,
Keith
0
Answers
I think writing Schema.Table does not work, because RM quotes the hole string again, so that . will be interpreted as a character by the database. I think we might add a new parameter for selecting the schema, rather than internally trying to interpret the name itself. I will see what we can do, but we have to evaluate this on several databases before, because the "standard" of SQL is interpreted more different than one would believe...
Greetings,
Sebastian