I'm using trying to use the library. I can select the fields but I can't export it to the CSV file or I do not understand it correctly.

func main() {
	trd := trdsql.NewTRDSQL(
		trdsql.NewImporter(trdsql.InDelimiter(",")),
		trdsql.NewExporter(trdsql.NewWriter()),
	)
	err := trd.Exec("SELECT COUNT(*) FROM sample.csv")
	if err != nil {
		log.Fatal(err)
	}
}

How can I output the result to CSV?

When for example you have a source file like this

"col1","col2" "dat1","dat2"

And output it as -ocsv the results are

col1,col2 dat1,dat2

You can right now configure the separator character with -od A similar one for quotes could be added

When a sufficiently large amount of data is passed to trdsql configured to use SQLite, an error is thrown indicating that there is no active transaction to commit.

Here's a demonstration. Although pulling from /dev/urandom, the behavior was consistent. Worked fine for any number less than or equal to 78768, and threw the error for larger numbers. (I would not be surprised if the cutoff number were different in every environment though.)

for byteCnt in 78768 78769
do
	dd if=/dev/urandom bs=$byteCnt count=1 |od -tx1 |sed 's/ /\x0a/g;' |
	trdsql -oat 'select count(*) as RecCnt, c1 as Val from - group by c1 order by count(*) desc limit 5';
done;

1+0 records in
1+0 records out
78768 bytes (79 kB, 77 KiB) copied, 0.0055778 s, 14.1 MB/s
+--------+-----+
| RecCnt | Val |
+--------+-----+
|    353 | a1  |
|    348 |  52 |
|    348 | d8  |
|    347 | 8a  |
|    345 |  81 |
+--------+-----+
1+0 records in
1+0 records out
78769 bytes (79 kB, 77 KiB) copied, 0.0052549 s, 15.0 MB/s
+--------+-----+
| RecCnt | Val |
+--------+-----+
+--------+-----+
2019/10/09 16:26:31 ERROR(COMMIT):cannot commit - no transaction is active

I looked briefly at the relevant code but didn't see anything alarming...

• trdsql.go: Exec()
• importer.go: ImportFile()
• database.go: Import(), insertImport()

I am working on a medium-large open data set of Belgian companies, which is freely downloadable at https://kbopub.economie.fgov.be/kbo-open-data/login

I am trying to execute following query to denormalize data into 1 file and it works fine when the output target is CSV, but nothing happens when outputting JSON:

docker run --rm -it -v $(pwd):/tmp noborus/trdsql -ojson -ih " \
SELECT * FROM /tmp/enterprise.csv as e \
LEFT JOIN /tmp/address.csv as a ON (e.EnterpriseNumber=a.EntityNumber) \
LEFT JOIN /tmp/contact.csv as c ON (e.EnterpriseNumber=c.EntityNumber) \
LEFT JOIN /tmp/denomination.csv as d ON (e.EnterpriseNumber=d.EntityNumber) \
";

Any ideas?

func main() {
	jsonString := `
{
  "deviceInfo": {
    "deviceName": "设备3",
    "edgeDeviceName": "设备three",
    "topic": "GG2",
    "encryption": false,
    "compression": false
  },
  "telemetry": {
    "ts": 1585194439000,
    "aa": 6,
    "bb": 10,
    "OPC温度": 10370,
    "OPC湿度": "86",
    "OPC电量": true,
	"staType":0
  }
}
`
	trdsql.EnableDebug()
	r := bytes.NewBufferString(jsonString)
	importer, err := trdsql.NewBufferImporter("test", r, trdsql.InFormat(trdsql.JSON))
	if err != nil {
		log.Fatal(err)
	}
	writer := trdsql.NewWriter(trdsql.OutFormat(trdsql.VF))
	trd := trdsql.NewTRDSQL(importer, trdsql.NewExporter(writer))

	err = trd.Exec("SELECT telemetry FROM test where telemetry.aa > 3")
	if err != nil {
		log.Fatal(err)
	}
}

Out :

2021/10/20 10:54:13 driver: sqlite3, dsn: 
2021/10/20 10:54:13 CREATE TEMPORARY TABLE test ( `deviceInfo` text, `telemetry` text );
2021/10/20 10:54:13 INSERT INTO test (`deviceInfo`, `telemetry`) VALUES (?,?);
2021/10/20 10:54:13 SELECT telemetry FROM test where telemetry.aa > 3
2021/10/20 10:54:13 export: no such column: telemetry.aa [SELECT telemetry FROM test where telemetry.aa > 3]
exit status 1

When using the join from the example, the json output is different than the csv output (only the latter is correct)

❯ cat hist.csv
1,2017-7-10
2,2017-7-10
2,2017-7-11%

❯ cat user.csv
1,userA
2,userB%

