Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]


Groups > comp.lang.clipper.visual-objects > #1966 > unrolled thread

Joining MSSQL and Firebird tables

Started by"andrej Terkaj" <aterkaj@task.si>
First post2011-09-07 10:38 +0200
Last post2011-09-09 11:57 +0200
Articles 14 — 3 participants

Back to article view | Back to comp.lang.clipper.visual-objects


Contents

  Joining MSSQL and Firebird tables "andrej Terkaj" <aterkaj@task.si> - 2011-09-07 10:38 +0200
    Re: Joining MSSQL and Firebird tables "Willie Moore" <williem@wmconsulting.com> - 2011-09-07 07:36 -0500
      Re: Joining MSSQL and Firebird tables "andrej Terkaj" <aterkaj@task.si> - 2011-09-07 16:25 +0200
        Re: Joining MSSQL and Firebird tables "Willie Moore" <williem@wmconsulting.com> - 2011-09-07 10:45 -0500
    Re: Joining MSSQL and Firebird tables E®!k \\/!$$E® <nospam@nospam.url> - 2011-09-07 18:23 +0200
      Re: Joining MSSQL and Firebird tables "andrej Terkaj" <aterkaj@task.si> - 2011-09-08 10:20 +0200
        Re: Joining MSSQL and Firebird tables "Willie Moore" <williem@wmconsulting.com> - 2011-09-08 07:17 -0500
          Re: Joining MSSQL and Firebird tables "andrej Terkaj" <aterkaj@task.si> - 2011-09-08 16:31 +0200
            Re: Joining MSSQL and Firebird tables "Willie Moore" <williem@wmconsulting.com> - 2011-09-08 10:30 -0500
              Re: Joining MSSQL and Firebird tables "andrej Terkaj" <aterkaj@task.si> - 2011-09-09 12:25 +0200
                Re: Joining MSSQL and Firebird tables "Willie Moore" <williem@wmconsulting.com> - 2011-09-09 08:26 -0500
                  Re: Joining MSSQL and Firebird tables "andrej Terkaj" <aterkaj@task.si> - 2011-09-13 16:27 +0200
        Re: Joining MSSQL and Firebird tables E®!k \\/!$$E® <nospam@nospam.url> - 2011-09-08 19:30 +0200
          Re: Joining MSSQL and Firebird tables "andrej Terkaj" <aterkaj@task.si> - 2011-09-09 11:57 +0200

#1966 — Joining MSSQL and Firebird tables

From"andrej Terkaj" <aterkaj@task.si>
Date2011-09-07 10:38 +0200
SubjectJoining MSSQL and Firebird tables
Message-ID<j47bm1$as3$1@news.siol.net>
I am trying to join (relate) two different SQL tables MS SQL one (master) 
and Firebird /Interbase/MySQL as child one using Vo2Ado 2.8 -> Adoserver
Connections to a single tables/databases are not a problem. I tried also a 
MS function known as "linked server". No success. Debuging a VO code it 
always
stops me when I try to initialize Adoserver class. Any ideas ?

TIA
Andrej Terkaj 


[toc] | [next] | [standalone]


#1968

From"Willie Moore" <williem@wmconsulting.com>
Date2011-09-07 07:36 -0500
Message-ID<j47ofk$9hl$1@speranza.aioe.org>
In reply to#1966
Andrej,

You can link different databases that use the same connection but I don’t 
believe that you can link 2 databases that use different connections.

Regards,
Willie

"andrej Terkaj"  wrote in message news:j47bm1$as3$1@news.siol.net...

I am trying to join (relate) two different SQL tables MS SQL one (master)
and Firebird /Interbase/MySQL as child one using Vo2Ado 2.8 -> Adoserver
Connections to a single tables/databases are not a problem. I tried also a
MS function known as "linked server". No success. Debuging a VO code it
always
stops me when I try to initialize Adoserver class. Any ideas ?

TIA
Andrej Terkaj

[toc] | [prev] | [next] | [standalone]


#1969

From"andrej Terkaj" <aterkaj@task.si>
Date2011-09-07 16:25 +0200
Message-ID<j48004$m46$1@news.siol.net>
In reply to#1968
Thanks for a reply, Willie.
I am still curious, because when I try with a query statement inside 
enterprise manager (Studio Management) connecting MS table and a view that 
is an object in a "linked server" - Firebird it works. The same query 
statement doesn't work with AdoServer. Using "linked server" a connection is 
probably only one.

Regards
Andrej

"Willie Moore" <williem@wmconsulting.com> wrote in message 
news:j47ofk$9hl$1@speranza.aioe.org...
> Andrej,
>
> You can link different databases that use the same connection but I don't 
> believe that you can link 2 databases that use different connections.
>
> Regards,
> Willie
>
> "andrej Terkaj"  wrote in message news:j47bm1$as3$1@news.siol.net...
>
> I am trying to join (relate) two different SQL tables MS SQL one (master)
> and Firebird /Interbase/MySQL as child one using Vo2Ado 2.8 -> Adoserver
> Connections to a single tables/databases are not a problem. I tried also a
> MS function known as "linked server". No success. Debuging a VO code it
> always
> stops me when I try to initialize Adoserver class. Any ideas ?
>
> TIA
> Andrej Terkaj
>
>
> 


