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 an...@andregarzia.com 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 mwie...@ahsoftware.net 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
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
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
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
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
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
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 an...@andregarzia.com 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 mwie...@ahsoftware.net 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
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 mwie...@ahsoftware.net 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
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
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 mwie...@ahsoftware.net 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
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
