accessing 2 databases in 1 sql query

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

accessing 2 databases in 1 sql query

Matthias Rebbe via use-livecode
Hi

Sorry if this counts as a double post but I thought I'd cast my net Wieder
after no bytes in the forums (both puns intended).

https://forums.livecode.com/viewtopic.php?f=12&t=30481

Basically I want to copy (migrate) a table in 1 database to another with
exactly the same number of records and same primary key but the new table
has a few extra fields which will be empty.

I can do it with a cut and paste in sqlitestudio - but I've written a
migration script for all the other tables which are not only smaller  but
it's a straight insert using a cursor.

I've thought of at least 4 ways of doing this - all will probably be slower
than this more direct route, but I'm thinking in the future using
mysql/postgres where I might(will?) need inner or outer joins from external
databases to do syncing - and yes  know I can do those as well with some
sql magic but I like SQL 1 liners ever since it was introduced in Foxpro
(for DOS)  over 25 years ago.

I can do this in Foxpro, VB, PHP  and any myriad of other languages but for
the life off me I can't think how this can be done using the calling
methods used within LC, since we pass the database "handle" outside the
query string how can we qualify a column as to the database it comes from.

If the answer is it can't be done then I'll have to do it in a  slower less
succinct way.

Thanks Lagi
_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: accessing 2 databases in 1 sql query

Matthias Rebbe via use-livecode
Does this help?  http://www.sqlitetutorial.net/sqlite-attach-database/

Short version.. attach database lets you attach another db file to the
current connection.  So if you have db "contacts" open, and you attach a
file AS contacts2 (which is an alias defined during the attach) then you
can address a specific table from the first db with "contacts.tablename"
and the second with "contacts2.tablename"

At which point you can most likely do something like..

INSERT INTO CONTACTS2.TABLENAME(fieldname1, fieldname2) SELECT
fieldname1, fieldname2 FROM CONTACTS.TABLENAME;

Alternatively, you might just create a copy of the table from the first db,
to create a table in the second db and then alter the table to add the
missing columns.  Not sure which would be better/more efficient.


On Thu, Jan 18, 2018 at 4:40 AM, Lagi Pittas via use-livecode <
[hidden email]> wrote:

> Hi
>
> Sorry if this counts as a double post but I thought I'd cast my net Wieder
> after no bytes in the forums (both puns intended).
>
> https://forums.livecode.com/viewtopic.php?f=12&t=30481
>
> Basically I want to copy (migrate) a table in 1 database to another with
> exactly the same number of records and same primary key but the new table
> has a few extra fields which will be empty.
>
> I can do it with a cut and paste in sqlitestudio - but I've written a
> migration script for all the other tables which are not only smaller  but
> it's a straight insert using a cursor.
>
> I've thought of at least 4 ways of doing this - all will probably be slower
> than this more direct route, but I'm thinking in the future using
> mysql/postgres where I might(will?) need inner or outer joins from external
> databases to do syncing - and yes  know I can do those as well with some
> sql magic but I like SQL 1 liners ever since it was introduced in Foxpro
> (for DOS)  over 25 years ago.
>
> I can do this in Foxpro, VB, PHP  and any myriad of other languages but for
> the life off me I can't think how this can be done using the calling
> methods used within LC, since we pass the database "handle" outside the
> query string how can we qualify a column as to the database it comes from.
>
> If the answer is it can't be done then I'll have to do it in a  slower less
> succinct way.
>
> Thanks Lagi
> _______________________________________________
> use-livecode mailing list
> [hidden email]
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
>
_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: accessing 2 databases in 1 sql query

Matthias Rebbe via use-livecode
Hi Thanks MIke

Your a saviour - I read about attach over a year or so  ago when I was
looking at how pragma works and other stuff like that but had no need till
now so totally forgot .

Thanks again

Lagi



On 18 January 2018 at 14:14, Mike Bonner via use-livecode <
[hidden email]> wrote:

> Does this help?  http://www.sqlitetutorial.net/sqlite-attach-database/
>
> Short version.. attach database lets you attach another db file to the
> current connection.  So if you have db "contacts" open, and you attach a
> file AS contacts2 (which is an alias defined during the attach) then you
> can address a specific table from the first db with "contacts.tablename"
> and the second with "contacts2.tablename"
>
> At which point you can most likely do something like..
>
> INSERT INTO CONTACTS2.TABLENAME(fieldname1, fieldname2) SELECT
> fieldname1, fieldname2 FROM CONTACTS.TABLENAME;
>
> Alternatively, you might just create a copy of the table from the first db,
> to create a table in the second db and then alter the table to add the
> missing columns.  Not sure which would be better/more efficient.
>
>
> On Thu, Jan 18, 2018 at 4:40 AM, Lagi Pittas via use-livecode <
> [hidden email]> wrote:
>
> > Hi
> >
> > Sorry if this counts as a double post but I thought I'd cast my net
> Wieder
> > after no bytes in the forums (both puns intended).
> >
> > https://forums.livecode.com/viewtopic.php?f=12&t=30481
> >
> > Basically I want to copy (migrate) a table in 1 database to another with
> > exactly the same number of records and same primary key but the new table
> > has a few extra fields which will be empty.
> >
> > I can do it with a cut and paste in sqlitestudio - but I've written a
> > migration script for all the other tables which are not only smaller  but
> > it's a straight insert using a cursor.
> >
> > I've thought of at least 4 ways of doing this - all will probably be
> slower
> > than this more direct route, but I'm thinking in the future using
> > mysql/postgres where I might(will?) need inner or outer joins from
> external
> > databases to do syncing - and yes  know I can do those as well with some
> > sql magic but I like SQL 1 liners ever since it was introduced in Foxpro
> > (for DOS)  over 25 years ago.
> >
> > I can do this in Foxpro, VB, PHP  and any myriad of other languages but
> for
> > the life off me I can't think how this can be done using the calling
> > methods used within LC, since we pass the database "handle" outside the
> > query string how can we qualify a column as to the database it comes
> from.
> >
> > If the answer is it can't be done then I'll have to do it in a  slower
> less
> > succinct way.
> >
> > Thanks Lagi
> > _______________________________________________
> > use-livecode mailing list
> > [hidden email]
> > Please visit this url to subscribe, unsubscribe and manage your
> > subscription preferences:
> > http://lists.runrev.com/mailman/listinfo/use-livecode
> >
> _______________________________________________
> use-livecode mailing list
> [hidden email]
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
>
_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode