- From: Boris Villazon-Terrazas <bvillazon@fi.upm.es>
- Date: Fri, 20 Apr 2012 02:25:39 +0200
- To: Eric Prud'hommeaux <eric@w3.org>
- Cc: public-rdb2rdf-wg@w3.org
- Message-Id: <FE20D3CE-252D-4C79-99B8-3E34DD664CD4@fi.upm.es>
Hi Eric On Apr 18, 2012, at 5:27 AM, Eric Prud'hommeaux wrote: > How have folks interpreted 1table1primarykey10columns3rowsSQLdatatypes > in Oracele?? (BTW, I propose a name like allTypes if that works for > others.) So, you are suggesting to replace SQLdatatypes by allTypes? If everyone agrees I'll rename it. > I've interpreted > [[ > CREATE TABLE "Patient" ( > "ID" INTEGER, > "FirstName" VARCHAR(50), > "LastName" VARCHAR(50), > "Sex" VARCHAR(6), > "Weight" REAL, > "Height" FLOAT, > "BirthDate" DATE, > "EntranceDate" TIMESTAMP, > "PaidInAdvance" BOOLEAN, > "Photo" BINARY VARYING(200), > PRIMARY KEY ("ID") > ); > > INSERT INTO "Patient" ("ID", "FirstName","LastName","Sex","Weight","Height","BirthDate","EntranceDate","PaidInAdvance","Photo") > VALUES (10,'Monica','Geller','female',80.25,1.65,'1981-10-10','2009-10-10 12:12:22',FALSE, CAST( > 'iVBORw0KGgoAAAANSUhEUgAAAAUAAAAFCAYAAACNbyblAAAAHElEQVQI12P4\ux2F\ux2F8\ux2Fw38GIAXDIBKE0DHxgljNBAAO9TXL0Y4OHwAAAABJRU5ErkJggg\ux3D\ux3D' > AS BINARY VARYING(200))); > ]] > as > [[ > CREATE TABLE "Patient" ( > "ID" INTEGER, > "FirstName" VARCHAR(50), > "LastName" VARCHAR(50), > "Sex" VARCHAR(6), > "Weight" REAL, > "Height" FLOAT, > "BirthDate" DATE, > "EntranceDate" TIMESTAMP, > "PaidInAdvance" NUMERIC(1), -- takes 0 or 1 > "Photo" BLOB, -- what's a good varying binary type? > PRIMARY KEY ("ID") > ); > > INSERT INTO "Patient" ("ID", "FirstName", "LastName", "Sex", "Weight", "Height", "BirthDate", "EntranceDate", "PaidInAdvance", "Photo") VALUES (10, 'Monica', 'Geller', 'female', 80.25, 1.65, TO_DATE('1981-10-10', 'yyyy-mm-dd'), TO_DATE('2009-10-10 12:12:22', 'yyyy-mm-dd HH24:MI:SS'), 0, CAST('iVBORw0KGgoAAAANSUhEUgAAAAUAAAAFCAYAAACNbyblAAAAHElEQVQI12P4//8/w38GIAXDIBKE0DHxgljNBAAO9TXL0Y4OHwAAAABJRU5ErkJggg==' AS BLOB)) > ]] > but I get > "ORA-00932: inconsistent datatypes: expected - got BLOB" at "0, CAST('iV" > ↑ > and if I get rid of the CAST I get "ORA-01465: invalid hex number" at ", 0, 'iVBO" I followed Richard suggestions, so now the sql script is sth like this CREATE TABLE "Patient" ( "ID" INTEGER, "FirstName" VARCHAR(50), "LastName" VARCHAR(50), "Sex" VARCHAR(6), "Weight" REAL, "Height" FLOAT, "BirthDate" DATE, "EntranceDate" TIMESTAMP, "PaidInAdvance" BOOLEAN, "Photo" VARBINARY(200), PRIMARY KEY ("ID") ); INSERT INTO "Patient" ("ID", "FirstName","LastName","Sex","Weight","Height","BirthDate","EntranceDate","PaidInAdvance","Photo") VALUES (10,'Monica','Geller','female',80.25,1.65,'1981-10-10','2009-10-10 12:12:22',FALSE, X'89504E470D0A1A0A0000000D49484452000000050000000508060000008D6F26E50000001C4944415408D763F9FFFEBFC37F062005C3201284D031F18258CD04000EF535CBD18E0E1F0000000049454E44AE426082'); INSERT INTO "Patient" ("ID", "FirstName","LastName","Sex","Weight","Height","BirthDate","EntranceDate","PaidInAdvance","Photo") VALUES (11,'Rachel','Green','female',70.22,1.70,'1982-11-12','2008-11-12 09:45:44',TRUE, X'89504E470D0A1A0A0000000D49484452000000050000000508060000008D6F26E50000001C4944415408D763F9FFFF3FC37F062005C3201284D031F18258CD04000EF535CBD18E0E1F0000000049454E44AE426082'); INSERT INTO "Patient" ("ID", "FirstName","LastName","Sex","Weight","Height","BirthDate","EntranceDate","PaidInAdvance","Photo") VALUES (12,'Chandler','Bing','male',90.31,1.76,'1978-04-06','2007-03-12 02:13:14',TRUE, X'89504E470D0A1A0A0000000D49484452000000050000000508060000008D6F26E50000001C4944415408D763F9FFFEBFC37F062005C3201284D031F18258CD04000EF535CBD18E0E1F0000000049454E44AE426082'); i.e., do not use CAST, as Richard suggested Don't use a CAST but use a binary literal. The standard SQL way of encoding binary literals is this X'0123456789ABCDEF' > ↑ > Also, gives me ORA-00957: duplicate column name: > CREATE TABLE "成分" ("皿" VARCHAR(10), "植物名" VARCHAR(10), "使用部" VARCHAR(10)) > ↑ > The apparent collision seems to be the last two column names. > CREATE TABLE "成分" ("皿" VARCHAR(10), "植物名" VARCHAR(10)) works I dunno, I haven't tested with ORACLE > > One more thing, for Postgresql, I used BYTEA for BINARY. Any better advice? It seems to be ok …. http://www.postgresql.org/docs/8.1/static/datatype-binary.html Boris > -- > -ericP >
Received on Friday, 20 April 2012 00:26:06 UTC