topic Re: DB Connection (Microsoft SQL Server) ignoring database context in Talend Studio

DB Connection (Microsoft SQL Server) ignoring database context

KVanselow — Sat, 16 Nov 2024 00:21:26 GMT

Hello Community,

I am in a setup with a Microsoft SQL Server on one side and trying to SELECT and UPDATE / INSERT data based on different databases on this server.

With the tDBConnection I can specifiy the Database but it seems like it is not considering that specification:

I discovered through one job-run the job is writing two tables in two different databases although the database is specified via context variable and the same in both connections
next to that i discovered that within a tDBInput-Statement using inner join the job is jumping between databases and joining the table x from database 1 with table y from database 2. > this I try to prevent now using "USE [db_name]" within my select-statement

But how do I prevent 1.?

I am getting errors when trying to specify the table for the update / insert-action via [db_name].[dbo].[tablename]. " > INVALID OBJECT NAME

Any suggestions why the database-context for the connection is not working and how to specify the correct database for both querys to DBInput and DBOutput?

I am using Talend Open Studio 7.1 at the moment.

Re: DB Connection (Microsoft SQL Server) ignoring database context

Anonymous — Mon, 29 Mar 2021 06:53:02 GMT

You can't select data from different database on the same tDBInput componnent (only one connection established), as a workaround, use two tDBInput components to query data from different Database, and then do join on tMap.

Regards

Shong

Re: DB Connection (Microsoft SQL Server) ignoring database context

David_Beaty — Mon, 29 Mar 2021 06:54:26 GMT

Hi,

I would suggest you create two tDBConnection components, one as the source/select connection and one as the desination/insert connection. That would probably just good practice, but also will help you, in this case, isolate the problem.

Also, remember that in database select statements, you should fully qualify the table name:

SELECT col1, col2,col3

FROM {database}.{schema}.{table}

Thanks

David

Re: DB Connection (Microsoft SQL Server) ignoring database context

KVanselow — Mon, 29 Mar 2021 17:04:54 GMT

Thanks for your feedback, @Shicong Hong and @David Beaty .

There is a misunderstanding though: i do not want to select from two different databases but only from one. And the select is not a problem since I use the statement "USE [db_name] SELECT * FROM [table]" now altough the db_name is already specified in the connection.

BUT there is no option to have "USE *" on tdboutput so it is inserting my values in the wrong database now [e.g. db_name_2] - even if the connection is still on [db_name].

Any suggestions?

Re: DB Connection (Microsoft SQL Server) ignoring database context

gjeremy1617088143 — Tue, 30 Mar 2021 08:15:44 GMT

Hi, is your tdbOutput is relied to the good tDbConnection ? Maybe you'll have to delete the component and recreate a new one relied to your tDbConnection component. Could you send screenshots of your job?

Re: DB Connection (Microsoft SQL Server) ignoring database context

KVanselow — Tue, 30 Mar 2021 10:35:05 GMT

I am providing a screenshot (in german). You see the connection open the db is used to do the update later on. But the table I specify in tDBConnection_1 is not updated through the tDBOutput_2 but rather a different table.

Re: DB Connection (Microsoft SQL Server) ignoring database context

gjeremy1617088143 — Tue, 30 Mar 2021 12:20:00 GMT

ok, is your tDBconnection_1 is correctly set, are you using shared db on your tDBConnection_1 ?

Re: DB Connection (Microsoft SQL Server) ignoring database context

gjeremy1617088143 — Tue, 30 Mar 2021 12:22:28 GMT

cause shared Db can overwrite settings of a tDBConnection.

Re: DB Connection (Microsoft SQL Server) ignoring database context

gjeremy1617088143 — Tue, 30 Mar 2021 12:40:57 GMT

"But the table I specify in tDBConnection_1 " :

you can only specify db on TdbConnection not tables, and in tDBOutput you can specify the table on table field, the db is automaticly recognized by the tDBConnection link.

Re: DB Connection (Microsoft SQL Server) ignoring database context

KVanselow — Tue, 30 Mar 2021 14:03:48 GMT

Hey @guenneguez jeremy , thanks for all your valuable feedback!

Yes, the setup is a shared db.

I am confused, since the DBConnections lets me specify a database as well .. at least this is what I thought it would do ?!

Re: DB Connection (Microsoft SQL Server) ignoring database context

David_Beaty — Tue, 30 Mar 2021 14:26:08 GMT

Hi @Kerstin Vanselow

Yes, the tDBConnection lets you specify the database, as you showed in the screen shot, but once you select the tDBConnection_1 as the connection to use in the tDBOutput_2 component, the only thing you can specify there is the table name.

Suggestions:

Create 2 DB connections, one for the read and one for the write.
Don't use the syntax of "USE [database]" in your selection - this "hides" what is really going on.

Thanks

David

Re: DB Connection (Microsoft SQL Server) ignoring database context

gjeremy1617088143 — Tue, 30 Mar 2021 15:01:20 GMT

could you specify db name and table name please ? and the value of context.db_name ?

Re: DB Connection (Microsoft SQL Server) ignoring database context

KVanselow — Tue, 30 Mar 2021 15:48:01 GMT

But why is it not using this database but rather another one on the same host ? Is this a bug ? I am still confused @David Beaty .

Both databases have the same setup, meaning tables and fields. One is rather the Test-Database [db_name_test] and the other the database with production data [db_name]. But both have the table [Protocol] but when I use the dbconnection on database [db_name] it is inserting the change into [db_name_test].[Protocol]

Re: DB Connection (Microsoft SQL Server) ignoring database context

David_Beaty — Tue, 30 Mar 2021 16:41:35 GMT

Hi,

I suspect then, that the context variable for the database is not getting updated from the test database to the live database when its run.

Thanks

David

Re: DB Connection (Microsoft SQL Server) ignoring database context

gjeremy1617088143 — Tue, 30 Mar 2021 17:49:47 GMT

You have to Check Your context value and when you launch the job, be sure you use the good context, context group have multiple configuration like : dev,preprod, prod.

Re: DB Connection (Microsoft SQL Server) ignoring database context

KVanselow — Wed, 31 Mar 2021 07:06:15 GMT

This is the funny thing, @guenneguez jeremy : my context-variables are fine - the process was working perfectly with three other tables and selects so far until now, when I introduced a new table on the SQL Server ...

So the context is definitely correct.

Re: DB Connection (Microsoft SQL Server) ignoring database context

gjeremy1617088143 — Wed, 31 Mar 2021 07:44:09 GMT

Hi KVanselow, you can do one thing to ensure you have the good value in your context :

in your job put a tJava component with the folowing syntax :

System.out.println(context.db_host);

System.out.println(context.db_name);

and you'll see in the console if you really use the good values.

Re: DB Connection (Microsoft SQL Server) ignoring database context

KVanselow — Wed, 31 Mar 2021 17:37:12 GMT

Finally I got the reason: I was working with a subjob and forgot the small checkbox: "transmit whole context". Aaargh.