[toc] | [prev] | [next] | [standalone]


#1970

From"Willie Moore" <williem@wmconsulting.com>
Date2011-09-07 10:45 -0500
Message-ID<j483hn$8qk$1@speranza.aioe.org>
In reply to#1969
Andrej,

If it is working from inside the studio manager,  you might try the 
statement in an ADORecordset instead of an ADOServer and see if that works. 
If it doesn’t work, what is the ADO error

Regards,
Willie

"andrej Terkaj"  wrote in message news:j48004$m46$1@news.siol.net...

Thanks for a reply, Willie.
I am still curious, because when I try with a query statement inside
enterprise manager (Studio Management) connecting MS table and a view that
is an object in a "linked server" - Firebird it works. The same query
statement doesn't work with AdoServer. Using "linked server" a connection is
probably only one.

Regards
Andrej

"Willie Moore" <williem@wmconsulting.com> wrote in message
news:j47ofk$9hl$1@speranza.aioe.org...
> Andrej,
>
> You can link different databases that use the same connection but I don't 
> believe that you can link 2 databases that use different connections.
>
> Regards,
> Willie
>
> "andrej Terkaj"  wrote in message news:j47bm1$as3$1@news.siol.net...
>
> I am trying to join (relate) two different SQL tables MS SQL one (master)
> and Firebird /Interbase/MySQL as child one using Vo2Ado 2.8 -> Adoserver
> Connections to a single tables/databases are not a problem. I tried also a
> MS function known as "linked server". No success. Debuging a VO code it
> always
> stops me when I try to initialize Adoserver class. Any ideas ?
>
> TIA
> Andrej Terkaj
>
>
>

[toc] | [prev] | [next] | [standalone]


#1971

FromE®!k \\/!$$E® <nospam@nospam.url>
Date2011-09-07 18:23 +0200
Message-ID<9cpk4jF86pU1@mid.individual.net>
In reply to#1966
Andrej,

If there is a ADO.NET provider available, you could try to use the linked 
server facilities from MS SqlServer.
Open Management Studio and see under Server Objects - Linked Servers.

Never done this, but I am pretty sure this works as it can be done with 
MySql and Oracle as well.
hth

Erik



"andrej Terkaj"  schreef in bericht news:j47bm1$as3$1@news.siol.net...

I am trying to join (relate) two different SQL tables MS SQL one (master)
and Firebird /Interbase/MySQL as child one using Vo2Ado 2.8 -> Adoserver
Connections to a single tables/databases are not a problem. I tried also a
MS function known as "linked server". No success. Debuging a VO code it
always
stops me when I try to initialize Adoserver class. Any ideas ?

TIA
Andrej Terkaj

[toc] | [prev] | [next] | [standalone]


#1973

From"andrej Terkaj" <aterkaj@task.si>
Date2011-09-08 10:20 +0200
Message-ID<j49v0a$nsd$1@news.siol.net>
In reply to#1971
Hi, Erik !

As I mentioned above my problem can be solved inside a Studio Management. 
Yes, you are right I used a linked server with a IBProvider (I do not know 
if it is an ADO.NET driver, probably no).
But I would like to have a problem solved inside a VO code --> using Vo2Ado 
classes. A child table is a large table with aprox. half million records of 
customers.
Any ideas are welcome.

Andrej


"EŽ!k \/!$$EŽ" <nospam@nospam.url> wrote in message 
news:9cpk4jF86pU1@mid.individual.net...
> Andrej,
>
> If there is a ADO.NET provider available, you could try to use the linked 
> server facilities from MS SqlServer.
> Open Management Studio and see under Server Objects - Linked Servers.
>
> Never done this, but I am pretty sure this works as it can be done with 
> MySql and Oracle as well.
> hth
>
> Erik
>
>
>
> "andrej Terkaj"  schreef in bericht news:j47bm1$as3$1@news.siol.net...
>
> I am trying to join (relate) two different SQL tables MS SQL one (master)
> and Firebird /Interbase/MySQL as child one using Vo2Ado 2.8 -> Adoserver
> Connections to a single tables/databases are not a problem. I tried also a
> MS function known as "linked server". No success. Debuging a VO code it
> always
> stops me when I try to initialize Adoserver class. Any ideas ?
>
> TIA
> Andrej Terkaj
>
>
> 


[toc] | [prev] | [next] | [standalone]


#1975

From"Willie Moore" <williem@wmconsulting.com>
Date2011-09-08 07:17 -0500
Message-ID<j4abnn$psp$1@speranza.aioe.org>
In reply to#1973
Andrej,

