[libreoffice-users] Re: Connection to SQL database
Le 18/01/2014 00:11, Carl Paulsen a écrit : Hi Carl, figured out a workaround. All seems to be fine now with a 13MB file, but next I've been given a 250MB file to open then export! I'm glad you managed to get there in the end :-) One word of warning - PHPMyAdmin via the file php.ini has a default cap on the size of data it can import in one write operation - this is done to prevent people crafting sql queries that could cause the server to keel over, so-called denial of service attacks. The default limit is something like 8Mb, so you might want to be wary of trying to import even your 13Mb file, not to mention a 250Mb file, via that method, as phpmyadmin will either fail, and/or truncate the data to the limit allowed. For big data imports, you really ought to be using the mysql command line. Alex -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: Connection to SQL database
Le 16/01/2014 14:45, Carl Paulsen a écrit : Hi Carl, Let's start at the source... You have access to a file containing DDL (data definition language) and DML (data manipulation language). This file has the extension .sql. Essentially, this file is a kind of dump from the mysql/mariadb database. The dump includes instructions about the database name or schema, the tables in the database, and the field types, as well as the corresponding statements that would allow you to insert that data into a corresponding mysql database server hosted elsewhere. First things first : - can you gain query access to the mysql/mariadb server from which the sql file originates ? If you can, then you should be able to export your data directly in CSV format, by querying the database and using the SELECT...INTO OUTFILE command, or an equivalent GUI function from an appropriate program (e.g. phpmyadmin, MySQL Query Browser, MySQL Navigator, Navicat, etc, etc) - if you do not have query access to the source of the data, then you are effectively reliant on the SQL file that you have been given. This means that you have several other ways of dealing with the data contained therein : (a) as it is a text file, you could use a script of your own making or if lucky, trawl for one on the net, to extract the data and output that to a CSV, Excel or other text-based file type of your choice - various languages are capable of this, Ruby, Python, Perl, PHP, etc, or you could probably even use bash/sed/awk ; (b) host the data on a locally accessible mysql server - to do that you would need to install mysql server and client programs. If console commands are not your thing, then you could use LO Base to connect, via one of the connector methods (extension, jdbc or odbc), to the mysql hosted database. The advantage of (b) is that you can do most of the work via the LO Base UI, once the connection to the mysql database has been set up and the data imported. Another advantage of solution (b) is that you can tailor your output via the GUI tools of LO Base, so that it meets the requirements of your Salesforce input. The advantage of (a) is that it operates directly on the content of your SQL file without having to go through the rigmarole of setting up a mysql server, but at the expense of having to learn how to manipulate text data. Note that solution (a) works well for data that is just text or numbers, but not so well for binary encoded data (although I imagine that solutions to handle this are also available). This means that you need to know what kind of data you are going to have to manage in that SQL file before you start trying to extract it. Salesforce. I thought I might be able to open or link to the data file I have through Base and export it. That's because I haven't really No, that is not directly possible, at least not in the format in which you have been given the data. Honestly, though, I don't understand what MySQL does. Does it open the data file, interpret the commands in it, and then allow the user to manipulate and/or display the data correctly (with the correct relational links)? And is that the only - or best - way to access the MySQl contains both a server and client programs. To simplify, the server hosts the data and serves it up against requests from the client program. Interfaces have been developed by third parties to enable the client program functions to be mapped to UIs, whether it be LO Base, MySQL Browser, MySQL Workbench, or any other number of GUI tools. The SQL file you have can be imported directly into a mysql server with the command I indicated. The server will interpret the statements in the SQL file and create a clone of the database schema, the tables, field definitions, and the insert the data into that database. You can then access the imported data and manipulate it as you would with a mysql hosted database that you had created yourself ab initio. Can I just use MySQL to generate CSV files (or some other format that Calc can open) for each of the tables in the file? At Yes, mysql has a SELECT...INTO OUTFILE query command that lets you do this (at least for CSV/TSV format) - it also allows you to specify certain, limited export options. This is done directly from the mysql command line console. However, there are GUI tools that make life much easier for you in this reqard : MySQL Workbench - possible outputs are : CSV, HTML, XML MySQL Browser (deprecated, but still functional) - CSV, HTML, XML, Excel (xslx) PHPMyAdmin - exports directly to CSV, other text formats, even Calc !! Importing data into mysql from a sql file : http://www.itworld.com/it-management/359857/3-ways-import-and-export-mysql-database Alex -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive:
Re: [libreoffice-users] Re: Connection to SQL database
Thanks yet again, Alex. A couple of things. First I don't have access to the server from which the file originated. So I've set up both MySQL/Server and XAMP on my Windows machine hoping to get one to work for me. I've used PHPMyAdmin before so I thought that would be best, but I can't get it to start. Unfortunately, I can't get Workbench to work either, in part b/c I don't understand it. I'm trying to load the database there (actually, working on the World sample database first), but can't seem to figure that out. Do I simply load the database, import it, reverse engineer it to create a schema, create a new schema or database and import into it, etc. etc.? Create a server connection? But don't I have to create the connection first? When I try that I get a failed to connect 10061 error. As you can see, it's a bit opaque to me. BTW, I tried your command at the command prompt but get a response about the command not being interpretable or something like that. But I just read that I maybe need to start the server and get to a mysql prompt??? Basically, I'm at a huge disadvantage b/c I don't understand ANY of the terms or tools. I'm watching a ton of videos and reading a ton of material. But being someone who has done a lot of teaching/tutoring, I have to say no one out there seems to know how to assume a user knows nothing but is still capable of learning. Too much is assumed in all the materials I find. Thankfully, I'm bright enough to plow my way through this and figure it out, but it will take a LONG time. Plus, my Workbench looks nothing like what is shown in the materials I've seen. In short, I'm trying b. because I can't do the programming without learning yet another tool and don't want to continue chasing after possible tools that end up not working. But at this point, I haven't been able to get the SQL server to load/clone the database (see my confusion above). I appreciate the link you sent re: importing. If I can get PHPMyAdmin to start I think I can do this. In XAMP all I get is a control panel that lets me start Apache and MySQL but there's no PHPMyAdmin option available and no apparent way to launch it from the start button. I'm reinstalling now in case I screwed up the first installation. I hope that works. Cheers, Carl On 1/17/14 5:55 AM, Alex Thurgood wrote: Le 16/01/2014 14:45, Carl Paulsen a écrit : Hi Carl, Let's start at the source... You have access to a file containing DDL (data definition language) and DML (data manipulation language). This file has the extension .sql. Essentially, this file is a kind of dump from the mysql/mariadb database. The dump includes instructions about the database name or schema, the tables in the database, and the field types, as well as the corresponding statements that would allow you to insert that data into a corresponding mysql database server hosted elsewhere. First things first : - can you gain query access to the mysql/mariadb server from which the sql file originates ? If you can, then you should be able to export your data directly in CSV format, by querying the database and using the SELECT...INTO OUTFILE command, or an equivalent GUI function from an appropriate program (e.g. phpmyadmin, MySQL Query Browser, MySQL Navigator, Navicat, etc, etc) - if you do not have query access to the source of the data, then you are effectively reliant on the SQL file that you have been given. This means that you have several other ways of dealing with the data contained therein : (a) as it is a text file, you could use a script of your own making or if lucky, trawl for one on the net, to extract the data and output that to a CSV, Excel or other text-based file type of your choice - various languages are capable of this, Ruby, Python, Perl, PHP, etc, or you could probably even use bash/sed/awk ; (b) host the data on a locally accessible mysql server - to do that you would need to install mysql server and client programs. If console commands are not your thing, then you could use LO Base to connect, via one of the connector methods (extension, jdbc or odbc), to the mysql hosted database. The advantage of (b) is that you can do most of the work via the LO Base UI, once the connection to the mysql database has been set up and the data imported. Another advantage of solution (b) is that you can tailor your output via the GUI tools of LO Base, so that it meets the requirements of your Salesforce input. The advantage of (a) is that it operates directly on the content of your SQL file without having to go through the rigmarole of setting up a mysql server, but at the expense of having to learn how to manipulate text data. Note that solution (a) works well for data that is just text or numbers, but not so well for binary encoded data (although I imagine that solutions to handle this are also available). This means that you need to know what kind of data you are going to have to manage in that
Re: [libreoffice-users] Re: Connection to SQL database
Update: Thanks everyone for your help. I finally figured out how to access phpMyAdmin and then figured out how to import the data file properly. I then encountered lots of errors trying to export it but ultimately figured out a workaround. All seems to be fine now with a 13MB file, but next I've been given a 250MB file to open then export! In any case, it ended up being easier to just work within MySQL and phpMyAdmin for this purpose, but I'll try to use Base to connect over the weekend just to learn it. Anyway, thanks again, especially to Alex and Jay. Carl On 1/17/14 1:19 PM, Carl Paulsen wrote: Thanks yet again, Alex. A couple of things. First I don't have access to the server from which the file originated. So I've set up both MySQL/Server and XAMP on my Windows machine hoping to get one to work for me. I've used PHPMyAdmin before so I thought that would be best, but I can't get it to start. Unfortunately, I can't get Workbench to work either, in part b/c I don't understand it. I'm trying to load the database there (actually, working on the World sample database first), but can't seem to figure that out. Do I simply load the database, import it, reverse engineer it to create a schema, create a new schema or database and import into it, etc. etc.? Create a server connection? But don't I have to create the connection first? When I try that I get a failed to connect 10061 error. As you can see, it's a bit opaque to me. BTW, I tried your command at the command prompt but get a response about the command not being interpretable or something like that. But I just read that I maybe need to start the server and get to a mysql prompt??? -- Carl Paulsen Dover, NH 03820 -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: Connection to SQL database
Peter West So his fame spread throughout all Syria... On 18 Jan 2014, at 4:19 am, Carl Paulsen carlpaul...@comcast.net wrote: Basically, I'm at a huge disadvantage b/c I don't understand ANY of the terms or tools. I'm watching a ton of videos and reading a ton of material. But being someone who has done a lot of teaching/tutoring, I have to say no one out there seems to know how to assume a user knows nothing but is still capable of learning. Too much is assumed in all the materials I find. Carl, this is an endemic problem in _all_ software documentation. I don't know whether it's worse in this area than others, but it is in any case horribly difficult to remember what it is like not to know anything. We don't observe the steps by which we come to know something; we are too busy struggling to understand. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: Connection to SQL database
Hi :) Thanks :) I guess that is part of my question. The original back-end of the database in this case is MySql/MariaDb. Base can normally use MySql/MariaDb as it's own back-end so there would seem to be 2 different routes that might be worth considering; 1. Attach Base directly to the existing MySql/MariaDb that is hosted on some web-site (or at least on an internet-facing server such as a Cloud). I know the back-end can either be on a local machine or on a local-area-network but could it work over the internet too? 2. Since the exported data is already laid out for MySql/MariaDb then just install MySql or MariaDb locally (onto the same desktop machine that is using Base) or onto a LAN file-share so that all machines can use Base (or other front-ends) to access the data. This seems to be the route Alex is suggesting except he goes further and suggests using a fairly local machine that already has MySql installed and just adding the exports as a new file on that machine. Carl, the o.p., seems to be thinking about the 3rd route and walking headlong into the type of troubles Jay just outlined. As i see it the problem with the 2nd or 3rd routes is that exporting data gives static data. As time goes on the original database gets updated with new data. So maybe at some point a new export might need to be grabbed and then somehow figure out a way to merge the updated data at this end with the updated data from over there. New records/rows are tricky enough but tracking changes in fields/columns in individual ancient records would be a complete nightmare. If it's a case of a single snapshot to rescue data from a sinking Cloud then none of that is a worry and the single export routes are perfect So it's really route 1 that i'm curious about and really in a yes/no way rather than in any detail. Carl doesn't seem to be thinking along those lines so this is a bit of a tangent that will probably crop up again in a future thread and be more relevant then. Regards from Tom :) On 16 January 2014 01:57, Jay Lozier jsloz...@gmail.com wrote: Tom, The initial format and some of the data types would need to be converted so the syntax matched to the new . This is a well known problem when migrating from one SQL database to another where there are added, non-standard data types. Jay On 01/15/2014 05:12 PM, Tom Davies wrote: Hi :) Is it likely to be possible to connect Base directly to the original data? So that instead of getting an export or a dump of the data it can be read dynamically? I know it is not what the o.p. is asking for but i often wonder. Regards from Tom :) On 15 January 2014 22:03, Alex Thurgood alex.thurg...@gmail.com wrote: Le 15/01/2014 22:08, Carl Paulsen a écrit : Hi Carl, ) ENGINE=MyISAM DEFAULT CHARSET=latin1; This is enough to tell us that the data came from a mysql database originally. MyISAM is the default engine for non-transactional MySQL databases : http://en.wikipedia.org/wiki/MyISAM In the sample table you give, the table/field definitions are particular to mysql, so if you try to run that sql with another db engine, e.g. in hsqldb via LO Tools SQL, it will fail because it will not recognise the field types you're trying to create (e.g. enum, mediumint. So, your best bet would be to import that into a mysql server, assuming you have one to hand and you have some kind of console/terminal access (localhost / same machine): mysql '/path/to/myfiletoimport.sql' optionally with -p if you require authentication for the user that is connecting to the mysql server : mysql -p '/path/to/myfileimport.sql' There are many web sites on the internet that are full of information on how to set up and import data into a mysql server. Alex -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted -- Jay Lozier jsloz...@gmail.com -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: Connection to SQL database
Hi :) Hmmm, actually i've only just realised that a 2nd grab from the 'proprietary' back-end data-tables might not be such a nightmare. Just keep a copy of the current export as it is so that the new grab can be compared against it. Any differences could then be added to the data-tables held fairly locally. Hopefully it's unlikely that different updates would happen to a single old field both here and there! Anyway, i guess my main question is could Base be used as the front-end for data tables (the back-end) that is online? Regards from Tom :) On 16 January 2014 10:32, Tom Davies tomc...@gmail.com wrote: Hi :) Thanks :) I guess that is part of my question. The original back-end of the database in this case is MySql/MariaDb. Base can normally use MySql/MariaDb as it's own back-end so there would seem to be 2 different routes that might be worth considering; 1. Attach Base directly to the existing MySql/MariaDb that is hosted on some web-site (or at least on an internet-facing server such as a Cloud). I know the back-end can either be on a local machine or on a local-area-network but could it work over the internet too? 2. Since the exported data is already laid out for MySql/MariaDb then just install MySql or MariaDb locally (onto the same desktop machine that is using Base) or onto a LAN file-share so that all machines can use Base (or other front-ends) to access the data. This seems to be the route Alex is suggesting except he goes further and suggests using a fairly local machine that already has MySql installed and just adding the exports as a new file on that machine. Carl, the o.p., seems to be thinking about the 3rd route and walking headlong into the type of troubles Jay just outlined. As i see it the problem with the 2nd or 3rd routes is that exporting data gives static data. As time goes on the original database gets updated with new data. So maybe at some point a new export might need to be grabbed and then somehow figure out a way to merge the updated data at this end with the updated data from over there. New records/rows are tricky enough but tracking changes in fields/columns in individual ancient records would be a complete nightmare. If it's a case of a single snapshot to rescue data from a sinking Cloud then none of that is a worry and the single export routes are perfect So it's really route 1 that i'm curious about and really in a yes/no way rather than in any detail. Carl doesn't seem to be thinking along those lines so this is a bit of a tangent that will probably crop up again in a future thread and be more relevant then. Regards from Tom :) On 16 January 2014 01:57, Jay Lozier jsloz...@gmail.com wrote: Tom, The initial format and some of the data types would need to be converted so the syntax matched to the new . This is a well known problem when migrating from one SQL database to another where there are added, non-standard data types. Jay On 01/15/2014 05:12 PM, Tom Davies wrote: Hi :) Is it likely to be possible to connect Base directly to the original data? So that instead of getting an export or a dump of the data it can be read dynamically? I know it is not what the o.p. is asking for but i often wonder. Regards from Tom :) On 15 January 2014 22:03, Alex Thurgood alex.thurg...@gmail.com wrote: Le 15/01/2014 22:08, Carl Paulsen a écrit : Hi Carl, ) ENGINE=MyISAM DEFAULT CHARSET=latin1; This is enough to tell us that the data came from a mysql database originally. MyISAM is the default engine for non-transactional MySQL databases : http://en.wikipedia.org/wiki/MyISAM In the sample table you give, the table/field definitions are particular to mysql, so if you try to run that sql with another db engine, e.g. in hsqldb via LO Tools SQL, it will fail because it will not recognise the field types you're trying to create (e.g. enum, mediumint. So, your best bet would be to import that into a mysql server, assuming you have one to hand and you have some kind of console/terminal access (localhost / same machine): mysql '/path/to/myfiletoimport.sql' optionally with -p if you require authentication for the user that is connecting to the mysql server : mysql -p '/path/to/myfileimport.sql' There are many web sites on the internet that are full of information on how to set up and import data into a mysql server. Alex -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted -- Jay Lozier jsloz...@gmail.com -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems?
Re: [libreoffice-users] Re: Connection to SQL database
Tom, Let me clarify what I need to do. I need the raw data to move to a new relational database. I'm not planning on having them work directly on spreadsheets. The data will go into Salesforce, but I don't know how to get the raw SQL data into Salesforce with the changes I need to make to it before the move without exporting it. I may have confused the discussion but not explaining my exact needs, but that's because I (wrongly) tend to think I can do almost everything, and only ask for help for specific issues I think I'm facing. There may well be other ways to do this, but I was focused on what I thought was the only way. So...what I need is to remove the data from the current database (apparently what I have is the best we can get), manipulate it so that certain fields meet the requirements of the new database, then move it into the new database. The only way I am aware of to get the data into the new (Salesforce) database is in CSV files. Field mapping is done via the first row of data (I need the field names), and I also need to figure out what fields contain what data for the mapping process. My goal was to use Base as a tool to extract the data, or perhaps even accomplish the manipulations I need to do in prep for the move to Salesforce. I thought I might be able to open or link to the data file I have through Base and export it. That's because I haven't really understood how to use SQL. I then realized I'd probably first need to open or host the SQL data through MySQL or another variant of that. Trouble is, I don't know how to do that. Alex gave some specific suggestions which I will try today. Honestly, though, I don't understand what MySQL does. Does it open the data file, interpret the commands in it, and then allow the user to manipulate and/or display the data correctly (with the correct relational links)? And is that the only - or best - way to access the data with Base? Can I just use MySQL to generate CSV files (or some other format that Calc can open) for each of the tables in the file? At this point, I'm not yet sure I have MySQL running properly, because it ain't like any packaged software I've used in the past (and it's been a LONG time since I've done and command-line work - like since the mid to late 1980s). Or maybe there's an option I haven't thought of to manipulate and move the data into Salesforce. At this point I'm all ears. If I need MySQL for this, can someone either point out a forum where I could get help with it or offer to coach me (off list if need be) through the process of opening the data and extracting it or connecting to Base? OK, thanks, and sorry for the tome. Carl On 1/16/14 5:41 AM, Tom Davies wrote: Hi :) Hmmm, actually i've only just realised that a 2nd grab from the 'proprietary' back-end data-tables might not be such a nightmare. Just keep a copy of the current export as it is so that the new grab can be compared against it. Any differences could then be added to the data-tables held fairly locally. Hopefully it's unlikely that different updates would happen to a single old field both here and there! Anyway, i guess my main question is could Base be used as the front-end for data tables (the back-end) that is online? Regards from Tom :) On 16 January 2014 10:32, Tom Davies tomc...@gmail.com wrote: Hi :) Thanks :) I guess that is part of my question. The original back-end of the database in this case is MySql/MariaDb. Base can normally use MySql/MariaDb as it's own back-end so there would seem to be 2 different routes that might be worth considering; 1. Attach Base directly to the existing MySql/MariaDb that is hosted on some web-site (or at least on an internet-facing server such as a Cloud). I know the back-end can either be on a local machine or on a local-area-network but could it work over the internet too? 2. Since the exported data is already laid out for MySql/MariaDb then just install MySql or MariaDb locally (onto the same desktop machine that is using Base) or onto a LAN file-share so that all machines can use Base (or other front-ends) to access the data. This seems to be the route Alex is suggesting except he goes further and suggests using a fairly local machine that already has MySql installed and just adding the exports as a new file on that machine. Carl, the o.p., seems to be thinking about the 3rd route and walking headlong into the type of troubles Jay just outlined. As i see it the problem with the 2nd or 3rd routes is that exporting data gives static data. As time goes on the original database gets updated with new data. So maybe at some point a new export might need to be grabbed and then somehow figure out a way to merge the updated data at this end with the updated data from over there. New records/rows are tricky enough but tracking changes in fields/columns in individual ancient records would be a complete nightmare. If it's a case of a single
Re: [libreoffice-users] Re: Connection to SQL database
Tom On 1 or 2 you need to the URL/path to the database, the port used if not the default (3306) and the log in credentials. The procedure with Base is the same. If you are do basic read/writes to the database then option 1 is desired. If you are doing development work Option 2 is highly recommended to avoid potentially corrupting the production database. Jay On 01/16/2014 05:32 AM, Tom Davies wrote: Hi :) Thanks :) I guess that is part of my question. The original back-end of the database in this case is MySql/MariaDb. Base can normally use MySql/MariaDb as it's own back-end so there would seem to be 2 different routes that might be worth considering; 1. Attach Base directly to the existing MySql/MariaDb that is hosted on some web-site (or at least on an internet-facing server such as a Cloud). I know the back-end can either be on a local machine or on a local-area-network but could it work over the internet too? 2. Since the exported data is already laid out for MySql/MariaDb then just install MySql or MariaDb locally (onto the same desktop machine that is using Base) or onto a LAN file-share so that all machines can use Base (or other front-ends) to access the data. This seems to be the route Alex is suggesting except he goes further and suggests using a fairly local machine that already has MySql installed and just adding the exports as a new file on that machine. Carl, the o.p., seems to be thinking about the 3rd route and walking headlong into the type of troubles Jay just outlined. As i see it the problem with the 2nd or 3rd routes is that exporting data gives static data. As time goes on the original database gets updated with new data. So maybe at some point a new export might need to be grabbed and then somehow figure out a way to merge the updated data at this end with the updated data from over there. New records/rows are tricky enough but tracking changes in fields/columns in individual ancient records would be a complete nightmare. If it's a case of a single snapshot to rescue data from a sinking Cloud then none of that is a worry and the single export routes are perfect So it's really route 1 that i'm curious about and really in a yes/no way rather than in any detail. Carl doesn't seem to be thinking along those lines so this is a bit of a tangent that will probably crop up again in a future thread and be more relevant then. Regards from Tom :) On 16 January 2014 01:57, Jay Lozier jsloz...@gmail.com wrote: Tom, The initial format and some of the data types would need to be converted so the syntax matched to the new . This is a well known problem when migrating from one SQL database to another where there are added, non-standard data types. Jay On 01/15/2014 05:12 PM, Tom Davies wrote: Hi :) Is it likely to be possible to connect Base directly to the original data? So that instead of getting an export or a dump of the data it can be read dynamically? I know it is not what the o.p. is asking for but i often wonder. Regards from Tom :) On 15 January 2014 22:03, Alex Thurgood alex.thurg...@gmail.com wrote: Le 15/01/2014 22:08, Carl Paulsen a écrit : Hi Carl, ) ENGINE=MyISAM DEFAULT CHARSET=latin1; This is enough to tell us that the data came from a mysql database originally. MyISAM is the default engine for non-transactional MySQL databases : http://en.wikipedia.org/wiki/MyISAM In the sample table you give, the table/field definitions are particular to mysql, so if you try to run that sql with another db engine, e.g. in hsqldb via LO Tools SQL, it will fail because it will not recognise the field types you're trying to create (e.g. enum, mediumint. So, your best bet would be to import that into a mysql server, assuming you have one to hand and you have some kind of console/terminal access (localhost / same machine): mysql '/path/to/myfiletoimport.sql' optionally with -p if you require authentication for the user that is connecting to the mysql server : mysql -p '/path/to/myfileimport.sql' There are many web sites on the internet that are full of information on how to set up and import data into a mysql server. Alex -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted -- Jay Lozier jsloz...@gmail.com -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted -- Jay Lozier jsloz...@gmail.com -- To unsubscribe e-mail to:
[libreoffice-users] Re: Connection to SQL database
Le 15/01/2014 16:51, Carl Paulsen a écrit : Hi Carl, An SQL file does not necessarily need to contain data, it could contain just a set of instructions for the database engine to execute. The SQL file is not a database as such, it is a generally text file containing instructions that a SQL db engine will understand and optionally data enclosed within INSERT statements. If your SQL file contains both data and instructions, it would be a good idea to know how it was produced, i.e. from which db engine the output came, as SQL dialects between different vendors, db engines etc, can differ according to their specific implementations of the SQL standards. The file itself should be loadable into any text editor, providing that it can handle the size of the file ! That can give you a better idea of what's inside. For example, it is all very well having just insert statements with associated data in a SQL file, but if the tables and db haven't been set up in advance, you won't be able to get very far with it. Some of those SQL statements might be directly executable from an empty LO Base file in the Tools SQL window, e.g. table creation statements, key definition statements, constraints, etc, but again that would depend on whether these statements were supported by the version of hsqldb that comes with a default embedded ODB database file. Alex -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: Connection to SQL database
Sent to just Alex T by mistake: On 1/15/14 1:41 PM, Carl Paulsen wrote: OK, this is VERY helpful. I decided to try reading the file in a text editor and that was instructive! The file is about 13MB, and from a quick read through, the vast majority seems to be actual data in a kind of comma-delimited form. First part seems to be instructions for setting up the tables including keys and fields/columns, etc. I don't pretend to understand a lot of it, but I think I get the basics. So... Given it has instructions for table setup and raw data in it, how do I open it in LO? Or would I be better off moving to MySQL or something else and trying that? Is there usually info in the file about which db engine produced the file, or is there any other way to determine that? Maybe I'll just see if I can open it directly in LO and hope for the best. If you have other thoughts, though, I'd be grateful to hear them. Cheers again, Carl On 1/15/14 12:57 PM, Alex Thurgood wrote: Le 15/01/2014 16:51, Carl Paulsen a écrit : Hi Carl, An SQL file does not necessarily need to contain data, it could contain just a set of instructions for the database engine to execute. The SQL file is not a database as such, it is a generally text file containing instructions that a SQL db engine will understand and optionally data enclosed within INSERT statements. If your SQL file contains both data and instructions, it would be a good idea to know how it was produced, i.e. from which db engine the output came, as SQL dialects between different vendors, db engines etc, can differ according to their specific implementations of the SQL standards. The file itself should be loadable into any text editor, providing that it can handle the size of the file ! That can give you a better idea of what's inside. For example, it is all very well having just insert statements with associated data in a SQL file, but if the tables and db haven't been set up in advance, you won't be able to get very far with it. Some of those SQL statements might be directly executable from an empty LO Base file in the Tools SQL window, e.g. table creation statements, key definition statements, constraints, etc, but again that would depend on whether these statements were supported by the version of hsqldb that comes with a default embedded ODB database file. Alex -- Carl Paulsen Dover, NH 03820 -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: Connection to SQL database
Below is an excerpt from the file referring to the donations_money
table. Obviously there are some ID fields, donation info fields
(Amount, Date, etc.), a primary key field, etc. Immediately after the
table, there's the dump of all the data in that table. I've only
included one or two records from the dump, but there are many hundreds
or even thousands of records.
There are quite a few tables in the file, many of which we don't need
(past logins under the old system, for example).
Any wisdom on how to convert this data into CSV files? I thought I'd
need to either open it with MySQL or connect to it via Base. If I
could convert it to CSV, I can do any cleanup and prep work in Calc or
some other tool I'm more familiar with.
Thanks again, everyone!
Excerpt:
-- Table structure for table `donation_money`
--
DROP TABLE IF EXISTS `donation_money`;
CREATE TABLE `donation_money` (
`dm_id` mediumint(9) NOT NULL auto_increment,
`dm_resource_id` mediumint(9) default NULL,
`dm_promise_id` mediumint(9) default '0',
`dm_date` date default NULL,
`dm_amount` float(10,2) default NULL,
`dm_receipt_number` varchar(15) default NULL,
`dm_donation_code` varchar(15) default NULL,
`dm_appeal_code` varchar(15) default 'NONE',
`dm_payment_code`
enum('CHE','CRD','CAS','ACH','STK','PAY','INK','OTH') default NULL,
`dm_mem_hon` enum('NUL','MEM','HON') default NULL,
`dm_mem_hon_who` varchar(40) default NULL,
`dm_foundation_code` varchar(15) default 'NONE',
`dm_in_kind_desc` varchar(60) default NULL,
`dm_comment` varchar(255) default NULL,
`dm_send_thank_you` tinyint(1) default '1',
`dm_in_thank_you_set` tinyint(1) default '0',
`dm_from_distribution_id` mediumint(9) default '0',
`dm_user_field1` varchar(50) default NULL,
`dm_created` timestamp NOT NULL default CURRENT_TIMESTAMP on update
CURRENT_TIMESTAMP,
`dm_created_by` varchar(15) default NULL,
`dm_modified` timestamp NOT NULL default '-00-00 00:00:00',
`dm_modified_by` varchar(15) default NULL,
PRIMARY KEY (`dm_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Dumping data for table `donation_money`
--
/*!4 ALTER TABLE `donation_money` DISABLE KEYS */;
LOCK TABLES `donation_money` WRITE;
INSERT INTO `donation_money` (`dm_id`, `dm_resource_id`,
`dm_promise_id`, `dm_date`, `dm_amount`, `dm_receipt_number`,
`dm_donation_code`, `dm_appeal_code`, `dm_payment_code`, `dm_mem_hon`,
`dm_mem_hon_who`, `dm_foundation_code`, `dm_in_kind_desc`, `dm_comment`,
`dm_send_thank_you`, `dm_in_thank_you_set`, `dm_from_distribution_id`,
`dm_user_field1`, `dm_created`, `dm_created_by`, `dm_modified`,
`dm_modified_by`) VALUES
(1,467,NULL,'2008-05-12',60.00,'03718C','MEMBERSHIP0','NONE','CRD','NUL','','NONE','','',1,1,NULL,NULL,'2008-05-14
15:39:14','jessi','2008-08-07
17:17:40','george'),(2,467,NULL,'2007-06-11',35.00,'04379C','MEMBERSHIP0','NONE','CRD','NUL','','NONE','','',
--
Carl Paulsen
Dover, NH 03820
--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: Connection to SQL database
Carl,
Depending on the program (this looks like the output from MySQL
Workbench) and the tables are MySQL/MariaDB - the MyISAM is specific
MySQL/MariaDB database engine. If you have MySQL or MariaDB and MySQL
Workbench installed you can import/restore the data.
The second dump has the data after value with each row in parentheses.
You could open the file in any text editor. The remove the first part
and the use find and replace to remove the parentheses and each '.
The program that generated the data probably allows you to get all the
rows and export/save them as a csv file.
Jay
On 01/15/2014 04:08 PM, Carl Paulsen wrote:
Below is an excerpt from the file referring to the donations_money
table. Obviously there are some ID fields, donation info fields
(Amount, Date, etc.), a primary key field, etc. Immediately after the
table, there's the dump of all the data in that table. I've only
included one or two records from the dump, but there are many
hundreds or even thousands of records.
There are quite a few tables in the file, many of which we don't need
(past logins under the old system, for example).
Any wisdom on how to convert this data into CSV files? I thought I'd
need to either open it with MySQL or connect to it via Base. If I
could convert it to CSV, I can do any cleanup and prep work in Calc or
some other tool I'm more familiar with.
Thanks again, everyone!
Excerpt:
-- Table structure for table `donation_money`
--
DROP TABLE IF EXISTS `donation_money`;
CREATE TABLE `donation_money` (
`dm_id` mediumint(9) NOT NULL auto_increment,
`dm_resource_id` mediumint(9) default NULL,
`dm_promise_id` mediumint(9) default '0',
`dm_date` date default NULL,
`dm_amount` float(10,2) default NULL,
`dm_receipt_number` varchar(15) default NULL,
`dm_donation_code` varchar(15) default NULL,
`dm_appeal_code` varchar(15) default 'NONE',
`dm_payment_code`
enum('CHE','CRD','CAS','ACH','STK','PAY','INK','OTH') default NULL,
`dm_mem_hon` enum('NUL','MEM','HON') default NULL,
`dm_mem_hon_who` varchar(40) default NULL,
`dm_foundation_code` varchar(15) default 'NONE',
`dm_in_kind_desc` varchar(60) default NULL,
`dm_comment` varchar(255) default NULL,
`dm_send_thank_you` tinyint(1) default '1',
`dm_in_thank_you_set` tinyint(1) default '0',
`dm_from_distribution_id` mediumint(9) default '0',
`dm_user_field1` varchar(50) default NULL,
`dm_created` timestamp NOT NULL default CURRENT_TIMESTAMP on update
CURRENT_TIMESTAMP,
`dm_created_by` varchar(15) default NULL,
`dm_modified` timestamp NOT NULL default '-00-00 00:00:00',
`dm_modified_by` varchar(15) default NULL,
PRIMARY KEY (`dm_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Dumping data for table `donation_money`
--
/*!4 ALTER TABLE `donation_money` DISABLE KEYS */;
LOCK TABLES `donation_money` WRITE;
INSERT INTO `donation_money` (`dm_id`, `dm_resource_id`,
`dm_promise_id`, `dm_date`, `dm_amount`, `dm_receipt_number`,
`dm_donation_code`, `dm_appeal_code`, `dm_payment_code`, `dm_mem_hon`,
`dm_mem_hon_who`, `dm_foundation_code`, `dm_in_kind_desc`,
`dm_comment`, `dm_send_thank_you`, `dm_in_thank_you_set`,
`dm_from_distribution_id`, `dm_user_field1`, `dm_created`,
`dm_created_by`, `dm_modified`, `dm_modified_by`) VALUES
(1,467,NULL,'2008-05-12',60.00,'03718C','MEMBERSHIP0','NONE','CRD','NUL','','NONE','','',1,1,NULL,NULL,'2008-05-14
15:39:14','jessi','2008-08-07
17:17:40','george'),(2,467,NULL,'2007-06-11',35.00,'04379C','MEMBERSHIP0','NONE','CRD','NUL','','NONE','','',
--
Jay Lozier
jsloz...@gmail.com
--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: Connection to SQL database
Le 15/01/2014 22:08, Carl Paulsen a écrit : Hi Carl, ) ENGINE=MyISAM DEFAULT CHARSET=latin1; This is enough to tell us that the data came from a mysql database originally. MyISAM is the default engine for non-transactional MySQL databases : http://en.wikipedia.org/wiki/MyISAM In the sample table you give, the table/field definitions are particular to mysql, so if you try to run that sql with another db engine, e.g. in hsqldb via LO Tools SQL, it will fail because it will not recognise the field types you're trying to create (e.g. enum, mediumint. So, your best bet would be to import that into a mysql server, assuming you have one to hand and you have some kind of console/terminal access (localhost / same machine): mysql '/path/to/myfiletoimport.sql' optionally with -p if you require authentication for the user that is connecting to the mysql server : mysql -p '/path/to/myfileimport.sql' There are many web sites on the internet that are full of information on how to set up and import data into a mysql server. Alex -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: Connection to SQL database
Hi :) Is it likely to be possible to connect Base directly to the original data? So that instead of getting an export or a dump of the data it can be read dynamically? I know it is not what the o.p. is asking for but i often wonder. Regards from Tom :) On 15 January 2014 22:03, Alex Thurgood alex.thurg...@gmail.com wrote: Le 15/01/2014 22:08, Carl Paulsen a écrit : Hi Carl, ) ENGINE=MyISAM DEFAULT CHARSET=latin1; This is enough to tell us that the data came from a mysql database originally. MyISAM is the default engine for non-transactional MySQL databases : http://en.wikipedia.org/wiki/MyISAM In the sample table you give, the table/field definitions are particular to mysql, so if you try to run that sql with another db engine, e.g. in hsqldb via LO Tools SQL, it will fail because it will not recognise the field types you're trying to create (e.g. enum, mediumint. So, your best bet would be to import that into a mysql server, assuming you have one to hand and you have some kind of console/terminal access (localhost / same machine): mysql '/path/to/myfiletoimport.sql' optionally with -p if you require authentication for the user that is connecting to the mysql server : mysql -p '/path/to/myfileimport.sql' There are many web sites on the internet that are full of information on how to set up and import data into a mysql server. Alex -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: Connection to SQL database
Tom, The initial format and some of the data types would need to be converted so the syntax matched to the new . This is a well known problem when migrating from one SQL database to another where there are added, non-standard data types. Jay On 01/15/2014 05:12 PM, Tom Davies wrote: Hi :) Is it likely to be possible to connect Base directly to the original data? So that instead of getting an export or a dump of the data it can be read dynamically? I know it is not what the o.p. is asking for but i often wonder. Regards from Tom :) On 15 January 2014 22:03, Alex Thurgood alex.thurg...@gmail.com wrote: Le 15/01/2014 22:08, Carl Paulsen a écrit : Hi Carl, ) ENGINE=MyISAM DEFAULT CHARSET=latin1; This is enough to tell us that the data came from a mysql database originally. MyISAM is the default engine for non-transactional MySQL databases : http://en.wikipedia.org/wiki/MyISAM In the sample table you give, the table/field definitions are particular to mysql, so if you try to run that sql with another db engine, e.g. in hsqldb via LO Tools SQL, it will fail because it will not recognise the field types you're trying to create (e.g. enum, mediumint. So, your best bet would be to import that into a mysql server, assuming you have one to hand and you have some kind of console/terminal access (localhost / same machine): mysql '/path/to/myfiletoimport.sql' optionally with -p if you require authentication for the user that is connecting to the mysql server : mysql -p '/path/to/myfileimport.sql' There are many web sites on the internet that are full of information on how to set up and import data into a mysql server. Alex -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted -- Jay Lozier jsloz...@gmail.com -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
