 |
IDS Forum
Re: Can Informix access Sql Server table?
Posted By: Art Kagel
Date: Wednesday, 6 March 2013, at 8:53 a.m.
In Response To: Re: Can Informix access Sql Server table? (Art Kagel)
Oops forgot, because SQL Server and Informix use different communications
protocols and therefore different libraries, I suspect that you will have
to go the separate tasks route.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Wed, Mar 6, 2013 at 8:47 AM, Art Kagel <art.kagel@gmail.com> wrote:
> OK, to maintain the two ODBC connections simultaneously he will have to
> spawn separate threads before connecting and connect to SQL Server in one
> thread and Informix in the other passing data and control information
> between the threads using memory and mutexes. If that doesn't work, he
> will have to use separate processes, one for each database, communicating
> via shared memory and semaphores or global mutexes.
>
> Either way, I think that he will only need to use CDC on the SQL Server
> side.
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> Blog: http://informix-myview.blogspot.com/
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
> other organization with which I am associated either explicitly,
> implicitly, or by inference. Neither do those opinions reflect those of
> other individuals affiliated with any entity with which I am affiliated nor
> those of the entities themselves.
>
> On Wed, Mar 6, 2013 at 7:57 AM, John Adamski <adamski@graceland.edu>
> wrote:
>
> > >From what I understand the developer needs from Informix side to read a
> > record
> > on the SQL Server table then update Informix table(s) based on the SQL
> > server
> > record and then finally update the SQL server record it was processed.
> He's
> > also is trying this from the SQL server side and has the sql that works,
> > but
> > only if he manually runs the sql. If he puts the sql in a job or trigger
> he
> > gets errors as the ODBC connection to Informix is not opened. At least
> > that is
> > my understanding of the situation/problem.
> >
> > John
> >
> > -----Original Message-----
> > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> Art
> > Kagel
> > Sent: Tuesday, March 05, 2013 2:21 PM
> > To: ids@iiug.org
> > Subject: Re: Can Informix access Sql Server table? [29710]
> >
> > If you only need to go from SQL Server to Informix and not
> bi-directional,
> > then you don't need the Informix CDC API, only the SQL Server one. The
> > inserts, updates, and deletes to the Informix copy will be 'normal' SQL
> > layer
> > statements. You only need the CDC on the MS side to capture the changes.
> >
> > Art
> >
> > Art S. Kagel
> > Advanced DataTools (www.advancedatatools.com)
> > Blog: http://informix-myview.blogspot.com/
> >
> > Disclaimer: Please keep in mind that my own opinions are my own opinions
> > and
> > do not reflect on my employer, Advanced DataTools, the IIUG, nor any
> other
> > organization with which I am associated either explicitly, implicitly, or
> > by
> > inference. Neither do those opinions reflect those of other individuals
> > affiliated with any entity with which I am affiliated nor those of the
> > entities themselves.
> >
> > On Tue, Mar 5, 2013 at 2:59 PM, John Adamski <adamski@graceland.edu>
> > wrote:
> >
> > > Hmmmm, looks like you need to run syscdcv1.sql to generate a database
> > > to use CDC API, which if I'm reading right comes with Informix since
> > > 11.50. It looks like our ERP did not provide that with the Informix
> > > installer they sent us.
> > >
> > > Oh, yippee I get to go beg them for it and have them turn me down like
> > > they did with the data blades.
> > >
> > > It does seem to be exactly what the developer needs.
> > >
> > > Thanks everyone for your suggestions.
> > >
> > > John
> > >
> > > -----Original Message-----
> > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> > > Art Kagel
> > > Sent: Tuesday, March 05, 2013 11:24 AM
> > > To: ids@iiug.org
> > > Subject: Re: Can Informix access Sql Server table? [29707]
> > >
> > > Sounds like a perfect application of CDC and the API is not complex.
> > > You create a stand-alone app which uses the API to connect to the SQL
> > > Server database, register the tables and operations it wants to be
> > > notified about with a callback function for each and wait for
> > > messages. It would trap for inserts, updates, and/or deletes take the
> > > data it gets and either add it directly to the Informix side with a
> > > direct connection, hand it off to a separate thread connected to
> > > Informix, or pipe the data to a separate app connected to Informix to
> > handle
> > that side.
> > >
> > > Art
> > >
> > > Art S. Kagel
> > > Advanced DataTools (www.advancedatatools.com)
> > > Blog: http://informix-myview.blogspot.com/
> > >
> > > Disclaimer: Please keep in mind that my own opinions are my own
> > > opinions and do not reflect on my employer, Advanced DataTools, the
> > > IIUG, nor any other organization with which I am associated either
> > > explicitly, implicitly, or by inference. Neither do those opinions
> > > reflect those of other individuals affiliated with any entity with
> > > which I am affiliated nor those of the entities themselves.
> > >
> > > On Tue, Mar 5, 2013 at 12:11 PM, John Adamski <adamski@graceland.edu>
> > > wrote:
> > >
> > > > Art,
> > > >
> > > > Thanks for this info will research and also pass on to the developer.
> > > >
> > > > He is now trying to do what he needs from the SQL server side but is
> > > > getting and error which I'm guessing has to do with the way SQL
> > > > server triggers work and it is not 'opening' a connection to
> Informix.
> > > >
> > > > Here is the developers last email to me.
> > > >
> > > > Since it sounds like going from Informix to SQL is going to take a
> > > > while and may include some costly purchases I am trying to go the
> > > > other way SQL to Informix.
> > > >
> > > > Basically just using a simple trigger that takes any rows inserted
> > > > into the SQL table and writing them to a table in Informix.
> > > >
> > > > If I run this code manually with a where clause it works fine insert
> > > > into CX.cars.informix.cus_cf_answers (SubmissionID, ItemID,
> > > > AnswerValue) select SubmissionID, ItemID, AnswerValue from
> > > > CUS_CF_Answers where SubmissionID =
> > > > '64166500-0CD4-40FF-8989-046263B6E83C'
> > > >
> > > > but as a trigger using the INSERTED table I get the error below.
> > > >
> > > > The trigger code:
> > > > CREATE trigger [dbo].[i_cus_cf_answers] ON [dbo].[CUS_CF_Answers]
> > > > AFTER INSERT AS --do not return any results SET NOCOUNT ON BEGIN
> > > > insert into CX.cars.informix.cus_cf_answers
> > > >
> > > > (SubmissionID, ItemID, AnswerValue) select SubmissionID, ItemID,
> > > > AnswerValue from INSERTED END
> > > >
> > > > Here is the error I get when I insert into the SQL table.
> > > >
> > > > The operation could not be performed because OLE DB provider
> > "Ifxoledbc"
> > > > for
> > > > linked server "CX" was unable to begin a distributed transaction.
> > > >
> > > > Any ideas what we would need to change to fix this error?
> > > >
> > > > -----Original Message-----
> > > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf
> > > > Of Art Kagel
> > > > Sent: Tuesday, March 05, 2013 8:07 AM
> > > > To: ids@iiug.org
> > > > Subject: Re: Can Informix access Sql Server table? [29695]
> > > >
> > > > There is another option. You can use CDC (Change Data Capture) to
> > > > replicate the SQL Server table(s) to Informix either by coding it
> > > > yourself
> > > > - the CDC API is an open one and supported by both SQL Server and
> > > > Informix so you could make it bi-directional - or using a 3rd party
> > > > tool - DbMoto comes to mind or IBM's CDC product. This will be less
> > > > expensive than EGM.
> > > >
> > > > Art
> > > >
> > > > Art S. Kagel
> > > > Advanced DataTools (www.advancedatatools.com)
> > > > Blog: http://informix-myview.blogspot.com/
> > > >
> > > > Disclaimer: Please keep in mind that my own opinions are my own
> > > > opinions and do not reflect on my employer, Advanced DataTools, the
> > > > IIUG, nor any other organization with which I am associated either
> > > > explicitly, implicitly, or by inference. Neither do those opinions
> > > > reflect those of other individuals affiliated with any entity with
> > > > which I am affiliated nor those of the entities themselves.
> > > >
> > > > On Tue, Mar 5, 2013 at 8:51 AM, John Adamski <adamski@graceland.edu>
> > > > wrote:
> > > >
> > > > > Oh, pooh, we looked at EGM for another project about 5 years ago
> > > > > and was out of our price range. That was what I was thinking that
> > > > > was not going to be able to help the developer.
> > > > >
> > > > > Thanks everyone, I now go get to rain on the developer's parade.
> > > > > :-)
> > > > >
> > > > > John
> > > > >
> > > > > -----Original Message-----
> > > > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf
> > > > > Of Art Kagel
> > > > > Sent: Monday, March 04, 2013 11:12 AM
> > > > > To: ids@iiug.org
> > > > > Subject: Re: Can Informix access Sql Server table? [29674]
> > > > >
> > > > > The only way you can do that would be to purchase the Infomrix
> > > > > Enterprise Gateway product.
> > > > >
> > > > > Art
> > > > >
> > > > > Art S. Kagel
> > > > > Advanced DataTools (www.advancedatatools.com)
> > > > > Blog: http://informix-myview.blogspot.com/
> > > > >
> > > > > Disclaimer: Please keep in mind that my own opinions are my own
> > > > > opinions and do not reflect on my employer, Advanced DataTools,
> > > > > the IIUG, nor any other organization with which I am associated
> > > > > either explicitly, implicitly, or by inference. Neither do those
> > > > > opinions reflect those of other individuals affiliated with any
> > > > > entity with which I am affiliated nor those of the entities
> > themselves.
> > > > >
> > > > > On Mon, Mar 4, 2013 at 11:45 AM, John Adamski
> > > > > <adamski@graceland.edu>
> > > > > wrote:
> > > > >
> > > > > > Informix:
> > > > > > HPUX 11.31 (iiiv3) on HP BL870c IA server IDS 11.70.FC4 SQL
> Server:
> > > > > > W2k8 r2 SP1 on Vsphere 5 cluster SQL Server 2008 r2
> > > > > >
> > > > > > One of our developers has asked me to help him setup "whatever
> > > > > > is
> > > > needed"
> > > > > > so
> > > > > > from Informix he can access data on a SQL Server table. We
> > > > > > already have the other direction (SQL server accessing Informix
> > > > > > data) setup and working using odbc on the sql server.
> > > > > >
> > > > > > I'm still clarifying with the developer if he needs only read
> > > > > > access or read/write access to the SQL server table from
> Informix.
> > > > > >
> > > > > > I don't know if this is possible, is it? If so how do I connect
> > > > > > the two databases? What tools/software do I need?
> > > > > >
> > > > > > A SQL server friend said I might be able to use SQL Server
> > > > > > Integration Service
> > > > > > (SSIS) but he has never used it so wasn't sure. I have never
> > > > > > heard of it so know even less. :-)
> > > > > >
> > > > > > John Adamski, Sr. Network Specialist Graceland University, 1
> > > > > > University Place, Lamoni, IA 50140 adamski@graceland.edu
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > > >
> > >
> > >
> > >
> >
> >
> >
>
> *******************************************************************************
> > > > > > Forum Note: Use "Reply" to post a response in the discussion
> forum.
> > > > > >
> > > > > >
> > > > >
> > > > > --bcaec54b48d0489c3c04d71c745b
> > > > >
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > > >
> > >
> > >
> > >
> >
> >
> >
>
> *******************************************************************************
> > > > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > > > >
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > > >
> > >
> > >
> > >
> >
> >
> >
>
> *******************************************************************************
> > > > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > > > >
> > > > >
> > > >
> > > > --f46d042d051c91e3f304d72df251
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> > >
> >
> >
> >
>
> *******************************************************************************
> > > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> > >
> >
> >
> >
>
> *******************************************************************************
> > > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > > >
> > > >
> > >
> > > --bcaec554d232b2576304d730bdb6
> > >
> > >
> > >
> > >
> >
> >
> >
>
> *******************************************************************************
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> > >
> > >
> >
> >
> >
>
> *******************************************************************************
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> > --f46d0408395d45bd3204d733359f
> >
> >
> >
> >
>
> *******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
> >
> >
>
> *******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --f46d042d051c7f7e1404d741d237
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--bcaec554dbc82f7be104d741e82a
Messages In This Thread
IDS Forum is maintained by Administrator with WebBBS 5.12.
|
|
