search

hankinsoft / SQLPro

SQLPro bug & features tracking.
106 stars 28 forks source link

When I export JSON, the JSON isn't showing the data type used in the table. Specifically everything is being exported as a string, with quotes. #1000

Closed jacksonsox closed 2 months ago

jacksonsox commented 3 months ago

Describe the bug A clear and concise description of what the bug is.

To Reproduce Steps to reproduce the behavior:

  1. Go to '...'
  2. Click on '....'
  3. Scroll down to '....'
  4. See error

Expected behavior A clear and concise description of what you expected to happen.

Screenshots If applicable, add screenshots to help explain your problem.

Environment details (please complete the following information):

Additional context Add any other context about the problem here.

Issues will be closed until environmental details are provided.

If the above template is not completed, issues with be closed with the statement Issue has been closed. Please edit the initial post (or create a new issue) and follow the template. Once completed, the issue may be reopened.

jacksonsox commented 3 months ago

Description: When I export JSON, the JSON isn't showing the data type used in the table. Specifically everything is being exported as a string, with quotes.

Approximate Issue Start Date The JSON was exporting correctly as of 7/2/2024. The change started some time between 7/2 and today.

To Reproduce Steps to reproduce the behavior:

  1. Select your table of interest
  2. Right click then table
  3. Select "Import/Export"
  4. Select "Export"
  5. Select JSON
  6. Save the file with a name
  7. Open the resulting saved file
  8. Notice quotes on fields that are integer/numeric

