react-native-sqlite-storage
react-native-sqlite-storage copied to clipboard
andpor
What version is Sqlite using and should I be able to Upsert?
I am creating an app and I would like to be able to use the upsert syntax https://sqlite.org/lang_UPSERT.html
The UPSERT syntax was added to SQLite with version 3.24.0 (2018-06-04).
When I run my code (detailed below) I get a syntax error.
Is it possible for me to update (for android and ios) the SQLite version? or am I making some other mistake?
Expected Behavior
I am running this code
return this.getDatabase()
.then(db =>
db.executeSql(
`Insert into Field (
FarmId,
"Field-Unique-Id",
Name,
Coordinates,
Soil,
Crop,
"Previous-Crop",
"Soil-Test-P",
"Soil-Test-K",
"Regular-Manure",
"Recent-Grass",
Size) values(?,?,?,?,?,?,?,?,?,?,?,?)
ON CONFLICT("Field-Unique-Id")
Do
UPDATE SET
FarmId = excluded.FarmId,
Name = excluded.Name,
Coordinates = excluded.Coordinates,
Soil = excluded.Soil,
Crop = excluded.Crop,
"Previous-Crop" = excluded."Previous-Crop",
"Soil-Test-P" = excluded."Soil-Test-P",
"Soil-Test-K" = excluded."Soil-Test-K",
"Regular-Manure" = excluded."Regular-Manure",
"Recent-Grass" = excluded."Recent-Grass",
Size = excluded.Size`,
[
field.farmKey,
field.key,
field.name,
JSON.stringify(field.fieldCoordinates),
field.soilType,
field.cropType,
field.prevCropType,
field.soilTestP,
field.soilTestK,
field.organicManure,
field.recentGrass,
field.area
]
)
)
and I wish to insert my object!
Current Behavior
The error I get is
[Info] 02-04 13:34:30.590 6722 6762 W ReactNativeJS: Possible Unhandled Promise Rejection (id: 0): 02-04 13:34:30.590 6722 6762 W ReactNativeJS: Object { 02-04 13:34:30.590 6722 6762 W ReactNativeJS: "code": 0, 02-04 13:34:30.590 6722 6762 W ReactNativeJS: "message": "near "ON": syntax error (code 1): , while compiling: Insert into Field ( 02-04 13:34:30.590 6722 6762 W ReactNativeJS: FarmId, 02-04 13:34:30.590 6722 6762 W ReactNativeJS: "Field-Unique-Id", 02-04 13:34:30.590 6722 6762 W ReactNativeJS: Name, 02-04 13:34:30.590 6722 6762 W ReactNativeJS: Coordinates, 02-04 13:34:30.590 6722 6762 W ReactNativeJS: Soil, 02-04 13:34:30.590 6722 6762 W ReactNativeJS: Crop, 02-04 13:34:30.590 6722 6762 W ReactNativeJS: "Previous-Crop", 02-04 13:34:30.590 6722 6762 W ReactNativeJS: "Soil-Test-P", 02-04 13:34:30.590 6722 6762 W ReactNativeJS: "Soil-Test-K", 02-04 13:34:30.590 6722 6762 W ReactNativeJS: "Regular-Manure", 02-04 13:34:30.590 6722 6762 W ReactNativeJS: "Recent-Grass", 02-04 13:34:30.590 6722 6762 W ReactNativeJS: Size) values(?,?,?,?,?,?,?,?,?,?,?,?) 02-04 13:34:30.590 6722 6762 W ReactNativeJS: ON CONFLICT("Field-Unique-Id") 02-04 13:34:30.590 6722 6762 W ReactNativeJS: Do 02-04 13:34:30.590 6722 6762 W ReactNativeJS: UPDATE SET 02-04 13:34:30.590 6722 6762 W ReactNativeJS: FarmId = excluded.FarmId, 02-04 13:34:30.590 6722 6762 W ReactNativeJS: Name = excluded.Name, 02-04 13:34:30.590 6722 6762 W ReactNativeJS: Coordinates = excluded.Coordinates, 02-04 13:34:30.590 6722 6762 W ReactNativeJS: Soil = excluded.Soil, 02-04 13:34:30.590 6722 6762 W ReactNativeJS: Crop = excluded.Crop, 02-04 13:34:30.590 6722 6762 W ReactNativeJS: "Previous-Crop" = excluded."Previous-Crop", 02-04 13:34:30.590 6722 6762 W ReactNativeJS: "Soil-Test-P" = excluded."Soil-Test-P", 02-04 13:34:30.590 6722 6762 W ReactNativeJS: "Soil-Test-K" = excluded."Soil-Test-K", 02-04 13:34:30.590 6722 6762 W ReactNativeJS: "Regular-Manure" = excluded."Regular-Manure", 02-04 13:34:30.590 6722 6762 W ReactNativeJS: "Recent-Grass" = excluded."Recent-Grass", 02-04 13:34:30.590 6722 6762 W ReactNativeJS: Size = excluded.Size", 02-04 13:34:30.590 6722 6762 W ReactNativeJS: }
Context
I expect I can reproduce the Upsert behaviour in multiple hits on the database, but it is a nice syntax and I would like to use it.
Your Environment
"dependencies": {
"@expo/vector-icons": "^9.0.0",
"@types/react-native-sqlite-storage": "^3.3.1",
"global": "4.3.2",
"mobx": "4.5.0",
"mobx-react": "5.2.8",
"moment": "^2.24.0",
"native-base": "^2.11.0",
"react": "16.6.1",
"react-native": "0.57.7",
"react-native-datepicker": "1.7.2",
"react-native-maps": "https://github.com/react-community/react-native-maps.git",
"react-native-simple-store": "1.3.0",
"react-native-sqlite-storage": "^3.3.9",
"react-navigation": "2.14.0"
},
- IDE used: VSCode
- Link to your project: https://github.com/fo-am/iCrapAppPro (tho this database stuff is not committed in full yet!)
Debug logs
See above!
For android it's listed here: https://github.com/andpor/react-native-sqlite-storage/tree/master/src/android-native/libs
It's using 3.8.10.2 (updated 3 years ago) at the moment
Oh. that is old!
I guess that upgrading is more than dropping in new files from www.sqlite.org nightlies!
If someone will hold my hand I would consider attempting a pull request, but I have a deadline first so not for a few weeks on my side.
If others find this in some hopless google search in the future my current work around looks like this horror
return this.getDatabase()
.then(db =>
db.executeSql(
`Insert or Ignore Into Field (
FarmId,
"Field-Unique-Id",
Name,
Coordinates,
Soil,
Crop,
"Previous-Crop",
"Soil-Test-P",
"Soil-Test-K",
"Regular-Manure",
"Recent-Grass",
Size) values(?1,?2,?3,?4,?5,?6,?7,?8,?9,?10,?11,?12);
UPDATE Field SET
FarmId = ?1,
Name = ?3,
Coordinates= ?4,
Soil= ?5,
Crop= ?6,
"Previous-Crop"= ?7,
"Soil-Test-P"= ?8,
"Soil-Test-K"= ?9,
"Regular-Manure"= ?10,
"Recent-Grass"= ?11,
Size= ?12
where changes() = 0 and "Field-Unique-Id" = ?2;
`,
[
field.farmKey,
field.key,
field.name,
JSON.stringify(field.fieldCoordinates),
field.soilType,
field.cropType,
field.prevCropType,
field.soilTestP,
field.soilTestK,
field.organicManure,
field.recentGrass,
field.area
]
)
)
Here's a manual workaround to upgrade sqlite:
- https://github.com/andpor/react-native-sqlite-storage/issues/310#issuecomment-467603415
I will upgrade Android native libs to recent version shortly...armeabi will be eliminated though...
Can you test new-native-sqlite-libs branch (new native libs) and let me know how it works for you. This library does not provide custom sqlite libs and relies on what's on the device.
ON CONFLICT("Field-Unique-Id") Do UPDATE SET
working fine on ios but giving error on android
@aidanw Your workaround horror is awesome, working flawlessly! :heart_eyes:
