Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]
Groups > comp.lang.clipper.visual-objects > #1966 > unrolled thread
| Started by | "andrej Terkaj" <aterkaj@task.si> |
|---|---|
| First post | 2011-09-07 10:38 +0200 |
| Last post | 2011-09-09 11:57 +0200 |
| Articles | 14 — 3 participants |
Back to article view | Back to comp.lang.clipper.visual-objects
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
| From | "andrej Terkaj" <aterkaj@task.si> |
|---|---|
| Date | 2011-09-07 10:38 +0200 |
| Subject | Joining 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]
| From | "Willie Moore" <williem@wmconsulting.com> |
|---|---|
| Date | 2011-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]
| From | "andrej Terkaj" <aterkaj@task.si> |
|---|---|
| Date | 2011-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]
| From | "Willie Moore" <williem@wmconsulting.com> |
|---|---|
| Date | 2011-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]
| From | E®!k \\/!$$E® <nospam@nospam.url> |
|---|---|
| Date | 2011-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]
| From | "andrej Terkaj" <aterkaj@task.si> |
|---|---|
| Date | 2011-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]
| From | "Willie Moore" <williem@wmconsulting.com> |
|---|---|
| Date | 2011-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]
| From | "andrej Terkaj" <aterkaj@task.si> |
|---|---|
| Date | 2011-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]
| From | "Willie Moore" <williem@wmconsulting.com> |
|---|---|
| Date | 2011-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]
| From | "andrej Terkaj" <aterkaj@task.si> |
|---|---|
| Date | 2011-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]
| From | "Willie Moore" <williem@wmconsulting.com> |
|---|---|
| Date | 2011-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]
| From | "andrej Terkaj" <aterkaj@task.si> |
|---|---|
| Date | 2011-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]
| From | E®!k \\/!$$E® <nospam@nospam.url> |
|---|---|
| Date | 2011-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]
| From | "andrej Terkaj" <aterkaj@task.si> |
|---|---|
| Date | 2011-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