GDAL SQL syntax to add field an put values

Question

I try to make a new field in my attribute table and put in values from another field after converting from character to integer but can't get the right syntax. I run this as shell-script in MSYS.

cd D:/GIS_DataBase/CorineLC/shps_app_and_extr/
myfile=extr_and_app.shp
name=${myfile%.shp}
ogrinfo $myfile -sql "ALTER TABLE $name ADD COLUMN code_num int(3)"
ogrinfo $myfile -sql "UPDATE TABLE $name SET code_num = CONVERT(code_06 As int(3))"

Error Message (MSYS):

Kay@KAY-PC /c/users/kay/desktop/bash
$ sh calc_field_shp.sh
Warning 6: Unsupported column type 'int'. Defaulting to VARCHAR
INFO: Open of `extr_and_app.shp'
 using driver `ESRI Shapefile' successful.
ERROR 1: SQL Expression Parsing Error: syntax error
INFO: Open of `extr_and_app.shp'
 using driver `ESRI Shapefile' successful.

Edit - Another go with SQLite:

cd D:/GIS_DataBase/CorineLC/shps_app_and_extr/
myfile=extr_and_app.dbf
name=${myfile%.dbf}
ogrinfo $myfile -sql "ALTER TABLE $name DROP COLUMN code_num"
ogrinfo $myfile -sql "ALTER TABLE $name ADD COLUMN code_num integer(3)"
ogrinfo $myfile -dialect SQLite -sql "UPDATE $name SET code_num = CAST(code_06 As integer(3))"

Error message:

Kay@KAY-PC /c/users/kay/desktop/bash
$ sh calc_field_shp.sh
INFO: Open of `extr_and_app.dbf'
 using driver `ESRI Shapefile' successful.
INFO: Open of `extr_and_app.dbf'
 using driver `ESRI Shapefile' successful.
ERROR 1: SQL Expression Parsing Error: syntax error
INFO: Open of `extr_and_app.dbf'
 using driver `ESRI Shapefile' successful.

• R.K.: What error are you getting?
• Kay: @R.K., I added the error message to the op.
• dmci: have you tried CAST instead of CONVERT?
• Kay: I did without success..

Answer 1

Because UPDATE is not supported in OGR SQL, as you stated in a comment, you should update the table using the SQLite SQL dialect available in GDAL>= 1.10 with SQLite and SpatiaLite support:

ogrinfo $myfile -sql "ALTER TABLE $name ADD COLUMN code_num integer(3)"
ogrinfo $myfile -dialect SQLite -sql "UPDATE $name SET code_num = CAST(code_06 AS integer(3))"

Answer 2

You can try using the CAST operator as dmci has mentioned like so

ogrinfo $myfile -sql "UPDATE TABLE $name SET code_num = CAST(code_06 as int(3))"

The SQL dialect supported by OGR does not have CONVERT if recall correctly. You can check the docs for more info. Good luck!

• Kay: Seemingly UPDATE is not supported, see here: osgeo-org.1560.x6.nabble.com/… However, it should be in SQLite dialect but I had no luck with that, either..
• R.K.: What version of GDAL are you using?
• Kay: I'm using gdal-17
• scruss: The SQLite dialect needs GDAL/OGR 1.10.