Did you try using an ADORecordset? ADOServer internally uses an ADORecordset 
but it also does a lot of conversions to VO types. ADORecordset it just the 
data.

        LOCAL oRS as ADORecordset
        LOCAL cSQL as String
        cSQL := "Select * from Customer"
        oRS := ADORecordset{}
        oRS:open(cSQL,SELF:oConn,adOpenForwardOnly,adLockReadOnly,adCmdText) 
// this is a readonly forward only that I use to build a drop down lookup
        if oRS:BOF .and oRS:EOF // no data was returned
           BREAK -1
        ENDIF

Regards,
Willie

"andrej Terkaj"  wrote in message news:j49v0a$nsd$1@news.siol.net...

Hi, Erik !

As I mentioned above my problem can be solved inside a Studio Management.
Yes, you are right I used a linked server with a IBProvider (I do not know
if it is an ADO.NET driver, probably no).
But I would like to have a problem solved inside a VO code --> using Vo2Ado
classes. A child table is a large table with aprox. half million records of
customers.
Any ideas are welcome.

Andrej


"E®!k \/!$$E®" <nospam@nospam.url> wrote in message
news:9cpk4jF86pU1@mid.individual.net...
> Andrej,
>
> If there is a ADO.NET provider available, you could try to use the linked 
> server facilities from MS SqlServer.
> Open Management Studio and see under Server Objects - Linked Servers.
>
> Never done this, but I am pretty sure this works as it can be done with 
> MySql and Oracle as well.
> hth
>
> Erik
>
>
>
> "andrej Terkaj"  schreef in bericht news:j47bm1$as3$1@news.siol.net...
>
> I am trying to join (relate) two different SQL tables MS SQL one (master)
> and Firebird /Interbase/MySQL as child one using Vo2Ado 2.8 -> Adoserver
> Connections to a single tables/databases are not a problem. I tried also a
> MS function known as "linked server". No success. Debuging a VO code it
> always
> stops me when I try to initialize Adoserver class. Any ideas ?
>
> TIA
> Andrej Terkaj
>
>
>

[toc] | [prev] | [next] | [standalone]


#1977

From"andrej Terkaj" <aterkaj@task.si>
Date2011-09-08 16:31 +0200
Message-ID<j4akp5$472$1@news.siol.net>
In reply to#1975
Hi, Willie !

Yes, I did try also with Adorecordset . Some examples bellow:

METHOD Test_8 CLASS StandardShellWindow

LOCAL oRs as AdoRecordSet

LOCAL oConn as AdoConnection

LOCAL oCmd as AdoCOmmand

oConn := OpenConnection()

oCmd := AdoCommand{}

oCmd:ActiveConnection := oConn

// it works  DNEV_PRI and ES_LOKASTOR are  tables within MSSQL Server

oCmd:CommandText := "Select CUST_CODE from DNEV_PRI;Select 
CUST_NAME,CUST_ADRESS  from ES_LOKASTOR"

oCmd:CommandType := AdCmdText

oRs := oCmd:Execute(nil, nil,nil)

? "First set"

ShowResults(oRs)

oRs := oRs:NextRecordset(nil)

IF oRs != null_object

? "Second set"

?

ShowResults(oRs)

ELSE

? "No second set"

ENDIF



//  second example --- it works ; TASK_LOKASTOR is a view in a Firebird 
database

oConn := Open_FireBirdConn("G:\Program Files\VASCO\10\VASCO.fdb")

oCmd := AdoCommand{}

oCmd:ActiveConnection := oConn

oCmd:CommandText := "Select PART_NAZIV,PART_NASLOV from TASK_LOKASTOR"

oCmd:CommandType := AdCmdText

oRs := oCmd:Execute(nil, nil,nil)

ShowResults(oRs)



// third example it doesn't work

// IBPROVIDER_FIREBIRD ... TASK_LOKASTOR  is a linked server/table  using 
SQL Studio MAnagement and it is linked on a view inside a Firebird DB

oConn := OpenConnection()

oCmd := AdoCommand{}

oCmd:ActiveConnection := oConn

oCmd:CommandText := "BEGIN DISTRIBUTED TRANSACTION Select 
PART_NAZIV,PART_NASLOV from IBPROVIDER_FIREBIRD...TASK_LOKASTOR"

oCmd:CommandType := AdCmdText

oRs := oCmd:Execute(nil, nil,nil)

ShowResults(oRs)


// when a use SQL statement "BEGIN DISTRIBUTED TRANSACTION Select 
PART_NAZIV,PART_NASLOV from IBPROVIDER_FIREBIRD...TASK_LOKASTOR" from inside 
Studio manager
I can see a results


TIA

Andrej Terkaj




