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.
Closed this issue · 22 comments
Describe the bug
A clear and concise description of what the bug is.
To Reproduce
Steps to reproduce the behavior:
- Go to '...'
- Click on '....'
- Scroll down to '....'
- 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):
- Device: [e.g. iPhone6, MacBook]
- OS: [e.g. iOS8.1, macOS 11]
- SQLPro app Version [e.g. SQLPro Studio 2022.x] Please ensure you specify the full app name. (ex: SQLPro Studio, SQLPro for MySQL, etc).
- Installation source: [App Store, Website download, Setapp].
- Target database server [e.g. MySQL, PostgreSQL, Microsoft SQL Server, etc]
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.
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:
- Select your table of interest
- Right click then table
- Select "Import/Export"
- Select "Export"
- Select JSON
- Save the file with a name
- Open the resulting saved file
- 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
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:
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.
Hi Kyle Hankinson. Any thoughts on this JSON issue? I'm not able to put new data into my production app.
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.
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?
Can you try:
And see if that sorts it out? If so, I'll get the App Store build submitted as well.
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
Data in the DB
New Copy Schema
New Copy Data in DB
Output JSON Original and New Copy
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?
- what was the old version of the app?
- can you provide me with the database?
Sure, you can grab the previous version here:
You can email it to kyle@hankinsoft.com.
Great, so just to confirm, this build sorts both the json export and the mixed up data you were seeing?
Perfect, App Store build has been submitted as well. It usually takes ~24 hours or so for it to be approved.