microbox Active Member Licensed User Longtime User Jun 30, 2020 #1 What will be the best approach to save records from sqlite table to MySql? The table consist of 6 columns and at least 50k rows. My initial idea is to convert the records into map or json file and use php script, but not really sure how to. Hope anyone here can help out. Thanks, Joe
What will be the best approach to save records from sqlite table to MySql? The table consist of 6 columns and at least 50k rows. My initial idea is to convert the records into map or json file and use php script, but not really sure how to. Hope anyone here can help out. Thanks, Joe
JohnC Expert Licensed User Longtime User Jun 30, 2020 #2 That sounds like a LOT of data. If it is a high megabytes worth of data and if you have control over the backend pages (meaning you can develop your own pages on the server), one thought might be to zip the data on the device, then send/FTP the ZIP file to the server and have the backend unzip and import the data. Upvote 0
That sounds like a LOT of data. If it is a high megabytes worth of data and if you have control over the backend pages (meaning you can develop your own pages on the server), one thought might be to zip the data on the device, then send/FTP the ZIP file to the server and have the backend unzip and import the data.
microbox Active Member Licensed User Longtime User Jun 30, 2020 #3 JohnC said: That sounds like a LOT of data. If it is a high megabytes worth of data and if you have control over the backend pages (meaning you can develop your own pages on the server), one thought might be to zip the data on the device, then send/FTP the ZIP file to the server and have the backend unzip and import the data. Click to expand... Thanks for the help, I found that the size of the file when converted to csv to almost 1Mb. Last edited: Jun 30, 2020 Upvote 0
JohnC said: That sounds like a LOT of data. If it is a high megabytes worth of data and if you have control over the backend pages (meaning you can develop your own pages on the server), one thought might be to zip the data on the device, then send/FTP the ZIP file to the server and have the backend unzip and import the data. Click to expand... Thanks for the help, I found that the size of the file when converted to csv to almost 1Mb.
JohnC Expert Licensed User Longtime User Jun 30, 2020 #4 microbox said: Thanks for the help, I found that the size of the file when converted to csv to almost 1Mb. Click to expand... That's not very big, so zipping it wont help much. Upvote 0
microbox said: Thanks for the help, I found that the size of the file when converted to csv to almost 1Mb. Click to expand... That's not very big, so zipping it wont help much.
drgottjr Expert Licensed User Longtime User Jun 30, 2020 #5 you don't say how much access you have to either database. in theory, you can dump your sqlite db to a text file, zip it, upload the file to your php server and create a table or db from the file. an sqlite dump should be readable by mysql. sqlite is relatively compliant with sql standards. there are lots of other possibilities, but they depend on where you are in the chain of command. if you only have access to a few canned php scripts from which to choose, and if your only access to the sqlite db is your phone, you're pretty reduced to uploading 50k records 1 by 1 to php. what are we looking at here? Upvote 0
you don't say how much access you have to either database. in theory, you can dump your sqlite db to a text file, zip it, upload the file to your php server and create a table or db from the file. an sqlite dump should be readable by mysql. sqlite is relatively compliant with sql standards. there are lots of other possibilities, but they depend on where you are in the chain of command. if you only have access to a few canned php scripts from which to choose, and if your only access to the sqlite db is your phone, you're pretty reduced to uploading 50k records 1 by 1 to php. what are we looking at here?
microbox Active Member Licensed User Longtime User Jun 30, 2020 #6 drgottjr said: you don't say how much access you have to either database. in theory, you can dump your sqlite db to a text file, zip it, upload the file to your php server and create a table or db from the file. an sqlite dump should be readable by mysql. sqlite is relatively compliant with sql standards. there are lots of other possibilities, but they depend on where you are in the chain of command. if you only have access to a few canned php scripts from which to choose, and if your only access to the sqlite db is your phone, you're pretty reduced to uploading 50k records 1 by 1 to php. what are we looking at here? Click to expand... To dump the selected sqlite table as text file and upload it to the server sounds okay. Just need to search the script for the php part. My idea was to convert the table records json file and upload it to the server and php code( I have access) will save it to the Mysql database but don't sure how to start and if it is better solution. I think uploading 1 by 1 will take too much time. Upvote 0
drgottjr said: you don't say how much access you have to either database. in theory, you can dump your sqlite db to a text file, zip it, upload the file to your php server and create a table or db from the file. an sqlite dump should be readable by mysql. sqlite is relatively compliant with sql standards. there are lots of other possibilities, but they depend on where you are in the chain of command. if you only have access to a few canned php scripts from which to choose, and if your only access to the sqlite db is your phone, you're pretty reduced to uploading 50k records 1 by 1 to php. what are we looking at here? Click to expand... To dump the selected sqlite table as text file and upload it to the server sounds okay. Just need to search the script for the php part. My idea was to convert the table records json file and upload it to the server and php code( I have access) will save it to the Mysql database but don't sure how to start and if it is better solution. I think uploading 1 by 1 will take too much time.
drgottjr Expert Licensed User Longtime User Jun 30, 2020 #7 if this is a one-shot deal, 1 by 1 is probably the easiest for you. i'm guessing you already have a php script which allows create (and/or insert) from an app. this will entail nothing or very little to learn. take a nap, and it's over. otherwise, you have to figure out how to "dump" sqlite to json (all on you to learn and test). then you have to figure out how to take a big json file and turn it into something php can convert to mysql. (again, on you to learn and test). i don't have my slide rule handy, but i think it might take you longer to research, learn and test the necessary new tricks than if you just started uploading the records right now. just sayin'... Upvote 0
if this is a one-shot deal, 1 by 1 is probably the easiest for you. i'm guessing you already have a php script which allows create (and/or insert) from an app. this will entail nothing or very little to learn. take a nap, and it's over. otherwise, you have to figure out how to "dump" sqlite to json (all on you to learn and test). then you have to figure out how to take a big json file and turn it into something php can convert to mysql. (again, on you to learn and test). i don't have my slide rule handy, but i think it might take you longer to research, learn and test the necessary new tricks than if you just started uploading the records right now. just sayin'...
microbox Active Member Licensed User Longtime User Jun 30, 2020 #8 drgottjr said: if this is a one-shot deal, 1 by 1 is probably the easiest for you. i'm guessing you already have a php script which allows create (and/or insert) from an app. this will entail nothing or very little to learn. take a nap, and it's over. otherwise, you have to figure out how to "dump" sqlite to json (all on you to learn and test). then you have to figure out how to take a big json file and turn it into something php can convert to mysql. (again, on you to learn and test). i don't have my slide rule handy, but i think it might take you longer to research, learn and test the necessary new tricks than if you just started uploading the records right now. just sayin'... Click to expand... Thanks for the time...I'll do some reading try out some test. I appreciate the effort and response. Upvote 0
drgottjr said: if this is a one-shot deal, 1 by 1 is probably the easiest for you. i'm guessing you already have a php script which allows create (and/or insert) from an app. this will entail nothing or very little to learn. take a nap, and it's over. otherwise, you have to figure out how to "dump" sqlite to json (all on you to learn and test). then you have to figure out how to take a big json file and turn it into something php can convert to mysql. (again, on you to learn and test). i don't have my slide rule handy, but i think it might take you longer to research, learn and test the necessary new tricks than if you just started uploading the records right now. just sayin'... Click to expand... Thanks for the time...I'll do some reading try out some test. I appreciate the effort and response.
Erel B4X founder Staff member Licensed User Longtime User Jul 1, 2020 #9 Why PHP? Use jRDC2. The performance is much better. Upvote 0
microbox Active Member Licensed User Longtime User Jul 1, 2020 #10 Erel said: Why PHP? Use jRDC2. The performance is much better. Click to expand... Thanks for the suggestion but for now uploading the file in csv to server is what I'm using. Upvote 0
Erel said: Why PHP? Use jRDC2. The performance is much better. Click to expand... Thanks for the suggestion but for now uploading the file in csv to server is what I'm using.
Biswajit Active Member Licensed User Longtime User Jul 1, 2020 #11 After uploading the CSV file run a foreach loop for every row and create a query string like this, SQL: INSERT INTO `table_name` (`field_1`, `field_2`, `field_3`) VALUES /*generate this from php foreach loop*/ ('data','data','data'), ('data','data','data'), ('data','data','data'), ... ('data','data','data'); After the foreach just run the query. So all of your data will be inserted from a single query. Upvote 0
After uploading the CSV file run a foreach loop for every row and create a query string like this, SQL: INSERT INTO `table_name` (`field_1`, `field_2`, `field_3`) VALUES /*generate this from php foreach loop*/ ('data','data','data'), ('data','data','data'), ('data','data','data'), ... ('data','data','data'); After the foreach just run the query. So all of your data will be inserted from a single query.
microbox Active Member Licensed User Longtime User Jul 1, 2020 #12 Biswajit said: After uploading the CSV file run a foreach loop for every row and create a query string like this, SQL: INSERT INTO `table_name` (`field_1`, `field_2`, `field_3`) VALUES /*generate this from php foreach loop*/ ('data','data','data'), ('data','data','data'), ('data','data','data'), ... ('data','data','data'); After the foreach just run the query. So all of your data will be inserted from a single query. Click to expand... I will give it a try to search and learn for it....thank you for helping out. Upvote 0
Biswajit said: After uploading the CSV file run a foreach loop for every row and create a query string like this, SQL: INSERT INTO `table_name` (`field_1`, `field_2`, `field_3`) VALUES /*generate this from php foreach loop*/ ('data','data','data'), ('data','data','data'), ('data','data','data'), ... ('data','data','data'); After the foreach just run the query. So all of your data will be inserted from a single query. Click to expand... I will give it a try to search and learn for it....thank you for helping out.