"Willie Moore" <williem@wmconsulting.com> wrote in message 
news:j4abnn$psp$1@speranza.aioe.org...
> Andrej,
>
> Did you try using an ADORecordset? ADOServer internally uses an 
> ADORecordset but it also does a lot of conversions to VO types. 
> ADORecordset it just the data.
>
>        LOCAL oRS as ADORecordset
>        LOCAL cSQL as String
>        cSQL := "Select * from Customer"
>        oRS := ADORecordset{}
> 
> oRS:open(cSQL,SELF:oConn,adOpenForwardOnly,adLockReadOnly,adCmdText) // 
> this is a readonly forward only that I use to build a drop down lookup
>        if oRS:BOF .and oRS:EOF // no data was returned
>           BREAK -1
>        ENDIF
>
> Regards,
> Willie
>
> "andrej Terkaj"  wrote in message news:j49v0a$nsd$1@news.siol.net...
>
> Hi, Erik !
>
> As I mentioned above my problem can be solved inside a Studio Management.
> Yes, you are right I used a linked server with a IBProvider (I do not know
> if it is an ADO.NET driver, probably no).
> But I would like to have a problem solved inside a VO code --> using 
> Vo2Ado
> classes. A child table is a large table with aprox. half million records 
> of
> customers.
> Any ideas are welcome.
>
> Andrej
>
>
> "EŽ!k \/!$$EŽ" <nospam@nospam.url> wrote in message
> news:9cpk4jF86pU1@mid.individual.net...
>> Andrej,
>>
>> If there is a ADO.NET provider available, you could try to use the linked 
>> server facilities from MS SqlServer.
>> Open Management Studio and see under Server Objects - Linked Servers.
>>
>> Never done this, but I am pretty sure this works as it can be done with 
>> MySql and Oracle as well.
>> hth
>>
>> Erik
>>
>>
>>
>> "andrej Terkaj"  schreef in bericht news:j47bm1$as3$1@news.siol.net...
>>
>> I am trying to join (relate) two different SQL tables MS SQL one (master)
>> and Firebird /Interbase/MySQL as child one using Vo2Ado 2.8 -> Adoserver
>> Connections to a single tables/databases are not a problem. I tried also 
>> a
>> MS function known as "linked server". No success. Debuging a VO code it
>> always
>> stops me when I try to initialize Adoserver class. Any ideas ?
>>
>> TIA
>> Andrej Terkaj
>>
>>
>>
>
>
> 


[toc] | [prev] | [next] | [standalone]


#1978

From"Willie Moore" <williem@wmconsulting.com>
Date2011-09-08 10:30 -0500
Message-ID<j4an2f$pqb$1@speranza.aioe.org>
In reply to#1977
Andrej,

If you put the code with in a begin sequence recover using end sequence you 
are probably getting an error from the oCmd:Excute. The error code should 
shed some light on why it is working from the management studio and why it 
is not working in vo2ado.

Regards,
Willie

"andrej Terkaj"  wrote in message news:j4akp5$472$1@news.siol.net...

Hi, Willie !

Yes, I did try also with Adorecordset . Some examples bellow:

METHOD Test_8 CLASS StandardShellWindow

LOCAL oRs as AdoRecordSet

LOCAL oConn as AdoConnection

LOCAL oCmd as AdoCOmmand

oConn := OpenConnection()

oCmd := AdoCommand{}

oCmd:ActiveConnection := oConn

// it works  DNEV_PRI and ES_LOKASTOR are  tables within MSSQL Server

oCmd:CommandText := "Select CUST_CODE from DNEV_PRI;Select
CUST_NAME,CUST_ADRESS  from ES_LOKASTOR"

oCmd:CommandType := AdCmdText

oRs := oCmd:Execute(nil, nil,nil)

? "First set"

ShowResults(oRs)

oRs := oRs:NextRecordset(nil)

IF oRs != null_object

? "Second set"

?

ShowResults(oRs)

ELSE

? "No second set"

ENDIF



//  second example --- it works ; TASK_LOKASTOR is a view in a Firebird
database

oConn := Open_FireBirdConn("G:\Program Files\VASCO\10\VASCO.fdb")

oCmd := AdoCommand{}

oCmd:ActiveConnection := oConn

oCmd:CommandText := "Select PART_NAZIV,PART_NASLOV from TASK_LOKASTOR"

oCmd:CommandType := AdCmdText

oRs := oCmd:Execute(nil, nil,nil)

ShowResults(oRs)



// third example it doesn't work

// IBPROVIDER_FIREBIRD ... TASK_LOKASTOR  is a linked server/table  using
SQL Studio MAnagement and it is linked on a view inside a Firebird DB

oConn := OpenConnection()

oCmd := AdoCommand{}

oCmd:ActiveConnection := oConn

oCmd:CommandText := "BEGIN DISTRIBUTED TRANSACTION Select
PART_NAZIV,PART_NASLOV from IBPROVIDER_FIREBIRD...TASK_LOKASTOR"

oCmd:CommandType := AdCmdText

oRs := oCmd:Execute(nil, nil,nil)

ShowResults(oRs)


