Opened 4 years ago

Closed 6 months ago

#2834 closed defect (invalid)

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

Reported by: khe Owned by: grass-dev@…
Priority: normal Milestone: 7.0.7
Component: Database Version: 7.0.2
Keywords: v.in.ogr Cc:
CPU: Unspecified Platform: Linux

Description (last modified by martinl)

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.

Attachments (6)

Schoenbuch_weg.shp (38.1 KB) - added by khe 4 years ago.
shp-file
Schoenbuch_weg.2.shp (38.1 KB) - added by khe 4 years ago.
trail.dbf (144.6 KB) - added by khe 4 years ago.
trail.sbx (564 bytes) - added by khe 4 years ago.
trail.shp (38.1 KB) - added by khe 4 years ago.
trail.shx (2.4 KB) - added by khe 4 years ago.

Download all attachments as: .zip

Change History (24)

comment:1 Changed 4 years ago by khe

Milestone: 7.0.3Website

comment:2 Changed 4 years ago by martinl

Description: modified (diff)
Keywords: v.in.ogr added
Milestone: Website7.0.3

comment:3 in reply to:  description Changed 4 years ago by 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.

comment:4 Changed 4 years ago by 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...".

comment:5 Changed 4 years ago by mlennert

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

Changed 4 years ago by khe

Attachment: Schoenbuch_weg.shp added

shp-file

Changed 4 years ago by khe

Attachment: Schoenbuch_weg.2.shp added

comment:6 in reply to:  4 Changed 4 years ago by 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?

Changed 4 years ago by khe

Attachment: trail.dbf added

Changed 4 years ago by khe

Attachment: trail.sbx added

comment:7 Changed 4 years ago by 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.

Last edited 4 years ago by khe (previous) (diff)

comment:8 Changed 4 years ago by 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.

Changed 4 years ago by khe

Attachment: trail.shp added

Changed 4 years ago by khe

Attachment: trail.shx added

comment:9 Changed 4 years ago by 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.

comment:10 in reply to:  9 Changed 4 years ago by 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.

comment:11 in reply to:  9 Changed 4 years ago by 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.

comment:12 Changed 4 years ago by khe

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

Last edited 4 years ago by khe (previous) (diff)

comment:13 Changed 4 years ago by neteler

Milestone: 7.0.3

Ticket retargeted after milestone closed

comment:14 Changed 4 years ago by neteler

Milestone: 7.0.4

Ticket retargeted after 7.0.3 milestone closed

comment:15 Changed 3 years ago by martinl

Milestone: 7.0.47.0.5

comment:16 Changed 3 years ago by neteler

Milestone: 7.0.57.0.6

comment:17 Changed 20 months ago by neteler

Milestone: 7.0.67.0.7

comment:18 Changed 6 months ago by martinl

Resolution: invalid
Status: newclosed
Note: See TracTickets for help on using tickets.