Re: Remote SQL databases
On May 21, 2010, at 6:31 PM, Bob Sneidar wrote: I'm wondering if the Relational aspect of Trevor's sqlYoga would be able to do this, but again, I think I have to be working with two tables in the same database for that to work. Trevor? That is correct. SQL Yoga will only work with relationships between tables in the same database. -- Trevor DeVore Blue Mango Learning Systems ScreenSteps: http://www.screensteps.com Releasable Revolution Resources for Developers: http://revolution.bluemangolearning.com ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
Re: Remote SQL databases
Which is why if I pull this off, there are people who may be interested in it. Bob On May 25, 2010, at 10:19 AM, Mark Wieder wrote: > I think the only way around this is to issue two SQL > commands, one to MySQL, one to SQLite, then take the recordsets and > mangle them yourself, pretending that you're the cross-db-type engine. > Could get ugly. ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
Re: Remote SQL databases
Bob- Monday, May 24, 2010, 9:00:59 PM, you wrote: > I am not a MySQL expert but I have found that it is possible to > join different databases, each with various tables. Bob S.'s main issue here is that he's trying to join two different *types* of databases, a MySQL database and a SQLite database. That can't be done with an SQL statement because the SQL command is performed by the database itself. So if you send a SQL command to the MySQL database it's getting executed by the MySQL engine, not by rev. The MySQL engine has no knowledge of any other databases, and so it would fail. I think the only way around this is to issue two SQL commands, one to MySQL, one to SQLite, then take the recordsets and mangle them yourself, pretending that you're the cross-db-type engine. Could get ugly. -- -Mark Wieder mwie...@ahsoftware.net ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
Re: Remote SQL databases
All well and good... so long as the database DOING the JOIN knows about the database it is joining to. In my case, this is not so. The joining database has no access to the related database. Thanks for the help tho. It's still an education. Bob On May 24, 2010, at 9:00 PM, Bob Cole wrote: > I am not a MySQL expert but I have found that it is possible to join > different databases, each with various tables. > The trick is to use the database name to fully qualify the table and item > such as: > SELECT db1.table1.item1, db2.table2.item1 FROM db1.table1, db2.table2 WHERE > ... > > Here is a sample that I have used successfully: > put "SELECT db1.table1.date, " into tSQLcommand > put "db2.table2.type, FORMAT(db2.table2.amount,2), " after tSQLcommand > put "db1.table1.code1 " after tSQLcommand > put "FROM db1.table1 " after tSQLcommand > put "INNER JOIN db2.table2 ON db1.table1.code1 = db2.table2.code2 " after > tSQLcommand > put "WHERE db2.table2.amount > 1.00 " after tSQLcommand > put "ORDER BY db1.table1.date; " after tSQLcommand > put revDataFromQuery(tab, return, tConId, tSQLcommand) into tItems > > I did not find it necessary to give the databases alias names. > In my situation, both databases are on the same machine and can be accessed > using the same username/password. > Hope this helps. > Bob > > > Date: Sun, 23 May 2010 18:19:24 -0300, From: Andre Garzia > > Subject: Re: Remote SQL databases > > I did it on my 6k thousand table database... > > It works fine for us. > > I have something like > > select Database1.* from DatabaseName1.Table1 as Database1, > DatabaseName2.Table2 ... > > On Sun, May 23, 2010 at 4:34 PM, Mark Wieder wrote: > >> Bob- >> >> Sunday, May 23, 2010, 12:04:03 PM, you wrote: >> >>> Oh hello! You are saying you CAN do a join on a table that is not in >>> the same database? >> >> Well, I haven't tried it, bu Mark S. implied that it was possible with >> two different aliases... >> >> -- >> -Mark Wieder >> mwie...@ahsoftware.net > > ___ > use-revolution mailing list > use-revolution@lists.runrev.com > Please visit this url to subscribe, unsubscribe and manage your subscription > preferences: > http://lists.runrev.com/mailman/listinfo/use-revolution ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
Re: Remote SQL databases
I am not a MySQL expert but I have found that it is possible to join different databases, each with various tables. The trick is to use the database name to fully qualify the table and item such as: SELECT db1.table1.item1, db2.table2.item1 FROM db1.table1, db2.table2 WHERE ... Here is a sample that I have used successfully: put "SELECT db1.table1.date, " into tSQLcommand put "db2.table2.type, FORMAT(db2.table2.amount,2), " after tSQLcommand put "db1.table1.code1 " after tSQLcommand put "FROM db1.table1 " after tSQLcommand put "INNER JOIN db2.table2 ON db1.table1.code1 = db2.table2.code2 " after tSQLcommand put "WHERE db2.table2.amount > 1.00 " after tSQLcommand put "ORDER BY db1.table1.date; " after tSQLcommand put revDataFromQuery(tab, return, tConId, tSQLcommand) into tItems I did not find it necessary to give the databases alias names. In my situation, both databases are on the same machine and can be accessed using the same username/password. Hope this helps. Bob Date: Sun, 23 May 2010 18:19:24 -0300, From: Andre Garzia Subject: Re: Remote SQL databases I did it on my 6k thousand table database... It works fine for us. I have something like select Database1.* from DatabaseName1.Table1 as Database1, DatabaseName2.Table2 ... On Sun, May 23, 2010 at 4:34 PM, Mark Wieder wrote: > Bob- > > Sunday, May 23, 2010, 12:04:03 PM, you wrote: > >> Oh hello! You are saying you CAN do a join on a table that is not in >> the same database? > > Well, I haven't tried it, bu Mark S. implied that it was possible with > two different aliases... > > -- > -Mark Wieder > mwie...@ahsoftware.net ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
Re: Remote SQL databases
Hi Mark and all interested parties. Perhaps I failed to mention that the two databases do not know about each other. The SQL server doing the join has no access to the file based SQLite database it is doing a join against. I am pretty sure this is impossible to do like this. It's okay though because I thought of another method for accomplishing this. If I query the table I am importing data from for the key column data, I can create a query of records in the destination database for the same keys. I can then compare both results, and the source keys that I do not find in the destination keys I can insert into the destination database before I go on and do my real data updating. Another facet of this is that I want the Data Import/Merge feature to be highly scalable, so I am building in paging. I want to be able to use this functionality and also to make it available to others as a standalone stack or app in the future. I need to know the limits of Revolution when it comes to doing SQL queries. How big a data set, in terms of bytes of data, do you think would be the most I should have in my page size? I wonder if anyone on the list has ever really pushed the limits of Revolution data sets? I know it depends on how much memory Revolution has access to, because queries are returned as read only memory based data, and the conversion to a text string would effectively double that. Bob On May 21, 2010, at 5:04 PM, Mark Stuart wrote: > Hi Bob, > on Fri May 21 17:31:53 CDT 2010, Bob Sneidar wrote: >>> > So now I have to think about using joins. > << > Just preface the table name that's in the other database with the > database_alias_name.table_name: > > SELECT cus.Customer_Number, cus.Customer_Name, ctyp.Customer_Type_Name > FROM database1.customers AS cus LEFT OUTER JOIN > database2.customer_types as ctyp ON cus.Customer_Type = > ctyp.Customer_Type_ID > > HTH, > Mark Stuart > ___ > use-revolution mailing list > use-revolution@lists.runrev.com > Please visit this url to subscribe, unsubscribe and manage your subscription > preferences: > http://lists.runrev.com/mailman/listinfo/use-revolution ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
Re: Remote SQL databases
Gotcha. Teaches me to take my web guy's word for it. :-) Bob On May 21, 2010, at 5:04 PM, Mark Stuart wrote: > Hi Bob, > on Fri May 21 17:31:53 CDT 2010, Bob Sneidar wrote: >>> > So now I have to think about using joins. > << > Just preface the table name that's in the other database with the > database_alias_name.table_name: > > SELECT cus.Customer_Number, cus.Customer_Name, ctyp.Customer_Type_Name > FROM database1.customers AS cus LEFT OUTER JOIN > database2.customer_types as ctyp ON cus.Customer_Type = > ctyp.Customer_Type_ID > > HTH, > Mark Stuart > ___ > use-revolution mailing list > use-revolution@lists.runrev.com > Please visit this url to subscribe, unsubscribe and manage your subscription > preferences: > http://lists.runrev.com/mailman/listinfo/use-revolution ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
Re: Remote SQL databases
I did it on my 6k thousand table database... It works fine for us. I have something like select Database1.* from DatabaseName1.Table1 as Database1, DatabaseName2.Table2 ... On Sun, May 23, 2010 at 4:34 PM, Mark Wieder wrote: > Bob- > > Sunday, May 23, 2010, 12:04:03 PM, you wrote: > > > Oh hello! You are saying you CAN do a join on a table that is not in > > the same database? > > Well, I haven't tried it, bu Mark S. implied that it was possible with > two different aliases... > > -- > -Mark Wieder > mwie...@ahsoftware.net > > ___ > use-revolution mailing list > use-revolution@lists.runrev.com > Please visit this url to subscribe, unsubscribe and manage your > subscription preferences: > http://lists.runrev.com/mailman/listinfo/use-revolution > -- http://www.andregarzia.com All We Do Is Code. ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
Re: Remote SQL databases
Bob- Sunday, May 23, 2010, 12:04:03 PM, you wrote: > Oh hello! You are saying you CAN do a join on a table that is not in > the same database? Well, I haven't tried it, bu Mark S. implied that it was possible with two different aliases... -- -Mark Wieder mwie...@ahsoftware.net ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
Re: Remote SQL databases
Oh hello! You are saying you CAN do a join on a table that is not in the same database? Bob Sneidar IT Manager Calvary Chapel CM Sent from iPhone On May 22, 2010, at 18:35, Mark Wieder wrote: Mark- Friday, May 21, 2010, 5:04:57 PM, you wrote: SELECT cus.Customer_Number, cus.Customer_Name, ctyp.Customer_Type_Name FROM database1.customers AS cus LEFT OUTER JOIN database2.customer_types as ctyp ON cus.Customer_Type = ctyp.Customer_Type_ID Nice one - someone actually found a use for an outer join. I've wracked my brain trying to come up with a situation where I'd want to use one, and never come up with anything. -- -Mark Wieder mwie...@ahsoftware.net ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
Re: Remote SQL databases
Mark- Friday, May 21, 2010, 5:04:57 PM, you wrote: > SELECT cus.Customer_Number, cus.Customer_Name, ctyp.Customer_Type_Name > FROM database1.customers AS cus LEFT OUTER JOIN > database2.customer_types as ctyp ON cus.Customer_Type = > ctyp.Customer_Type_ID Nice one - someone actually found a use for an outer join. I've wracked my brain trying to come up with a situation where I'd want to use one, and never come up with anything. -- -Mark Wieder mwie...@ahsoftware.net ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
Remote SQL databases
Hi Bob, on Fri May 21 17:31:53 CDT 2010, Bob Sneidar wrote: >> So now I have to think about using joins. << Just preface the table name that's in the other database with the database_alias_name.table_name: SELECT cus.Customer_Number, cus.Customer_Name, ctyp.Customer_Type_Name FROM database1.customers AS cus LEFT OUTER JOIN database2.customer_types as ctyp ON cus.Customer_Type = ctyp.Customer_Type_ID HTH, Mark Stuart ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
Remote SQL databases
Hi all. Anyone have any idea how fast the queries to the On-Rev SQL databases are on average? I was using a method where I was looking up a key from one query of 100 records at a time and querying a mySql database at my On-Rev site ONE RECORD AT A TIME! I discovered that the queries were taking about a second, which now that I think about it, is about right, with internet lag and all. So now I have to think about using joins. My problem now becomes, I am looking up values from a table in one database, in a table tables residing in another database. Can I even create a join like that? Don't the tables have to reside in the same database to do a join like that? Barring that, I suppose I can compile a list of key values, and do a single query for records with key values in that string, then loop through and add the records that don't exist, but how to do that with a single query! Sheesh! If I were using Foxpro this would be child's play. I'm wondering if the Relational aspect of Trevor's sqlYoga would be able to do this, but again, I think I have to be working with two tables in the same database for that to work. Trevor? Bob ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
