How to Insert txt file into SQL table(DB2)

kolaG · #1 (**permalink**) 11-08-08, 23:16

Hi can anyone help me with a sample code that i can use to insert a txt file into an SQL database table in DB2.
Below is the file to be inserted:

1,00:22,63,132,73,142
1,00:50,116,155,126,165
1,01:35,206,182,216,192
1,02:01,233,197,243,207
1,05:14,218,233,228,243
1,07:44,244,261,254,271
1,13:26,272,256,282,266
1,18:14,285,228,298,239
1,18:25,356,238,366,248
1,18:57,496,346,506,356
2,01:07,221,116,231,126
2,01:18,232,74,242,84
2,01:50,196,75,206,85
2,02:01,129,123,139,133
2,12:50,113,157,123,167
2,13:20,107,183,117,193
2,15:58,192,214,202,224
2,16:45,207,185,217,195
2,18:14,202,145,212,155
2,20:22,150,130,160,140
3,02:48,303,62,313,72
3,04:25,361,94,371,104
3,06:18,295,219,305,229
3,09:46,356,239,366,249
3,15:26,392,164,402,174
3,17:20,429,182,439,192
3,22:48,446,242,456,252
3,22:14,507,230,517,240
3,23:05,464,297,474,307
3,23:48,423,355,433,365
4,10:02,40,294,50,304
4,12:18,59,320,69,330
4,15:22,169,280,179,290
4,15:58,194,216,204,226
4,18:03,227,227,237,237
4,18:14,285,229,295,239
4,20:33,358,99,368,109
4,20:45,416,127,426,137
4,21:06,578,216,588,226
4,22:04,539,359,549,369
5,03:13,501,42,511,52
5,03:15,426,56,436,66
5,04:25,361,93,371,103
5,06:51,539,189,549,199
5,07:44,506,233,516,243
5,07:48,401,263,411,273
5,09:10,267,204,277,214
5,12:14,310,125,320,135
5,17:28,233,74,243,84
5,18:44,193,74,203,84

I have this command which can only insert one row at a time:

HTML Code:

INSERT INTO TABLE(ID, T_IME, GEOMETRY) 
  VALUES (1, '12:22',DB2GSE.ST_Point('point(63 132,73 142)',1))

How can i modify this code to insert it all the above data at once.
Thanks

kolaG · #2 (**permalink**) 11-09-08, 06:44

Thanks. I manage to insert the whole row one by one using the insert command.
My problem now is to query where two points intersect. Here is my command:

HTML Code:

SELECT a.ID, b.ID, CAST(DB2GSE.ST_AsText(DB2GSE.ST_Intersection(a.geometry, b.geometry)) 
		 as VARCHAR(150)) Intersection
FROM ADB5681.MLTABLE  a, ADB5681.MLTABLE  b

But i am having the following error:
[quote]DB2 Database Error: ERROR [38SSR] [IBM][DB2/NT] SQL0443N Routine "DB2GSE.ST_INTERSECTION" (specific name "ST_INTERS") has returned an error SQLSTATE with diagnostic text "GSE3427N Incompatible SRSs.". SQLSTATE=38SSR
[/HTML]

The problem is a compatibilty issue of

B2GSE.ST_INTERSECTION
Can anyone help please
Thanks

Peter.Vanroose · #3 (**permalink**) 11-09-08, 15:01

Quote:

Originally Posted by kolaG

Below is the file to be inserted:
1,00:22,63,132,73,142
1,00:50,116,155,126,165
...
[CODE]INSERT INTO TABLE(ID, T_IME, GEOMETRY)
VALUES (1, '12:22',DB2GSE.ST_Point('point(63 132,73 142)',1))
[/HTML]
How can i modify this code to insert all the above data at once.

Use e.g. perl to convert the input to a sequence of valid INSERT statements:

Code:

perl -pe 's/(\d+),(\d\d:\d\d),(\d+),(\d+),(\d+),(\d+)/INSERT INTO TABLE(ID, T_IME, GEOMETRY) VALUES ($1, '\'\$2\'', DB2GSE.ST_Point('\''point($3 $4,$5 $6)'\'', 1)) ;/;' my_file.txt | db2 -tm

where "my_file.txt" is the name of the file containing the given lines.

stolze · #4 (**permalink**) 11-09-08, 16:23

The geometries that you inserted use a different Spatial Reference System (SRS), which explains the error message. What I cannot tell is why you use different SRSs. Can you show us the INSERT statements?

Internet Services

Web Development

Digital Marketing

Consumer Tech