Reproduced Table Layout CREATE TABLE IF NOT EXISTS "Figure_StarWars" ( Series_Unique_ID char(128) NOT NULL, Figure_Unique_ID char(128) PRIMARY KEY NOT NULL, ### Sort_Order integer(128) NOT NULL, Debut_Year integer(128) NOT NULL, Phase char(128) NOT NULL, Wave char(128) NOT NULL, Number char(128), Item_Number char(128), UPC char(128), Action_Figure char(128) NOT NULL, Action_Figure_Package_Name char(256) NOT NULL, Scene char(256), Find_Terms char(256) NOT NULL, Exclude_Terms char(256), Amazon_ASIN char(128), Amazon_Short_Link char(128), Walmart_SKU integer(128), Target_TCIN integer(128), Target_DPCI char(128), EEIN char(128), Retail_Date char(128), Retail_Price real(128), Added_Date char(128), Sort_Grouping char(128), Tags char(256), FOREIGN KEY (Series_Unique_ID) REFERENCES "Series" (Series_Unique_ID)

Previous Output Example (as of 7/2/2024) [ { "Tags" : null, "UPC" : "630509773930", "Figure_Unique_ID" : "3200100", "Exclude_Terms" : null, "Amazon_Short_Link" : "https:\/\/amzn.to\/2U1P5XD", "Target_DPCI" : "087-16-3978", "Find_Terms" : "Darth Vader", "Action_Figure_Package_Name" : "Darth Vader", "Wave" : "1", "Series_Unique_ID" : "SWBSHR", "Debut_Year" : 2019, "Number" : "2019", "Action_Figure" : "Darth Vader", "Target_TCIN" : 75560480, "Phase" : "Figure", "Item_Number" : "E4384", "Retail_Price" : 79.99, ### "Sort_Order" : 1, "Amazon_ASIN" : "B07D769KSP", "Sort_Grouping" : "Figure - 2019", "Walmart_SKU" : 329409902, "Retail_Date" : "08\/01\/2019", "EEIN" : null, "Scene" : "The Empire Strikes Back", "Added_Date" : null },

Output Example on 8/2/2024 [ { "Tags" : null, "UPC" : "630509773930", "Figure_Unique_ID" : "3200100", "Exclude_Terms" : null, "Amazon_Short_Link" : "https:\/\/amzn.to\/2U1P5XD", "Target_DPCI" : "087-16-3978", "Find_Terms" : "Darth Vader", "Action_Figure_Package_Name" : "Darth Vader", "Wave" : "1", "Series_Unique_ID" : "SWBSHR", "Debut_Year" : "2019", "Number" : "2019", "Action_Figure" : "Darth Vader", "Target_TCIN" : "75560480", "Phase" : "Figure", "Item_Number" : "E4384", "Retail_Price" : "79.99", ### "Sort_Order" : "1", "Amazon_ASIN" : "B07D769KSP", "Sort_Grouping" : "Figure - 2019", "Walmart_SKU" : "329409902", "Retail_Date" : "08\/01\/2019", "EEIN" : null, "Scene" : "The Empire Strikes Back", "Added_Date" : null },

Environment details:

Device: Mac OSX OS: Sonoma 14.5 SQLPro app Version Version 2024.61 (Build 4376) Installation source: App Store Target database server MySQL

jacksonsox commented 3 months ago

Exporting as XML looks similar with all values quoted. Although, I'm not sure what XML should look like.

row Series_Unique_ID="SWBSHR" Figure_Unique_ID="3200100" Sort_Order="1" Debut_Year="2019" Phase="Figure" Wave="1" Number="2019" Item_Number="E4384" UPC="630509773930" Action_Figure="Darth Vader" Action_Figure_Package_Name="Darth Vader" Scene="The Empire Strikes Back" Find_Terms="Darth Vader" Amazon_ASIN="B07D769KSP" Amazon_Short_Link="https://amzn.to/2U1P5XD" Walmart_SKU="329409902" Target_TCIN="75560480" Target_DPCI="087-16-3978" Retail_Date="08/01/2019" Retail_Price="79.99" Sort_Grouping="Figure - 2019"

I tried to export another table with integers and the export is also showing the JSON as strings. Similar to the example.

Example of the Data in the Table:

Screenshot 2024-08-02 at 6 10 03 PM
hankinsoft commented 3 months ago

Thank you for the details. I’m away for the weekend, but based on what I see here and the scripts you provided I should be able to fix this up pretty early next week.

jacksonsox commented 3 months ago

Thank you.

Jason Gloor Sent from my iPhone

On Aug 2, 2024, at 8:06 PM, Kyle Hankinson @.***> wrote:



Thank you for the details. I’m away for the weekend, but based on what I see here and the scripts you provided I should be able to fix this up pretty early next week.

— Reply to this email directly, view it on GitHubhttps://github.com/hankinsoft/SQLPro/issues/1000#issuecomment-2266285181, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AHYJNVTA4KLKA7ZM4BTMD33ZPQNINAVCNFSM6AAAAABL5IGLSCVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDENRWGI4DKMJYGE. You are receiving this because you authored the thread.Message ID: @.***>

jacksonsox commented 3 months ago

Hi Kyle Hankinson. Any thoughts on this JSON issue? I'm not able to put new data into my production app.

hankinsoft commented 3 months ago

Can you try the attached build and see if that sorts the issue? If so, I will get the App Store build updated as well.

SQLPro for MySQL.zip

jacksonsox commented 3 months ago

Thanks,       Does it matter that its the mysql version vs. me using sqllite?

From: Kyle Hankinson @.> Sent: Wednesday, August 7, 2024 8:01 AM To: hankinsoft/SQLPro @.> Cc: jacksonsox @.>; Author @.> Subject: Re: [hankinsoft/SQLPro] When I export JSON, the JSON isn't showing the data type used in the table. Specifically everything is being exported as a string, with quotes. (Issue #1000)

Can you try the attached build and see if that sorts the issue? If so, I will get the App Store build updated as well.

SQLPro for MySQL.ziphttps://github.com/user-attachments/files/16527611/SQLPro.for.MySQL.zip

— Reply to this email directly, view it on GitHubhttps://github.com/hankinsoft/SQLPro/issues/1000#issuecomment-2273419590, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AHYJNVV5TT3L2ZZNYUXXEF3ZQILDHAVCNFSM6AAAAABL5IGLSCVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDENZTGQYTSNJZGA. You are receiving this because you authored the thread.Message ID: @.***>

hankinsoft commented 3 months ago

Does it matter that its the mysql version vs. me using sqllite?

Sorry, I assumed you were using the MySQL version as it had listed

Target database server MySQL

You are using SQLPro for SQLite?

jacksonsox commented 3 months ago

Sorry for the mixup.

SQLPro for SQLite.

Jason

From: Kyle Hankinson @.> Sent: Wednesday, August 7, 2024 9:57 AM To: hankinsoft/SQLPro @.> Cc: jacksonsox @.>; Author @.> Subject: Re: [hankinsoft/SQLPro] When I export JSON, the JSON isn't showing the data type used in the table. Specifically everything is being exported as a string, with quotes. (Issue #1000)

Does it matter that its the mysql version vs. me using sqllite?

Sorry, I assumed you were using the MySQL version as it had listed

Target database server MySQL

You are using SQLPro for SQLite?

— Reply to this email directly, view it on GitHubhttps://github.com/hankinsoft/SQLPro/issues/1000#issuecomment-2273677877, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AHYJNVXZ24RBVVLDGVWH2QTZQIYWRAVCNFSM6AAAAABL5IGLSCVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDENZTGY3TOOBXG4. You are receiving this because you authored the thread.Message ID: @.***>

hankinsoft commented 3 months ago

Can you try:

SQLPro for SQLite.zip

And see if that sorts it out? If so, I'll get the App Store build submitted as well.

jacksonsox commented 3 months ago

I'm getting some interesting results. The order of the data seems wrong in the new version liked above (let's call it "new copy"). I have some screen shots of the of the original vs the new copy SQLite.zip. I've tried to add pictures that represent the changes. Let me know if you need more or want to chat.

Schema

Screenshot 2024-08-07 at 7 21 39 PM

Data in the DB

Original DB Dagta

New Copy Schema

New Copy Schema

New Copy Data in DB

New Copy Data

Output JSON Original and New Copy

Output Original and New data shifting to different columns
hankinsoft commented 3 months ago

This is very odd. So if you open the database in the old version the schema appears one way and in the new version it appears different?

jacksonsox commented 3 months ago

Hi Kyle, Can you send me a link to the SQLPro version from early July? That will allow me to paste my data while working on the new version. I’m going to try and export from an older version and import into the newest version. I’m hoping that the import will take care of the misalignment.

If not, I’ll work on getting you a trimmed down version of my DB/Data

Jason Gloor Sent from my iPhone

On Aug 8, 2024, at 5:41 AM, Kyle Hankinson @.***> wrote:



This is very odd. So if you open the database in the old version the schema appears one way and in the new version it appears different?

— Reply to this email directly, view it on GitHubhttps://github.com/hankinsoft/SQLPro/issues/1000#issuecomment-2275394328, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AHYJNVXZ5PUDFRDVIV743KTZQM4LVAVCNFSM6AAAAABL5IGLSCVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDENZVGM4TIMZSHA. You are receiving this because you authored the thread.Message ID: @.***>

hankinsoft commented 3 months ago

Sure, you can grab the previous version here:

https://www.sqlitepro.com/download.php

jacksonsox commented 3 months ago

What is the best way to send you the DB with data?

Jason Gloor Sent from my iPhone

On Aug 8, 2024, at 2:19 PM, Kyle Hankinson @.***> wrote:



Sure, you can grab the previous version here:

https://www.sqlitepro.com/download.php

— Reply to this email directly, view it on GitHubhttps://github.com/hankinsoft/SQLPro/issues/1000#issuecomment-2276406092, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AHYJNVR4BEY2PAVIZUVPYS3ZQOZD5AVCNFSM6AAAAABL5IGLSCVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDENZWGQYDMMBZGI. You are receiving this because you authored the thread.Message ID: @.***>

hankinsoft commented 3 months ago

You can email it to kyle@hankinsoft.com.

hankinsoft commented 2 months ago

Could you try this build:

SQLPro for SQLite_78.zip

I think this should sort it out.

jacksonsox commented 2 months ago

Hi Kyle,       Lools like the version below worked. Thank you for looking at this so quickly.

Jason G.

From: Kyle Hankinson @.> Sent: Friday, August 23, 2024 1:01 PM To: hankinsoft/SQLPro @.> Cc: jacksonsox @.>; Author @.> Subject: Re: [hankinsoft/SQLPro] When I export JSON, the JSON isn't showing the data type used in the table. Specifically everything is being exported as a string, with quotes. (Issue #1000)

Could you try this build:

SQLPro for SQLite_78.ziphttps://github.com/user-attachments/files/16732252/SQLPro.for.SQLite_78.zip

I think this should sort it out.

— Reply to this email directly, view it on GitHubhttps://github.com/hankinsoft/SQLPro/issues/1000#issuecomment-2307562100, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AHYJNVXMPUWSEAKPIEJPA6DZS52H5AVCNFSM6AAAAABL5IGLSCVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDGMBXGU3DEMJQGA. You are receiving this because you authored the thread.Message ID: @.***>

hankinsoft commented 2 months ago

Great, so just to confirm, this build sorts both the json export and the mixed up data you were seeing?

jacksonsox commented 2 months ago

Correct, Both issues have been corrected. Jason Gloor Sent from my iPhone

On Aug 26, 2024, at 7:25 AM, Kyle Hankinson @.***> wrote:



Great, so just to confirm, this build sorts both the json export and the mixed up data you were seeing?

— Reply to this email directly, view it on GitHubhttps://github.com/hankinsoft/SQLPro/issues/1000#issuecomment-2309974917, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AHYJNVWBOQROROUDGNN5DUDZTMGDVAVCNFSM6AAAAABL5IGLSCVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDGMBZHE3TIOJRG4. You are receiving this because you authored the thread.Message ID: @.***>

hankinsoft commented 2 months ago

Perfect, App Store build has been submitted as well. It usually takes ~24 hours or so for it to be approved.

jacksonsox commented 2 months ago

Thanks,       I updated to the new version on the App Store.

      Thanks again for the help.

Jason G.

From: Kyle Hankinson @.> Sent: Monday, August 26, 2024 11:58 AM To: hankinsoft/SQLPro @.> Cc: jacksonsox @.>; Author @.> Subject: Re: [hankinsoft/SQLPro] When I export JSON, the JSON isn't showing the data type used in the table. Specifically everything is being exported as a string, with quotes. (Issue #1000)

Perfect, App Store build has been submitted as well. It usually takes ~24 hours or so for it to be approved.

— Reply to this email directly, view it on GitHubhttps://github.com/hankinsoft/SQLPro/issues/1000#issuecomment-2310654654, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AHYJNVTEOORPDFR6VJITYE3ZTNNFBAVCNFSM6AAAAABL5IGLSCVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDGMJQGY2TINRVGQ. You are receiving this because you authored the thread.Message ID: @.***>