// when a use SQL statement "BEGIN DISTRIBUTED TRANSACTION Select
PART_NAZIV,PART_NASLOV from IBPROVIDER_FIREBIRD...TASK_LOKASTOR" from inside
Studio manager
I can see a results


TIA

Andrej Terkaj




"Willie Moore" <williem@wmconsulting.com> wrote in message
news:j4abnn$psp$1@speranza.aioe.org...
> Andrej,
>
> Did you try using an ADORecordset? ADOServer internally uses an 
> ADORecordset but it also does a lot of conversions to VO types. 
> ADORecordset it just the data.
>
>        LOCAL oRS as ADORecordset
>        LOCAL cSQL as String
>        cSQL := "Select * from Customer"
>        oRS := ADORecordset{}
>
> oRS:open(cSQL,SELF:oConn,adOpenForwardOnly,adLockReadOnly,adCmdText) // 
> this is a readonly forward only that I use to build a drop down lookup
>        if oRS:BOF .and oRS:EOF // no data was returned
>           BREAK -1
>        ENDIF
>
> Regards,
> Willie
>
> "andrej Terkaj"  wrote in message news:j49v0a$nsd$1@news.siol.net...
>
> Hi, Erik !
>
> As I mentioned above my problem can be solved inside a Studio Management.
> Yes, you are right I used a linked server with a IBProvider (I do not know
> if it is an ADO.NET driver, probably no).
> But I would like to have a problem solved inside a VO code --> using 
> Vo2Ado
> classes. A child table is a large table with aprox. half million records 
> of
> customers.
> Any ideas are welcome.
>
> Andrej
>
>
> "E®!k \/!$$E®" <nospam@nospam.url> wrote in message
> news:9cpk4jF86pU1@mid.individual.net...
>> Andrej,
>>
>> If there is a ADO.NET provider available, you could try to use the linked 
>> server facilities from MS SqlServer.
>> Open Management Studio and see under Server Objects - Linked Servers.
>>
>> Never done this, but I am pretty sure this works as it can be done with 
>> MySql and Oracle as well.
>> hth
>>
>> Erik
>>
>>
>>
>> "andrej Terkaj"  schreef in bericht news:j47bm1$as3$1@news.siol.net...
>>
>> I am trying to join (relate) two different SQL tables MS SQL one (master)
>> and Firebird /Interbase/MySQL as child one using Vo2Ado 2.8 -> Adoserver
>> Connections to a single tables/databases are not a problem. I tried also 
>> a
>> MS function known as "linked server". No success. Debuging a VO code it
>> always
>> stops me when I try to initialize Adoserver class. Any ideas ?
>>
>> TIA
>> Andrej Terkaj
>>
>>
>>
>
>
>

[toc] | [prev] | [next] | [standalone]


#1985

From"andrej Terkaj" <aterkaj@task.si>
Date2011-09-09 12:25 +0200
Message-ID<j4cqn5$8hi$1@news.siol.net>
In reply to#1978
Willie !

here is the answer:

uError:Description ---->>  AdErrInvalidConnection
Example:

cbErr := ErrorBlock({|oErr|_Break(oErr)})

oConn := OpenConnection()

oCmd := AdoCommand{}

oCmd:ActiveConnection := oConn

oCmd:CommandText := "BEGIN DISTRIBUTED TRANSACTION "

oCmd:CommandType := AdCmdText

oCmd:Execute(nil, nil,nil)    // works

oCmd := AdoCommand{}

oCmd:CommandText := "Select PART_NAZIV,PART_NASLOV from 
IBPROVIDER_FIREBIRD...TASK_LOKASTOR"

oCmd:CommandType := AdCmdText


BEGIN SEQUENCE

oRs := oCmd:Execute(nil, nil,nil)

RECOVER USING uError

ErrorBox{,uError:description}:Show()

end


uError:Description ---->>  AdErrInvalidConnection

I also enclose you some terminal output strings-- Connection succedd

Provider: SQLOLEDB.1
database Name: RADIX_ENS
String  : Provider=SQLOLEDB.1;Password=radix;User ID=radix;Initial 
Catalog=RADIX
_ENS;Data Source=(local);Use Procedure for Prepare=1;Auto 
Translate=True;Packet
Size=4096;Workstation ID=TASK-29VA034HSO;Use Encryption for Data=False;Tag 
with
column collation when possible=False

TIA

Andrej