❯ docker run --rm -it -v $(pwd):/tmp noborus/trdsql "SELECT u.c1,u.c2,h.c2 FROM /tmp/user.csv as u LEFT JOIN /tmp/hist.csv as h ON(u.c1=h.c1)"
1,userA,2017-7-10
2,userB,2017-7-10
2,userB,2017-7-11


❯ docker run --rm -it -v $(pwd):/tmp noborus/trdsql -ojson "SELECT u.c1,u.c2,h.c2 FROM /tmp/user.csv as u LEFT JOIN /tmp/hist.csv as h ON(u.c1=h.c1)"
[
  {
    "c1": "1",
    "c2": "2017-7-10"
  },
  {
    "c1": "2",
    "c2": "2017-7-10"
  },
  {
    "c1": "2",
    "c2": "2017-7-11"
  }
]

Hardcoded values containing a dash are unexpectedly output with backquotes when selecting from

• stdin
• a filename containing one or more dashes

Table data containing dashes is correctly output without any added backquotes.

I can work around it by enclosing my filenames in quotes when selecting directly from them, but that same method does not work to quote stdin.

Some examples:

$ FILE=/tmp/foo-bar.csv  # filename containing a dash
$ echo "x,y-z" > $FILE
$ cat $FILE | trdsql -oat "select '$FILE' as stdin_quoted_dashes, * from -"                                                                                                                                                             
+---------------------+----+-----+
| stdin_quoted_dashes | c1 | c2  |
+---------------------+----+-----+
| /tmp/foo`-`bar.csv  | x  | y-z |
+---------------------+----+-----+

$ trdsql -oat "select '$FILE' as unquoted_file_full_string_quoted, * from $FILE"                                                                                                                                                        
+----------------------------------+----+-----+
| unquoted_file_full_string_quoted | c1 | c2  |
+----------------------------------+----+-----+
| `/tmp/foo-bar.csv`               | x  | y-z |
+----------------------------------+----+-----+

$ trdsql -oat "select '$FILE' as quoted_file_ok, * from \"$FILE\""                                                                                                                                                                      
+------------------+----+-----+
|  quoted_file_ok  | c1 | c2  |
+------------------+----+-----+
| /tmp/foo-bar.csv | x  | y-z |
+------------------+----+-----+

$ # but can't use the same workaround for stdin:(
$ cat $FILE | trdsql -oat "select '$FILE' as quoted_stdin, * from \"-\""                                                                                                                                                                
2019/08/26 08:40:46 ERROR(EXPORT):SQL:no such table: -
[select '/tmp/foo-bar.csv' as quoted_stdin, * from "-"]

$ uname -srvmpio
Linux 4.9.184-linuxkit #1 SMP Tue Jul 2 22:58:16 UTC 2019 x86_64 x86_64 x86_64 GNU/Linux

$ trdsql --version
0.6.3

I would like to use this as a dependency of my own application, however I cannot use it as such because all the code is in the main package.

Please move supporting code to it's own package and reference from your main.go so this can be used from other go programs.

Moreover, structs appear to use all private fields, so those would need to be updated as well.

Please also provide an option to supply an input stream to the TRDSQL struct, instead of assuming a file.

When querying a tab-delimited file, if there's an empty column, the rest of the row shifts over one position. Works fine as long as there is data in every column.

echo 'a,b,c,d 1,2,3,4 5,,,8 9,A,B,C' | sed 's/,/\t/g; s/ /\n/g;' |trdsql -ih -id '\t' -oat 'select * from -'

+---+---+---+---+
| a | b | c | d |
+---+---+---+---+
| 1 | 2 | 3 | 4 |
| 5 | 8 |   |   | <-- value from column "d" is output under column "b"
| 9 | A | B | C |
+---+---+---+---+

For completeness, I tested all the ASCII values 1-126 as delimiters. The problem only occurs for a few values:

• 9 (Horizontal Tab)
• 11 (Vertical Tab)
• 12 (Form Feed)
• 32 (Space)

for ord in $(seq 1 126)
do
	[ $ord -eq 10 ] && continue; # linefeed
	[ $ord -eq 13 ] && continue; # carriage return
	[ $ord -eq 34 ] && continue; # double quote
	[ $ord -eq 39 ] && continue; # single quote
	[ $ord -eq 49 ] && continue; # the number 1 (our data below)

	delim=$(printf \\$(printf '%03o' $ord));
	[ $ord -eq 92 ] && delim="\\$delim";

	sedDelim=$delim;
	[ "$delim" = "/" ] && sedDelim="\/";
	[ "$delim" = "&" ] && sedDelim="\&";

	output=$(
		echo "1,,1" | sed "s/,/$sedDelim/g;" |
		trdsql -id "$delim" 'select * from -';
	);

	[ "$output" = "1,,1" ] || echo "$output for ord $ord";
done;

1,1 for ord 9
1,1 for ord 11
1,1 for ord 12
1,1 for ord 32

While I've never encountered a file delimited by VT, FF or Space, I regularly encounter tab delimited files, so this is a fairly important issue.

this is my dockerfile for test.

