v.db.select comand works, sqlite, attribute table does not open

[khe]

Hey,

I imported vector data by using the Python Shell

g.run_command("v.in.ogr", dsn=loc2+"Schoenbuch_clip_strasse.shp", output=ort+"_strasse",overwrite=True, type="line",flags="o")
g.run_command("v.in.ogr", dsn=loc2+"Schoenbuch_gewaesserordnung.shp", output=ort+"_gewaesser",overwrite=True, type="line",flags="o")	
g.run_command("v.in.ogr", dsn=loc2+"Schoenbuch_gewasserachse.shp", output=ort+"_gewaesserachse",overwrite=True, type="line",flags="o")	
g.run_command("v.in.ogr", dsn=loc2+"Schoenbuch_weg.shp", output=ort+"_weg",overwrite=True, type="line",flags="o")

The database sqlite is connected (v.db.connect), I can open the vector layer, but cannot open the attribute table, but the command v.db.select works without having any problems.

It says: "DMBI-SQLite driver error: Unable to scan data: Fehler: Kann Daten nicht aus der Tabelle <S_weg> holen.

What could be the reason? Did anybody of you had similar problems?

Thanks for your help.

[martinl]

Replying to khe:

> g.run_command("v.in.ogr", dsn=loc2+"Schoenbuch_clip_strasse.shp", output=ort+"_strasse",overwrite=True, type="line",flags="o")

btw, why do you use o flag?

It says: "DMBI-SQLite driver error: Unable to scan data: Fehler: Kann Daten nicht aus der Tabelle <S_weg> holen.

Please exact command which fails.

[khe]

Hey Martin, thanks for your answer.

<btw, why do you use o flag?

