[MonetDB-users] Problems with COPY INTO and CHAR(N) columns containing empty strings or all spaces
We've had problems with MonetDB SR5 using COPY INTO with data that may be the empty string or all spaces. We've been able to work around this by surrounding all of the values in our copy files with "quotes" (I read tablet.c to try and diagnose the issue and found that as a workaround). I'm not familiar enough with the source and build process yet to submit a patch.
We are using '\t' as the separator and the fields that we've had problem with are CHAR(2) or CHAR(16) if that matters.
-bob
We've just started again a few hours ago (because SR5 crashed and we couldn't resurrect it) with the --nightly=stable build as downloaded today 2007-01-21 and we ended up with a different problem. Using "" to represent the empty string doesn't work because MonetDB thinks "" represents NULL and that is not allowed by column constraint. The new workaround is to explicitly rpad the strings to fill the CHAR(2) or CHAR(16) in addition to quoting. e.g. instead of for a CHAR(2) value of all spaces "" we use " ".
On Jan 21, 2008 1:24 PM, Bob Ippolito bob@redivi.com wrote:
We've had problems with MonetDB SR5 using COPY INTO with data that may be the empty string or all spaces. We've been able to work around this by surrounding all of the values in our copy files with "quotes" (I read tablet.c to try and diagnose the issue and found that as a workaround). I'm not familiar enough with the source and build process yet to submit a patch.
We are using '\t' as the separator and the fields that we've had problem with are CHAR(2) or CHAR(16) if that matters.
-bob
We would be greatly helped if you could sent a file with the table definition and a single example tuple that caused havoq in tablet.c to provide a fix. A workaround is not the preferred way to go ;)
Bob Ippolito wrote:
We've had problems with MonetDB SR5 using COPY INTO with data that may be the empty string or all spaces. We've been able to work around this by surrounding all of the values in our copy files with "quotes" (I read tablet.c to try and diagnose the issue and found that as a workaround). I'm not familiar enough with the source and build process yet to submit a patch.
We are using '\t' as the separator and the fields that we've had problem with are CHAR(2) or CHAR(16) if that matters.
-bob
This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2008. http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/ _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
No problem, here you go. This script (also attached) fails with SR5 and the nightly I built on 2008-01-21:
import MonetSQLdb conn = MonetSQLdb.connect(host='localhost', lang='sql') cur = conn.cursor() fn = '/tmp/copy_problem.txt' fh = file(fn, 'wb') fh.write('ok\t\tok\n') fh.flush() fh.close() try: cur.execute("DROP TABLE test_copy") except RuntimeError: pass cur.execute("CREATE TABLE test_copy(A CHAR(4), B CHAR(4), C CHAR(4))") cur.execute("COPY 1 RECORDS INTO test_copy FROM '/tmp/copy_problem.txt' USING DELIMITERS '\t'") cur.close() conn.close()
The exception is: RuntimeError: !SQLException:sql:missing sep line 1 field 1 !SQLException:importTable:failed to import table
-bob
On Jan 21, 2008 11:29 PM, Martin Kersten Martin.Kersten@cwi.nl wrote:
We would be greatly helped if you could sent a file with the table definition and a single example tuple that caused havoq in tablet.c to provide a fix. A workaround is not the preferred way to go ;)
Bob Ippolito wrote:
We've had problems with MonetDB SR5 using COPY INTO with data that may be the empty string or all spaces. We've been able to work around this by surrounding all of the values in our copy files with "quotes" (I read tablet.c to try and diagnose the issue and found that as a workaround). I'm not familiar enough with the source and build process yet to submit a patch.
We are using '\t' as the separator and the fields that we've had problem with are CHAR(2) or CHAR(16) if that matters.
-bob
This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2008. http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/ _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2008. http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/ _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
participants (2)
-
Bob Ippolito -
Martin Kersten