mattn
commented
6 years ago What you expect to pass the value from database/sql? []int64?
Closed Blue-Ledao closed 5 years ago
mattn
commented
6 years ago What you expect to pass the value from database/sql? []int64?
Blue-Ledao
commented
6 years ago I'm glad to see your response. I didn't want to pass []int64 from the database. In fact, I use a interface{} variable to store the data read from the database. Then the assertion converts the data. Just like this(This is my test code):
db, err := sql.Open("oci8", "userName/password@127.0.0.1:1521/orcl")
if err != nil {
fmt.Println(err)
}
stmt, err := db.Prepare(`select "h" from "t_databasefiledtype" WHERE "a" = '002' AND "d1" > 1 AND "j1" = 18`)
if err != nil {
fmt.Println(err)
}
defer stmt.Close()
rows, err := stmt.Query()
if err != nil {
fmt.Println(err)
}
columnCount, _ := rows.Columns()
fmt.Println(columnCount)
cols := make([]interface{}, len(columnCount))
colPtrs := make([]interface{}, len(columnCount))
for i := range cols {
colPtrs[i] = &cols[i]
}
for rows.Next() {
rows.Scan(colPtrs...)
}
var result interface{}
for i, _ := range cols {
fmt.Println("field "+columnCount[i]+"'s type is:", reflect.TypeOf(cols[i]))
switch temp := cols[i].(type) {
case nil:
result = ""
case float64:
formatStr := "%." + fmt.Sprintf("%df", 20)
floatString := fmt.Sprintf(formatStr, cols[i])
result, _ = strconv.ParseFloat(floatString, 20)
case int64:
result = cols[i].(int64)
case string:
result = cols[i].(string)
case time.Time:
result = temp.Format("2006-01-02 15:04:05")
case []uint8:
result = string(cols[i].([]uint8))
}
fmt.Println(result)
}The data type of "H" in the SQL statement in an Oracle database is NCLOB，As long as the Nclob type field appears in my SELECT statement, cols[i].(type) becomes nil. I found by debugging the code：rows. Next () returns false. In other words, rows do not have data. But in my database, the NCLOB fields are stored values.
mattn
commented
6 years ago Could you please show me your DDL?
Blue-Ledao
commented
6 years ago You say DDL, I guess not Data Definition Language, but the data stored in the NCLOB field. Well, my data stored in NCLOB which is a paragraph of text.
And this is my golang program execution results:
Blue-Ledao
commented
6 years ago Hello, mattn. I've been testing the LONG and NCLOB fields for the last few days. I've printed errors in oci8.go, just like this:
func (rc *OCI8Rows) Next(dest []driver.Value) (err error) {
if rc.closed {
return nil
}
rv := C.OCIStmtFetch2(
(*C.OCIStmt)(rc.s.s),
(*C.OCIError)(rc.s.c.err),
1,
C.OCI_FETCH_NEXT,
0,
C.OCI_DEFAULT) //Whether it is LONG or NCLOB field,after executing C.OCIStmtFetch2() function, rv= -1
if rv == C.OCI_NO_DATA {
fmt.Println(1, io.EOF.Error()) // I added the code in this line
return io.EOF
} else if rv != C.OCI_SUCCESS && rv != C.OCI_SUCCESS_WITH_INFO {
fmt.Println(2, ociGetError(rv, rc.s.c.err).Error()) // I added the code in this line
return ociGetError(rv, rc.s.c.err)
}
......I got two errors, when I select NCLOB field, get ORA-24806, when I select LONG field, get ORA-24374. I'm not sure if my Oracle environment is going to be a problem, or is there go-oci8 has bug? Can you give me some advice? thanks
mattn
commented
6 years ago go-oci8 support only blob. you do have your CREATE TABLE sql?
Blue-Ledao
commented
6 years ago No problem. This is the SQL I export with Navicat (hope is what you want)：
/*
Navicat Oracle Data Transfer
Oracle Client Version : 12.2.0.1.0
Source Server : oracle
Source Server Version : 120200
Source Host : localhost:1521
Source Schema : C##TLD
Target Server Type : ORACLE
Target Server Version : 120200
File Encoding : 65001
Date: 2017-11-06 20:13:04
*/
-- ----------------------------
-- Table structure for t_databasefiledtype
-- ----------------------------
DROP TABLE "C##TLD"."t_databasefiledtype";
CREATE TABLE "C##TLD"."t_databasefiledtype" (
"a" CHAR(255 BYTE) NULL ,
"b" NCHAR(255) NULL ,
"c" VARCHAR2(255 BYTE) NULL ,
"d" VARCHAR2(255 BYTE) NULL ,
"e" NVARCHAR2(255) NULL ,
"f" CLOB NULL ,
"g" LONG NULL ,
"h" NCLOB NULL ,
"i" NUMBER NULL ,
"j" BINARY_FLOAT NULL ,
"k" BINARY_DOUBLE NULL ,
"l" DATE NULL ,
"m" INTERVAL DAY (2) TO SECOND(6) DEFAULT '' NULL ,
"n" INTERVAL YEAR (2) TO MONTH NULL ,
"o" TIMESTAMP(6) NULL ,
"p" TIMESTAMP (6) WITH TIME ZONE NULL ,
"q" TIMESTAMP (6) WITH LOCAL TIME ZONE NULL ,
"r" BLOB NULL ,
"s" BFILE NULL ,
"t" RAW(10) NULL ,
"u" ROWID NULL ,
"v" CHAR(255 BYTE) NULL ,
"w" VARCHAR2(255 BYTE) NULL ,
"x" VARCHAR2(255 BYTE) NULL ,
"y" NCHAR(255) NULL ,
"z" NCHAR(255) NULL ,
"a1" NVARCHAR2(255) NULL ,
"b1" NVARCHAR2(255) NULL ,
"c1" NVARCHAR2(255) NULL ,
"d1" NUMBER(1) NULL ,
"e1" NUMBER(30) NULL ,
"f1" NUMBER NULL ,
"g1" NUMBER NULL ,
"h1" NUMBER NULL ,
"i1" FLOAT(126) NULL ,
"j1" FLOAT(126) NULL ,
"k1" FLOAT(63) NULL
)
LOGGING
NOCOMPRESS
NOCACHE
;
-- ----------------------------
-- Records of t_databasefiledtype
-- ----------------------------
INSERT INTO "C##TLD"."t_databasefiledtype" VALUES ('sfd ', 'true ', '4,1,3,2', '25jafn0', '25amajafn0', '192ama168.1.14', '112iphone7', 'The LONG type column cannot be used in the following clauses of Select: where, group by, order by, and select statements with distinct.', '102', '21', '254.2', TO_DATE('2017-12-13 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), null, null, TO_TIMESTAMP(' 2017-12-13 12:13:14:000000', 'YYYY-MM-DD HH24:MI:SS:FF6'), TO_TIMESTAMP_TZ(' 2015-04-13 00:00:00:000000 +08:00', 'YYYY-MM-DD HH24:MI:SS:FF6 TZR'), TO_TIMESTAMP(' 2010-02-03 04:05:06:000000', 'YYYY-MM-DD HH24:MI:SS:FF6'), HexToRaw('12354ABC'), null, HexToRaw('145BBC'), 'AAAO0fAAFAAAAlmAAA', 'iPad ', 'firefoxama', 'Siemens', 'ama2012-12-02 12:12:12ama ', 'amaBBC123456ama ', 'amaBBCDama', 'amaAAAO0fAAFAAAAlmAAAama', 'world cupama', '2', '9876', '457', '145', '222', '254.221', '888.999', '123455.9999');
INSERT INTO "C##TLD"."t_databasefiledtype" VALUES ('250 ', 'AOoAB ', 'google', '25jafn0', '25amajafn0', '192ama168.1.14', '112iphone7', 'The LONG type column cannot be used in the following clauses of Select: where, group by, order by, and select statements with distinct.', '102', '21', '254.2', TO_DATE('2017-12-13 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), null, null, TO_TIMESTAMP(' 2017-12-13 12:13:14:000000', 'YYYY-MM-DD HH24:MI:SS:FF6'), TO_TIMESTAMP_TZ(' 2015-04-13 00:00:00:000000 +08:00', 'YYYY-MM-DD HH24:MI:SS:FF6 TZR'), TO_TIMESTAMP(' 2010-02-03 04:05:06:000000', 'YYYY-MM-DD HH24:MI:SS:FF6'), HexToRaw('12354ABC'), null, HexToRaw('145BBC'), 'AAAO0fAAFAAAAlmAAA', 'iPad ', 'firefoxama', 'Siemens', 'ama2012-12-02 12:12:12ama ', 'amaBBC123456ama ', 'amaBBCDama', 'amaAAAO0fAAFAAAAlmAAAama', 'world cupama', '1', '77', '32', '15', '400', '99.9', '132.2', '20.1');
INSERT INTO "C##TLD"."t_databasefiledtype" VALUES ('sfd ', 'true ', '4,1,3,2', '25jafn0', '25amajafn0', '192ama168.1.14', '112iphone7', 'The LONG type column cannot be used in the following clauses of Select: where, group by, order by, and select statements with distinct.', '102', '21', '254.2', TO_DATE('2017-12-13 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), null, null, TO_TIMESTAMP(' 2017-12-13 12:13:14:000000', 'YYYY-MM-DD HH24:MI:SS:FF6'), TO_TIMESTAMP_TZ(' 2015-04-13 00:00:00:000000 +08:00', 'YYYY-MM-DD HH24:MI:SS:FF6 TZR'), TO_TIMESTAMP(' 2010-02-03 04:05:06:000000', 'YYYY-MM-DD HH24:MI:SS:FF6'), HexToRaw('12354ABC'), null, HexToRaw('145BBC'), 'AAAO0fAAFAAAAlmAAA', 'iPad ', 'firefoxama', 'Siemens', 'ama2012-12-02 12:12:12ama ', 'amaBBC123456ama ', 'amaBBCDama', 'amaAAAO0fAAFAAAAlmAAAama', 'world cupama', '2', '9876', '457', '145', '222', '254.221', '888.999', '123455.9999');
INSERT INTO "C##TLD"."t_databasefiledtype" VALUES ('250 ', 'AOoAB ', 'google', '25jafn0', '25amajafn0', '192ama168.1.14', '112iphone7', 'The LONG type column cannot be used in the following clauses of Select: where, group by, order by, and select statements with distinct.', '102', '21', '254.2', TO_DATE('2017-12-13 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), null, null, TO_TIMESTAMP(' 2017-12-13 12:13:14:000000', 'YYYY-MM-DD HH24:MI:SS:FF6'), TO_TIMESTAMP_TZ(' 2015-04-13 00:00:00:000000 +08:00', 'YYYY-MM-DD HH24:MI:SS:FF6 TZR'), TO_TIMESTAMP(' 2010-02-03 04:05:06:000000', 'YYYY-MM-DD HH24:MI:SS:FF6'), HexToRaw('12354ABC'), null, HexToRaw('145BBC'), 'AAAO0fAAFAAAAlmAAA', 'iPad ', 'firefoxama', 'Siemens', 'ama2012-12-02 12:12:12ama ', 'amaBBC123456ama ', 'amaBBCDama', 'amaAAAO0fAAFAAAAlmAAAama', 'world cupama', '1', '77', '32', '15', '400', '99.9', '132.2', '20.1');
INSERT INTO "C##TLD"."t_databasefiledtype" VALUES ('sfd ', 'true ', '4,1,3,2', '25jafn0', '25amajafn0', '192ama168.1.14', '112iphone7', 'The LONG type column cannot be used in the following clauses of Select: where, group by, order by, and select statements with distinct.', '102', '21', '254.2', TO_DATE('2017-12-13 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), null, null, TO_TIMESTAMP(' 2017-12-13 12:13:14:000000', 'YYYY-MM-DD HH24:MI:SS:FF6'), TO_TIMESTAMP_TZ(' 2015-04-13 00:00:00:000000 +08:00', 'YYYY-MM-DD HH24:MI:SS:FF6 TZR'), TO_TIMESTAMP(' 2010-02-03 04:05:06:000000', 'YYYY-MM-DD HH24:MI:SS:FF6'), HexToRaw('12354ABC'), null, HexToRaw('145BBC'), 'AAAO0fAAFAAAAlmAAA', 'iPad ', 'firefoxama', 'Siemens', 'ama2012-12-02 12:12:12ama ', 'amaBBC123456ama ', 'amaBBCDama', 'amaAAAO0fAAFAAAAlmAAAama', 'world cupama', '2', '9876', '457', '145', '222', '254.221', '888.999', '123455.9999');
INSERT INTO "C##TLD"."t_databasefiledtype" VALUES ('250 ', 'AOoAB ', 'google', '25jafn0', '25amajafn0', '192ama168.1.14', '112iphone7', 'The LONG type column cannot be used in the following clauses of Select: where, group by, order by, and select statements with distinct.', '102', '21', '254.2', TO_DATE('2017-12-13 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), null, null, TO_TIMESTAMP(' 2017-12-13 12:13:14:000000', 'YYYY-MM-DD HH24:MI:SS:FF6'), TO_TIMESTAMP_TZ(' 2015-04-13 00:00:00:000000 +08:00', 'YYYY-MM-DD HH24:MI:SS:FF6 TZR'), TO_TIMESTAMP(' 2010-02-03 04:05:06:000000', 'YYYY-MM-DD HH24:MI:SS:FF6'), HexToRaw('12354ABC'), null, HexToRaw('145BBC'), 'AAAO0fAAFAAAAlmAAA', 'iPad ', 'firefoxama', 'Siemens', 'ama2012-12-02 12:12:12ama ', 'amaBBC123456ama ', 'amaBBCDama', 'amaAAAO0fAAFAAAAlmAAAama', 'world cupama', '1', '77', '32', '15', '400', '99.9', '132.2', '20.1');go-oci8 can read all fields except Lang,NCLOB and BFILE.
mattn
commented
6 years ago Thank you. I'll look into it in later.
242617
commented
6 years ago I have the same issue.
Workaround is to use
DBMS_LOB.SUBSTR(test, {{amount}}, {{offset}})
in cycle.
ghost
commented
6 years ago @242617 Dmitry, can you provide an example? How it works?
I have the same issue. Workaround is to use DBMS_LOB.SUBSTR(test, {{amount}}, {{offset}}) in cycle.
Thanks!
242617
commented
6 years ago Something like that (it could be non-working example):
const rawQuery = `
SELECT
DBMS_LOB.SUBSTR(NEWS_TEXT, {{amount}}, {{offset}})
FROM NEWS
WHERE NEWS_ID = :1
ORDER BY NEWS_CREATED DESC
`
const SubstrAmount = 2000
func News() (text string, err error) {
var n int
for {
query := rawQuery
query = strings.Replace(query, "{{amount}}", strconv.Itoa(SubstrAmount), -1)
query = strings.Replace(query, "{{offset}}", strconv.Itoa(1+n*SubstrAmount), -1)
var str string
err = db.QueryRowContext(query, id).Scan(&str)
if err != nil {
return
}
text += str
n++
}
return
}
MichaelS11
commented
5 years ago Is this still an issue? Now have tests for longs up to 4000 chars and lobs up to 32K.
242617
commented
5 years ago I had an ~45 KB NCLOB. Unfortunately, now I can't reproduce this case for the test.
MichaelS11
commented
5 years ago So can this issue be close?
MichaelS11
commented
5 years ago @mattn The original issue owner has not responded in a while. Should this be closed or is there another test that should be added to oci8 to cover this issue?
mattn
commented
5 years ago Please reopen if you still have this issue.
Dot-Liu
commented
5 years ago I'm having this problem, too. You only need to modify the lobbuffersize variable in the global.go like this：
const (
lobBufferSize = 32 * 1024
useOCISessionBegin = true
sizeOfNilPointer = unsafe.Sizeof(unsafe.Pointer(nil))
)@qq1324185 Pretty sure that the raising the lobBufferSize is not the correct solution for the problem you are having.
Welcome to open a new issue and provide simple SQL and Go code that show the issue.
Hello, mattn. I used your go-oci8 in my program. In most cases, go-oci8 is very usefu, but when I select long and nclob fields, the result returned is nil. Does go-oci8 not support these two types?