Prev: using Ms OutlookView
Next: VO Beginner: how to use Oracle with VO 2.6?
From: Meinhard Schnoor-Matriciani on 11 Oct 2006 11:20
Hi Geoff ,

are you allowed to modify these databases ?

The reason why I ask is, that it might be easier to use the databases
directly to achiev what you would like to to. In both orducts ( MS SQL and
Sybase Anywhere ) you can defined something which enables you to access the
other database directly. In MS this is called linked server and in Sybase
you need to define proxy tables. Thta way you can define a view in either
the MS or the Sybase server which accesses the other side and joins it
together with your table. Then access this view from inside your client
program.

Regards
Meinhard



From: Filip Fransen on 12 Oct 2006 04:53
I wrote my own driver for ADS so I can use both Sql-Style / xBase-Style.
This gives me the opportunity to get the best from both worlds and slowly
migrate my app to use sql-queries.

Filip

"Geoff" <geoff(a)soft_objectives.com.au> wrote in message
news:452df9d0$1(a)dnews.tpgi.com.au...
> Richard.
>
> SQL is not slow on lookups - where is your evidence for this <g>.
>
> Mind you, I can SQL go slow but I can make DBF go slow too - just a apply
> a big filter!
>
> Secondly, SQL is superior to ADS for an SQL engine as opposed to a DBF
> engine. But that much is obvious - what is less obvious would be the
> comparison between ADS SQL and MS SQL, for instance. The only thing I'd
> say here is that MS SQL is potentially cheaper TCO wise than MS SQL (at
> all scales) and MS is used by vastly more people. However it is equally
> true to say that most ADS enthusiasts are quite happy with ADS. So too are
> MySQL users <g>. ADS do give good support.
>
> Now, joins ARE a good idea - I am not sure where you get your counter
> advice from. Like anything in SQL they have to be done properly but it is
> the concept of a join that gives you the most important benefit over a
> DBF. Infinite and (usually) instant relations at runtime. A DBF requires
> you to plan relations around known indexes and to change one requires you
> to change code and change indexes.
>
> "richard.townsendrose(a)googlemail.com"
> <richard.townsendrose(a)googlemail.com> wrote in message
> news:1160639833.585275.97300(a)i3g2000cwc.googlegroups.com:
>
>> Geoff,
>>
>> What I want to know is why SQL is so slow on lookups etc. and why
>> "joins" are reported as not to be a good idea.
>>
>> SQL is supposed to be superior to ADS .... but really is it ????
>>
>> Richard
>


From: Geoff Chambers on 12 Oct 2006 11:41
Yes I have complete access to the database, this is exactly what I want
to do, I have created complex views using the same database I'll have
to try the way you explained. Can you explain it in more detail? I
would like to set it up in SQL Express and link to the Sybase table. my
email is gchambers02(a)msn.com

Meinhard Schnoor-Matriciani wrote:
> Hi Geoff ,
>
> are you allowed to modify these databases ?
>
> The reason why I ask is, that it might be easier to use the databases
> directly to achiev what you would like to to. In both orducts ( MS SQL and
> Sybase Anywhere ) you can defined something which enables you to access the
> other database directly. In MS this is called linked server and in Sybase
> you need to define proxy tables. Thta way you can define a view in either
> the MS or the Sybase server which accesses the other side and joins it
> together with your table. Then access this view from inside your client
> program.
>
> Regards
> Meinhard

From: Geoff on 12 Oct 2006 17:58
Meinhard.

Sure. Connections are connections, they are as read-write as any other
but I should have qualified one thing (when using VO2Ado). Robert only
anticipates one connection with the AdoGetConnection() function and so
if you require two properly independent connections then you need to
override this method and make your connection object class dependent.
ie, stored with each server class. We leave the "Robert" one as default.

We also override the FieldPut method to accommodate both joined tables
and writing to the "related" child table. As I said, perhaps the most
difficult thing, especially with browsers, is to suffer the select into
the child for every row displayed in a browser. There is a performance
issue to mind but the benefit is obvious.

If browsers are not the issue then a way to achieve good performance
with this concept is to override the AdoServer skipping mechanisms that
move the child and only allow child update at the point of requesting
new data. For example, skipping loops where the related table is only
used conditionally. The lookup is a seek so it doesn't matter how many
operations are missed.

"Meinhard Schnoor-Matriciani" <meinhard(a)appfact.de> wrote in message
news:452d0d44$1_1(a)news.arcor-ip.de:

> Hi Geoff ,
>
> are you allowed to modify these databases ?
>
> The reason why I ask is, that it might be easier to use the databases
> directly to achiev what you would like to to. In both orducts ( MS SQL and
> Sybase Anywhere ) you can defined something which enables you to access the
> other database directly. In MS this is called linked server and in Sybase
> you need to define proxy tables. Thta way you can define a view in either
> the MS or the Sybase server which accesses the other side and joins it
> together with your table. Then access this view from inside your client
> program.
>
> Regards
> Meinhard

From: Meinhard Schnoor-Matriciani on 13 Oct 2006 10:20
Hi Geoff,

ok, so here we go. I have the german version of sql express installed, so
hopefully I am using the right translations. First of all,
if not yet done, get yourself a copy of Microsofts SQL Server Management
Studio Express. Then go ahead, start it and choose your database.
In the tree you have database, security , server objects , replication and
management. Click on >>server objects<< and expand it. Then you
should see backup media, linked server and server trigger, or something
similar ( as I said I only have the german version ... ). Now right
click on linked server ( the second node ) and choose new linked server.
You'll get a dialog window where you can define a linked server
with all information neccessary to make your other database server
accessible to your SQL Express engine. Let's say your SQL Engine is
installed
on Machine SQLExpress and the name of your just defined linked server is
SQLAnywhere, then you're able to access the tables inside your SQL Anywhere
database by simply qualifying the table with the name of the linked server.
Here are some samples ( you can test this from the Management Studio
by open the database node and right clicking one of the databases and choose
new query ) :

select * from SQLAnywhere..dba.employee -- accessing the
employee table in the asademo

select * from SQLServer2000.Northwind.dbo.Employees -- accessing the
employee table in the northwind sample database on an SQL Server 2000
installation

Watch out for the .. in the frist sample. This is neccessary because
Anywhere does know the concept of database qualification.

Now after you've done the, you can do things like

create view MixedAccess
select *
from SQLServer2000.Northwind.dbo.Employees as Emp1
join SQLAnywhere..dba.employee as Emp2 on
Emp1.EmployeeID = Emp2.Emp_id

go

select * from mixedaccess


This would work with all kind of databases which can be access by the
machine where SQL Express is running on, as long as you have the neccessary
driver installed. We constructed a datawarehouse for a large customer, where
we're using this technique to access different databases : Oracle, AS
400/DB2
Informix, MySQL, ADS and even Excel or DBF. Works like a charme.

Regards,
Meinhard


First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4
Prev: using Ms OutlookView
Next: VO Beginner: how to use Oracle with VO 2.6?