"Willie Moore" <williem@wmconsulting.com> wrote in message 
news:j4an2f$pqb$1@speranza.aioe.org...
> Andrej,
>
> If you put the code with in a begin sequence recover using end sequence 
> you are probably getting an error from the oCmd:Excute. The error code 
> should shed some light on why it is working from the management studio and 
> why it is not working in vo2ado.
>
> Regards,
> Willie
>
> "andrej Terkaj"  wrote in message news:j4akp5$472$1@news.siol.net...
>
> Hi, Willie !
>
> Yes, I did try also with Adorecordset . Some examples bellow:
>
> METHOD Test_8 CLASS StandardShellWindow
>
> LOCAL oRs as AdoRecordSet
>
> LOCAL oConn as AdoConnection
>
> LOCAL oCmd as AdoCOmmand
>
> oConn := OpenConnection()
>
> oCmd := AdoCommand{}
>
> oCmd:ActiveConnection := oConn
>
> // it works  DNEV_PRI and ES_LOKASTOR are  tables within MSSQL Server
>
> oCmd:CommandText := "Select CUST_CODE from DNEV_PRI;Select
> CUST_NAME,CUST_ADRESS  from ES_LOKASTOR"
>
> oCmd:CommandType := AdCmdText
>
> oRs := oCmd:Execute(nil, nil,nil)
>
> ? "First set"
>
> ShowResults(oRs)
>
> oRs := oRs:NextRecordset(nil)
>
> IF oRs != null_object
>
> ? "Second set"
>
> ?
>
> ShowResults(oRs)
>
> ELSE
>
> ? "No second set"
>
> ENDIF
>
>
>
> //  second example --- it works ; TASK_LOKASTOR is a view in a Firebird
> database
>
> oConn := Open_FireBirdConn("G:\Program Files\VASCO\10\VASCO.fdb")
>
> oCmd := AdoCommand{}
>
> oCmd:ActiveConnection := oConn
>
> oCmd:CommandText := "Select PART_NAZIV,PART_NASLOV from TASK_LOKASTOR"
>
> oCmd:CommandType := AdCmdText
>
> oRs := oCmd:Execute(nil, nil,nil)
>
> ShowResults(oRs)
>
>
>
> // third example it doesn't work
>
> // IBPROVIDER_FIREBIRD ... TASK_LOKASTOR  is a linked server/table  using
> SQL Studio MAnagement and it is linked on a view inside a Firebird DB
>
> oConn := OpenConnection()
>
> oCmd := AdoCommand{}
>
> oCmd:ActiveConnection := oConn
>
> oCmd:CommandText := "BEGIN DISTRIBUTED TRANSACTION Select
> PART_NAZIV,PART_NASLOV from IBPROVIDER_FIREBIRD...TASK_LOKASTOR"
>
> oCmd:CommandType := AdCmdText
>
> oRs := oCmd:Execute(nil, nil,nil)
>
> ShowResults(oRs)
>
>
> // when a use SQL statement "BEGIN DISTRIBUTED TRANSACTION Select
> PART_NAZIV,PART_NASLOV from IBPROVIDER_FIREBIRD...TASK_LOKASTOR" from 
> inside
> Studio manager
> I can see a results
>
>
> TIA
>
> Andrej Terkaj
>
>
>
>
> "Willie Moore" <williem@wmconsulting.com> wrote in message
> news:j4abnn$psp$1@speranza.aioe.org...
>> Andrej,
>>
>> Did you try using an ADORecordset? ADOServer internally uses an 
>> ADORecordset but it also does a lot of conversions to VO types. 
>> ADORecordset it just the data.
>>
>>        LOCAL oRS as ADORecordset
>>        LOCAL cSQL as String
>>        cSQL := "Select * from Customer"
>>        oRS := ADORecordset{}
>>
>> oRS:open(cSQL,SELF:oConn,adOpenForwardOnly,adLockReadOnly,adCmdText) // 
>> this is a readonly forward only that I use to build a drop down lookup
>>        if oRS:BOF .and oRS:EOF // no data was returned
>>           BREAK -1
>>        ENDIF
>>
>> Regards,
>> Willie
>>
>> "andrej Terkaj"  wrote in message news:j49v0a$nsd$1@news.siol.net...
>>
>> Hi, Erik !
>>
>> As I mentioned above my problem can be solved inside a Studio Management.
>> Yes, you are right I used a linked server with a IBProvider (I do not 
>> know
>> if it is an ADO.NET driver, probably no).
>> But I would like to have a problem solved inside a VO code --> using 
>> Vo2Ado
>> classes. A child table is a large table with aprox. half million records 
>> of
>> customers.
>> Any ideas are welcome.
>>
>> Andrej
>>
>>
>> "EŽ!k \/!$$EŽ" <nospam@nospam.url> wrote in message
>> news:9cpk4jF86pU1@mid.individual.net...
>>> Andrej,
>>>
>>> If there is a ADO.NET provider available, you could try to use the 
>>> linked server facilities from MS SqlServer.
>>> Open Management Studio and see under Server Objects - Linked Servers.
>>>
>>> Never done this, but I am pretty sure this works as it can be done with 
>>> MySql and Oracle as well.
>>> hth
>>>
>>> Erik
>>>
>>>
>>>
>>> "andrej Terkaj"  schreef in bericht news:j47bm1$as3$1@news.siol.net...
>>>
>>> I am trying to join (relate) two different SQL tables MS SQL one 
>>> (master)
>>> and Firebird /Interbase/MySQL as child one using Vo2Ado 2.8 -> Adoserver
>>> Connections to a single tables/databases are not a problem. I tried also 
>>> a
>>> MS function known as "linked server". No success. Debuging a VO code it
>>> always
>>> stops me when I try to initialize Adoserver class. Any ideas ?
>>>
>>> TIA
>>> Andrej Terkaj
>>>
>>>
>>>
>>
>>
>>
>
>
> 


