JDBC Adapter: Database table field names contain spaces

[Former Member]

Hi,

I need to read a database table (SQL Server database) using PI JDBC Adapter. Table field names contain space. For example: "Customer Name". If I create data type in ESR, PI doesn't allow to put space in between field names. I there any way to handle this? Can I use underscore in place of space? Will it work? Or any other solution for this?

Thanks,

Netrey

[Former Member]

Hello!

Have you tried something like "SELECT 'customer name' AS cName FROM..." ? This should create an alias which you could use on the data type.

Otherwise, you could as well stick with a T-SQL procedure.

BR,

Lucas Santos

[Former Member]

Hi Lucas Santos,

Can you please elaborate more on T-SQL procedure?

BR,

Netrey

[Former Member]

Hello Netrey!

T-SQL is the Stored Procedure language used in the MS SQL databases. With this, you could from PI call the stored procedure and it would return the data after processing it on the database. You can check some code examples and usage on http://msdn.microsoft.com/en-us/library/ms187926.aspx .

However, have you tried using the "AS" SQL command as I've mentioned before? This should remove the necessity of declaring the data type with space, and should allow you use the alias name instead. Let me know if this works.

BR,

Lucas Santos

[baskar_gopalakrishnan2]

What Lucas referring is very similar to the terminology called SQL Alias.  Please take help from DBA. You can use Alias for both column names as well table names. You might want to try without space as Alias for all the columns you create in ESR and see if that helps. Otherwise you need to go for stored procedure, if the database fields are not feasible to change.

Also if you use jdbc receiver then you can go for the statement6 structure on the request side and use alias sql as query.  The response structure provide alias name and see if that helps.

You can see this link

http://help.sap.com/saphelp_nw04/helpdata/en/2e/96fd3f2d14e869e10000000a155106/content.htm

http://www.w3schools.com/sql/sql_alias.asp

[rajasekhar_reddy14]

if you use underscore it will not work, ask DB team to change field name,request them to avoid spaces.

[Former Member]

Hi,

That's the first option I had thought of, but that's not feasible because these tables are quite old and hence being used at multiple other places. So, how can still work with field names with spaces?

Regards,

Netrey

[rajasekhar_reddy14]

What exactly is your requirement? JDBC sender or receiver?

if it is receiver then use stored procedure . if it is lookup or Sender not possible.