I used the "o"-flag in order to override dataset projection (use location's projection).

The command itself ("v.in.ogr") is working. I can open the vector-data of the the streams, roads...

BUT I cannot open the attribute table by using the sqlite-database. I connected the sqlite-database, the command "v.db.select" is working.

When I want to open the attribute table a window opens saying "DMBI-SQLite driver error: Unable to scan data...".

[mlennert]

Would it be possible for you to share a shapefile that creates the issue, so that we can try to reproduce ?

[khe]

shp-file

[hellik]

Replying to khe:

Hey Martin, thanks for your answer.

<btw, why do you use o flag?

I used the "o"-flag in order to override dataset projection (use location's projection).

The command itself ("v.in.ogr") is working. I can open the vector-data of the the streams, roads...

BUT I cannot open the attribute table by using the sqlite-database. I connected the sqlite-database, the command "v.db.select" is working.

When I want to open the attribute table a window opens saying "DMBI-SQLite driver error: Unable to scan data...".

    Anhang Schoenbuch_weg.shp​ hinzugefügt

shp-file

according to ​https://en.wikipedia.org/wiki/Shapefile#Overview for a working shapefile following files are needed at least:

    .shp — shape format; the feature geometry itself
    .shx — shape index format; a positional index of the feature geometry to allow seeking forwards and backwards quickly
    .dbf — attribute format; columnar attributes for each shape, in dBase IV format

could you post all 3 files (zipped) to test?

[khe]

Thanks for this advice. Do you think I cannot open the attribute table, because I only imported the .shp-format? Why does it work by using the dbf-database?

<according to ​​https://en.wikipedia.org/wiki/Shapefile#Overview for a working shapefile following <files are needed at least:

Thanks for your help.

[pvanbosgeo]

I cannot open the shapefile (the three files) you send in QGIS or GRASS. Probably because the *.shx file is missing. But also note that the three files (.shp, .shx, .dbf) should have the same name. The .shp file you shared has another name than the .dbf and .sbx files.

[mlennert]

I can confirm your error with the trail shapefile, although for me v.db.select also fails:

v.db.select trailcat|object_id|gml_id|objektart|objektname|funktion_i|funktion_n|befestigun|befestig_1|art_id|art_name|breite|eigenname|kennung|gml_id_unt|unten_name|klasse|beginn|ende|SHAPE_STLe
DBMI-SQLite erreur de pilote :
Unable to scan date: 

DBMI-SQLite erreur de pilote :
Unable to scan date: 

ERREUR :Unable to fetch data from table <trail>

Setting DEBUG to 3 I get the following:

[...]
D3/3: col: SHAPE_STLe, nkcols 19, litetype : 2, sqltype 6
cat|object_id|gml_id|objektart|objektname|funktion_i|funktion_n|befestigun|befestig_1|art_id|art_name|breite|eigenname|kennung|gml_id_unt|unten_name|klasse|beginn|ende|SHAPE_STLe
D3/3: fetch row = -1
D3/3: col 0, litetype 1, sqltype 3: val = '1'
D3/3: col 1, litetype 3, sqltype 1: val = '0c6d5762-d8cb-4554-a559-45010a046a4d'
D3/3: col 2, litetype 3, sqltype 1: val = 'DEBWB0010000LRl3'
D3/3: col 3, litetype 1, sqltype 3: val = '42008'
D3/3: col 4, litetype 3, sqltype 1: val = 'Fahrwegachse'
D3/3: col 5, litetype 1, sqltype 3: val = '5211'
D3/3: col 6, litetype 3, sqltype 1: val = 'Hauptwirtschaftsweg'
D3/3: col 7, litetype 1, sqltype 3: val = '0'
D3/3: col 8, litetype 3, sqltype 1: val = ''
D3/3: col 9, litetype 1, sqltype 3: val = '0'
D3/3: col 10, litetype 3, sqltype 1: val = ''
D3/3: col 11, litetype 2, sqltype 6: val = '6.0'
D3/3: col 12, litetype 3, sqltype 1: val = '; ForstBW'
D3/3: col 13, litetype 3, sqltype 1: val = ''
D3/3: col 14, litetype 3, sqltype 1: val = 'DEBWB0010000BTEi'
D3/3: col 15, litetype 3, sqltype 1: val = 'Brücke'
D3/3: col 16, litetype 1, sqltype 3: val = '11190'
D3/3: col 17, litetype 3, sqltype 9: val = '2014-09-24'
D3/3: sqlite fetched date: <2014-09-24>
D3/3: col 18, litetype 3, sqltype 9: val = ''
D3/3: sqlite fetched date: <>
DBMI-SQLite erreur de pilote :
Unable to scan date: 

DBMI-SQLite erreur de pilote :
Unable to scan date: 

ERREUR :Unable to fetch data from table <trail>

I can see that there is a problem with the 19th column which is 'ende'. The programme cannot read the column as a date. This column is empty, but it is not considered as NULL (no value), but as (empty string value).

I tried setting the column explicitely to NULL using v.db.update:

v.db.update trail col=ende value=NULL

but I still get the same error with v.db.select.

However, when I set the column to NULL using db.execute

db.execute sql="UPDATE trail SET ende=NULL"

v.db.select runs as expected.

So, there seems to be a problem with the NULL handling. When I open the shapefile in QGIS, the ende column values are all marked as 'NULL'. Somehow, either v.in.ogr or the sqlite driver seems to create a confusion between NULL and an empty string.

[mlennert]

Replying to mlennert:

I tried setting the column explicitely to NULL using v.db.update:

v.db.update trail col=ende value=NULL

but I still get the same error with v.db.select.

See #2850 for the relevant bug report.

[mlennert]

Replying to mlennert:

So, there seems to be a problem with the NULL handling. When I open the shapefile in QGIS, the ende column values are all marked as 'NULL'. Somehow, either v.in.ogr

See #2851.

[khe]

Thank you so much. I would not have been able to solve the problem on my own. Have a nice day.

[neteler]

Ticket retargeted after milestone closed

[neteler]

Ticket retargeted after 7.0.3 milestone closed