[toc] | [prev] | [next] | [standalone]


#1987

From"Willie Moore" <williem@wmconsulting.com>
Date2011-09-09 08:26 -0500
Message-ID<j4d45j$ems$1@speranza.aioe.org>
In reply to#1985
Andrej,

The easiest way to solve your issue would be to write a stored proceedure. 
but if you want to do it all in VO you must include the entire SQL command 
in one line so it is executed as a script. Use ";" in between lines. Have 
you ever downloaded Geoff's SQLMaster. It is a very good tool to run your 
SQL scripts. It is written entirely in VO.

Regards,
Willie

// stored proceedure
cbErr := ErrorBlock({|oErr|_Break(oErr)})
begin sequence
    oConn := OpenConnection()
    oCmd := AdoCommand{}
    oCmd:ActiveConnection := oConn
    oCmd:CommandText := "exec spMyStoredProc"
    oCmd:CommandType := AdCmdText
    oRS := oCmd:Execute(nil, nil,nil)
recover using oErr
end sequence

// recordset
cbErr := ErrorBlock({|oErr|_Break(oErr)})
begin sequence
    cSQL :=  "BEGIN DISTRIBUTED TRANSACTION; "
    cSQL += " Select PART_NAZIV,PART_NASLOV from 
IBPROVIDER_FIREBIRD...TASK_LOKASTOR"
    oConn := OpenConnection()
    oCmd := AdoCommand{}
    oCmd:ActiveConnection := oConn
    oCmd:CommandText := cSQL
    oCmd:CommandType := AdCmdText
    oRS := oCmd:Execute(nil, nil,nil)
recover using oErr
end sequence

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
CREATE PROCEDURE <spMyStoredProc>
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    BEGIN DISTRIBUTED TRANSACTION;
    Select PART_NAZIV,PART_NASLOV from IBPROVIDER_FIREBIRD...TASK_LOKASTOR
END
GO

[toc] | [prev] | [next] | [standalone]


#2001

From"andrej Terkaj" <aterkaj@task.si>
Date2011-09-13 16:27 +0200
Message-ID<j4nqdr$vtv$1@news.siol.net>
In reply to#1987
Hi Willie !

Thank you once again.  I solved my problem. Now it works with both ways ( 
stored procedure or without it). A trouble  was in a connectivity security 
.Because of my definition of SQL authentication a connection to the linked 
server must be made using security context - login and password (of course I 
used it for the firebird conn. string ) but I forgot it  for the MSSQL 
server. A little bit strange philosophy, that  you must indicate login and 
password two times.

Best regards

Andrej

"Willie Moore" <williem@wmconsulting.com> wrote in message 
news:j4d45j$ems$1@speranza.aioe.org...
> Andrej,
>
> The easiest way to solve your issue would be to write a stored proceedure. 
> but if you want to do it all in VO you must include the entire SQL command 
> in one line so it is executed as a script. Use ";" in between lines. Have 
> you ever downloaded Geoff's SQLMaster. It is a very good tool to run your 
> SQL scripts. It is written entirely in VO.
>
> Regards,
> Willie
>
> // stored proceedure
> cbErr := ErrorBlock({|oErr|_Break(oErr)})
> begin sequence
>    oConn := OpenConnection()
>    oCmd := AdoCommand{}
>    oCmd:ActiveConnection := oConn
>    oCmd:CommandText := "exec spMyStoredProc"
>    oCmd:CommandType := AdCmdText
>    oRS := oCmd:Execute(nil, nil,nil)
> recover using oErr
> end sequence
>
> // recordset
> cbErr := ErrorBlock({|oErr|_Break(oErr)})
> begin sequence
>    cSQL :=  "BEGIN DISTRIBUTED TRANSACTION; "
>    cSQL += " Select PART_NAZIV,PART_NASLOV from 
> IBPROVIDER_FIREBIRD...TASK_LOKASTOR"
>    oConn := OpenConnection()
>    oCmd := AdoCommand{}
>    oCmd:ActiveConnection := oConn
>    oCmd:CommandText := cSQL
>    oCmd:CommandType := AdCmdText
>    oRS := oCmd:Execute(nil, nil,nil)
> recover using oErr
> end sequence
>
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> -- =============================================
> -- Author:        <Author,,Name>
> -- Create date: <Create Date,,>
> -- Description:    <Description,,>
> -- =============================================
> CREATE PROCEDURE <spMyStoredProc>
> AS
> BEGIN
>    -- SET NOCOUNT ON added to prevent extra result sets from
>    -- interfering with SELECT statements.
>    SET NOCOUNT ON;
>
>    BEGIN DISTRIBUTED TRANSACTION;
>    Select PART_NAZIV,PART_NASLOV from IBPROVIDER_FIREBIRD...TASK_LOKASTOR
> END
> GO
>
> 