But the mirror image is still huge, about 800M

FROM --platform=$BUILDPLATFORM golang:1.16 as builder
ENV TZ=Asia/Shanghai LANG="C.UTF-8"
ARG TARGETARCH
ARG TARGETOS

WORKDIR /workspace
# Copy the Go Modules manifests
COPY go.mod go.mod
COPY go.sum go.sum
ENV GO111MODULE=on
ENV GOPROXY=https://goproxy.io,direct

# Copy the go source
COPY query query

# Build
RUN CGO_ENABLED=1 GOOS=$TARGETOS GOARCH=$TARGETARCH go build --tags='json1' -a -o queryd query/cmd/main.go

FROM golang:1.16-buster
WORKDIR /
COPY --from=builder /workspace/queryd .
ENTRYPOINT ["/queryd"]

Hi, first of all thank you for this great tool.

I'm trying to use the documentation page to reply JSON_EXTRACT example, My input is

[
  {
    "id": 1,
    "name": "Drolet",
    "attribute": { "country": "Maldives", "color": "burlywood" }
  },
  {
    "id": 2,
    "name": "Shelly",
    "attribute": { "country": "Yemen", "color": "plum" }
  },
  {
    "id": 3,
    "name": "Tuck",
    "attribute": { "country": "Mayotte", "color": "antiquewhite" }
  }
]

If I run the below command I have nothing.

trdsql -ijson "SELECT id, name, JSON_EXTRACT(attribute,'$country'), JSON_EXTRACT(attribute,'$color') FROM sample2.json"

I'm using trdsql version v0.9.0.

What's wrong in my command?

Thank you

Even if I run just trdsql -version it exists with a 137 exit code instantly. Any ideas? I'm using 0.10.0 and have installed (and tried to reinstall it already) via Homebrew.

I have used an older version in the past without issues. It's not a memory problem on my computer as I have plenty of RAM available.

~ ❯ trdsql -version                                                                                                                                                                                                        ✘ KILL 05:30:11 PM
[1]    16749 killed     trdsql -version

Add https://github.com/multiprocessio/go-sqlite3-stdlib to the driver with the name "sqlite3_ext". The following examples are possible:

trdsql -driver sqlite3_ext "SELECT repeat('x', 2)"

Resolved #140.

Support for DuckDB (https://duckdb.org/) would be great. A quick search shows that there is a Go SQL driver for it: https://github.com/marcboeker/go-duckdb (not sure if it is compatible with trdsql).

Please support multiple queries. Currently, it seems that only last query is output, e.g.

$ trdsql 'select 1; select 2,3'
2,3

Note: union is not used here because of different number of columns.

A workaround is to run multiple commands (e.g. trdsql 'select 1'; trdsql 'select 2,3'), but database will be created multiple times. And if input is from stdin, you have to save it first to share same input for each command.

Please add an option to auto-detect column types (e.g. -itype), or specify numeric column types (e.g. -inum c2,c3).

Currently, it seems that column types are not auto-detected, which may cause wrong order, e.g.

$ d='a,1
b,2
c,11'

$ <<<$d trdsql -a -
The table name is -.
The file type is CSV.

Data types:
+-------------+------+
| column name | type |
+-------------+------+
| c1          | text |
| c2          | text |
+-------------+------+
…

$ <<<$d trdsql 'select * from - order by c2'
a,1
c,11
b,2

$ <<<$d trdsql 'select * from - order by cast(c2 as number)'
a,1
b,2
c,11

$ trdsql -ijson "select verbose_name from fudaneducn.json group by verbose_name"
trdsql: /lib/x86_64-linux-gnu/libc.so.6: version `GLIBC_2.28' not found (required by trdsql)
trdsql: /lib/x86_64-linux-gnu/libc.so.6: version `GLIBC_2.32' not found (required by trdsql)
trdsql: /lib/x86_64-linux-gnu/libc.so.6: version `GLIBC_2.33' not found (required by trdsql)

It depends on a particular version of glibc

my glibc info

strings /lib/x86_64-linux-gnu/libc.so.6 | grep GLIBC
GLIBC_2.2.5
GLIBC_2.2.6
GLIBC_2.3
GLIBC_2.3.2
GLIBC_2.3.3
GLIBC_2.3.4
GLIBC_2.4
GLIBC_2.5
GLIBC_2.6
GLIBC_2.7
GLIBC_2.8
GLIBC_2.9
GLIBC_2.10
GLIBC_2.11
GLIBC_2.12
GLIBC_2.13
GLIBC_2.14
GLIBC_2.15
GLIBC_2.16
GLIBC_2.17
GLIBC_2.18
GLIBC_2.22
GLIBC_2.23
GLIBC_2.24
GLIBC_2.25
GLIBC_2.26
GLIBC_2.27
GLIBC_PRIVATE
GNU C Library (Ubuntu GLIBC 2.27-3ubuntu1) stable release version 2.27.