[toc] | [prev] | [next] | [standalone]


#1980

FromE®!k \\/!$$E® <nospam@nospam.url>
Date2011-09-08 19:30 +0200
Message-ID<9cscdkF46dU1@mid.individual.net>
In reply to#1973
Andrej,
You do not need to do it with Management Studio. It can be done with a query 
as well, using OpenRowSet
Best (fastest) way for these kind of action always is to do as much as 
possible on the server side, not inside you code.
Here is an article about this: http://tinyurl.com/4dzq6rz

Erik





"andrej Terkaj"  schreef in bericht news:j49v0a$nsd$1@news.siol.net...

Hi, Erik !

As I mentioned above my problem can be solved inside a Studio Management.
Yes, you are right I used a linked server with a IBProvider (I do not know
if it is an ADO.NET driver, probably no).
But I would like to have a problem solved inside a VO code --> using Vo2Ado
classes. A child table is a large table with aprox. half million records of
customers.
Any ideas are welcome.

Andrej


"E®!k \/!$$E®" <nospam@nospam.url> wrote in message
news:9cpk4jF86pU1@mid.individual.net...
> Andrej,
>
> If there is a ADO.NET provider available, you could try to use the linked 
> server facilities from MS SqlServer.
> Open Management Studio and see under Server Objects - Linked Servers.
>
> Never done this, but I am pretty sure this works as it can be done with 
> MySql and Oracle as well.
> hth
>
> Erik
>
>
>
> "andrej Terkaj"  schreef in bericht news:j47bm1$as3$1@news.siol.net...
>
> I am trying to join (relate) two different SQL tables MS SQL one (master)
> and Firebird /Interbase/MySQL as child one using Vo2Ado 2.8 -> Adoserver
> Connections to a single tables/databases are not a problem. I tried also a
> MS function known as "linked server". No success. Debuging a VO code it
> always
> stops me when I try to initialize Adoserver class. Any ideas ?
>
> TIA
> Andrej Terkaj
>
>
>

[toc] | [prev] | [next] | [standalone]


#1984

From"andrej Terkaj" <aterkaj@task.si>
Date2011-09-09 11:57 +0200
Message-ID<j4cp39$7i6$1@news.siol.net>
In reply to#1980
Thank you Erik for usefull link. I will let you know on my progress 
resolving

Andrej

"EŽ!k \/!$$EŽ" <nospam@nospam.url> wrote in message 
news:9cscdkF46dU1@mid.individual.net...
> Andrej,
> You do not need to do it with Management Studio. It can be done with a 
> query as well, using OpenRowSet
> Best (fastest) way for these kind of action always is to do as much as 
> possible on the server side, not inside you code.
> Here is an article about this: http://tinyurl.com/4dzq6rz
>
> Erik
>
>
>
>
>
> "andrej Terkaj"  schreef in bericht news:j49v0a$nsd$1@news.siol.net...
>
> Hi, Erik !
>
> As I mentioned above my problem can be solved inside a Studio Management.
> Yes, you are right I used a linked server with a IBProvider (I do not know
> if it is an ADO.NET driver, probably no).
> But I would like to have a problem solved inside a VO code --> using 
> Vo2Ado
> classes. A child table is a large table with aprox. half million records 
> of
> customers.
> Any ideas are welcome.
>
> Andrej
>
>
> "EŽ!k \/!$$EŽ" <nospam@nospam.url> wrote in message
> news:9cpk4jF86pU1@mid.individual.net...
>> Andrej,
>>
>> If there is a ADO.NET provider available, you could try to use the linked 
>> server facilities from MS SqlServer.
>> Open Management Studio and see under Server Objects - Linked Servers.
>>
>> Never done this, but I am pretty sure this works as it can be done with 
>> MySql and Oracle as well.
>> hth
>>
>> Erik
>>
>>
>>
>> "andrej Terkaj"  schreef in bericht news:j47bm1$as3$1@news.siol.net...
>>
>> I am trying to join (relate) two different SQL tables MS SQL one (master)
>> and Firebird /Interbase/MySQL as child one using Vo2Ado 2.8 -> Adoserver
>> Connections to a single tables/databases are not a problem. I tried also 
>> a
>> MS function known as "linked server". No success. Debuging a VO code it
>> always
>> stops me when I try to initialize Adoserver class. Any ideas ?
>>
>> TIA
>> Andrej Terkaj
>>
>>
>>
>
>
> 


[toc] | [prev] | [standalone]


Back to top | Article view | comp.lang.clipper.visual-objects


csiph-web