diff options
author | Sergei Golubchik <sergii@pisem.net> | 2015-03-02 16:45:44 +0100 |
---|---|---|
committer | Sergei Golubchik <sergii@pisem.net> | 2015-03-02 16:45:44 +0100 |
commit | c06c465a963fb114c6c83ced1b9b933aa2ff40ee (patch) | |
tree | 5f129c3359f1b16576c4c3225ab8f6a6efead3d1 | |
parent | 4a3e94e0259cbe2dea9d1d64a6bd68feb4a70084 (diff) | |
parent | b9a9b82f9ec09e7b52b03984b0d25f5dc86b0f73 (diff) | |
download | mariadb-git-c06c465a963fb114c6c83ced1b9b933aa2ff40ee.tar.gz |
10.0-connect merge
22 files changed, 711 insertions, 143 deletions
diff --git a/storage/connect/ha_connect.cc b/storage/connect/ha_connect.cc index e73a64a150c..ea81bf43a01 100644 --- a/storage/connect/ha_connect.cc +++ b/storage/connect/ha_connect.cc @@ -4112,6 +4112,7 @@ MODE ha_connect::CheckMode(PGLOBAL g, THD *thd, case SQLCOM_UPDATE_MULTI: case SQLCOM_SELECT: case SQLCOM_OPTIMIZE: + case SQLCOM_SET_OPTION: break; case SQLCOM_LOCK_TABLES: locked= 1; @@ -5461,7 +5462,9 @@ static int connect_assisted_discovery(handlerton *hton, THD* thd, case TYPE_DOUBLE: // Some data sources do not count dec in length (prec) prec += (dec + 2); // To be safe + break; case TYPE_DECIM: + prec= len; break; default: dec= 0; diff --git a/storage/connect/json.cpp b/storage/connect/json.cpp index 8031ba51b19..6f4bd18457b 100644 --- a/storage/connect/json.cpp +++ b/storage/connect/json.cpp @@ -662,7 +662,7 @@ JOUTSTR::JOUTSTR(PGLOBAL g) : JOUT(g) N = 0; Max = pph->FreeBlk; - Max = (Max > 512) ? Max - 512 : Max; + Max = (Max > 32) ? Max - 32 : Max; Strp = (char*)PlugSubAlloc(g, NULL, 0); // Size not know yet } // end of JOUTSTR constructor @@ -881,27 +881,26 @@ PJVAL JOBJECT::GetValue(const char* key) /***********************************************************************/ /* Return the text corresponding to all keys (XML like). */ /***********************************************************************/ -PSZ JOBJECT::GetText(PGLOBAL g) +PSZ JOBJECT::GetText(PGLOBAL g, PSZ text) { - char *p, *text = (char*)PlugSubAlloc(g, NULL, 0); - bool b = true; + int n; - if (!First) - return NULL; - else for (PJPR jp = First; jp; jp = jp->Next) { - if (!(p = jp->Val->GetString())) - p = "???"; + if (!text) { + text = (char*)PlugSubAlloc(g, NULL, 0); + text[0] = 0; + n = 1; + } else + n = 0; - if (b) { - strcpy(text, p); - b = false; - } else - strcat(strcat(text, " "), p); + if (!First && n) + return NULL; + else for (PJPR jp = First; jp; jp = jp->Next) + jp->Val->GetText(g, text); - } // endfor jp + if (n) + PlugSubAlloc(g, NULL, strlen(text) + 1); - PlugSubAlloc(g, NULL, strlen(text) + 1); - return text; + return text + n; } // end of GetValue; /***********************************************************************/ @@ -924,6 +923,18 @@ void JOBJECT::SetValue(PGLOBAL g, PJVAL jvp, PSZ key) } // end of SetValue +/***********************************************************************/ +/* True if void or if all members are nulls. */ +/***********************************************************************/ +bool JOBJECT::IsNull(void) +{ + for (PJPR jp = First; jp; jp = jp->Next) + if (!jp->Val->IsNull()) + return false; + + return true; +} // end of IsNull + /* -------------------------- Class JARRAY --------------------------- */ /***********************************************************************/ @@ -1012,6 +1023,18 @@ bool JARRAY::DeleteValue(int n) } // end of DeleteValue +/***********************************************************************/ +/* True if void or if all members are nulls. */ +/***********************************************************************/ +bool JARRAY::IsNull(void) +{ + for (int i = 0; i < Size; i++) + if (!Mvals[i]->IsNull()) + return false; + + return true; +} // end of IsNull + /* -------------------------- Class JVALUE- -------------------------- */ /***********************************************************************/ @@ -1087,6 +1110,25 @@ PSZ JVALUE::GetString(void) } // end of GetString /***********************************************************************/ +/* Return the Value's String value. */ +/***********************************************************************/ +PSZ JVALUE::GetText(PGLOBAL g, PSZ text) +{ + if (Jsp && Jsp->GetType() == TYPE_JOB) + return Jsp->GetText(g, text); + + char buf[32]; + PSZ s = (Value) ? Value->GetCharString(buf) : NULL; + + if (s) + strcat(strcat(text, " "), s); + else + strcat(text, " ???"); + + return text; +} // end of GetText + +/***********************************************************************/ /* Set the Value's value as the given integer. */ /***********************************************************************/ void JVALUE::SetInteger(PGLOBAL g, int n) @@ -1110,3 +1152,11 @@ void JVALUE::SetString(PGLOBAL g, PSZ s) Value = AllocateValue(g, s, TYPE_STRING); } // end of AddFloat +/***********************************************************************/ +/* True when its JSON or normal value is null. */ +/***********************************************************************/ +bool JVALUE::IsNull(void) +{ + return (Jsp) ? Jsp->IsNull() : (Value) ? Value->IsNull() : true; +} // end of IsNull + diff --git a/storage/connect/json.h b/storage/connect/json.h index e56803f63c9..cdf9034e07f 100644 --- a/storage/connect/json.h +++ b/storage/connect/json.h @@ -152,7 +152,7 @@ class JSON : public BLOCK { virtual int GetInteger(void) {X return 0;}
virtual double GetFloat() {X return 0.0;}
virtual PSZ GetString() {X return NULL;}
- virtual PSZ GetText(PGLOBAL g) {X return NULL;}
+ virtual PSZ GetText(PGLOBAL g, PSZ text) {X return NULL;}
virtual bool SetValue(PGLOBAL g, PJVAL jvp, int i) {X return true;}
virtual void SetValue(PGLOBAL g, PJVAL jvp, PSZ key) {X}
virtual void SetValue(PVAL valp) {X}
@@ -161,6 +161,7 @@ class JSON : public BLOCK { virtual void SetInteger(PGLOBAL g, int n) {X}
virtual void SetFloat(PGLOBAL g, double f) {X}
virtual bool DeleteValue(int i) {X return true;}
+ virtual bool IsNull(void) {X return true;}
protected:
int Size;
@@ -182,8 +183,9 @@ class JOBJECT : public JSON { virtual PJPR AddPair(PGLOBAL g, PSZ key);
virtual PJOB GetObject(void) {return this;}
virtual PJVAL GetValue(const char* key);
- virtual PSZ GetText(PGLOBAL g);
+ virtual PSZ GetText(PGLOBAL g, PSZ text);
virtual void SetValue(PGLOBAL g, PJVAL jvp, PSZ key);
+ virtual bool IsNull(void);
protected:
PJPR First;
@@ -208,6 +210,7 @@ class JARRAY : public JSON { virtual PJVAL GetValue(int i);
virtual bool SetValue(PGLOBAL g, PJVAL jvp, int i);
virtual bool DeleteValue(int n);
+ virtual bool IsNull(void);
protected:
// Members
@@ -244,11 +247,13 @@ class JVALUE : public JSON { virtual int GetInteger(void);
virtual double GetFloat(void);
virtual PSZ GetString(void);
+ virtual PSZ GetText(PGLOBAL g, PSZ text);
virtual void SetValue(PVAL valp) {Value = valp;}
virtual void SetValue(PJSON jsp) {Jsp = jsp;}
virtual void SetString(PGLOBAL g, PSZ s);
virtual void SetInteger(PGLOBAL g, int n);
virtual void SetFloat(PGLOBAL g, double f);
+ virtual bool IsNull(void);
protected:
PJSON Jsp; // To the json value
diff --git a/storage/connect/jsonudf.cpp b/storage/connect/jsonudf.cpp index b100f377295..194cb6defd6 100644 --- a/storage/connect/jsonudf.cpp +++ b/storage/connect/jsonudf.cpp @@ -37,6 +37,10 @@ DllExport my_bool Json_Object_init(UDF_INIT*, UDF_ARGS*, char*); DllExport char *Json_Object(UDF_INIT*, UDF_ARGS*, char*, unsigned long*, char *, char *); DllExport void Json_Object_deinit(UDF_INIT*); +DllExport my_bool Json_Object_Nonull_init(UDF_INIT*, UDF_ARGS*, char*); +DllExport char *Json_Object_Nonull(UDF_INIT*, UDF_ARGS*, char*, + unsigned long*, char *, char *); +DllExport void Json_Object_Nonull_deinit(UDF_INIT*); DllExport my_bool Json_Array_Grp_init(UDF_INIT*, UDF_ARGS*, char*); DllExport void Json_Array_Grp_add(UDF_INIT *, UDF_ARGS *, char *, char *); DllExport char *Json_Array_Grp(UDF_INIT*, UDF_ARGS*, char*, @@ -228,7 +232,7 @@ static PSZ MakeKey(PGLOBAL g, UDF_ARGS *args, int i) /***********************************************************************/ static PJVAL MakeValue(PGLOBAL g, UDF_ARGS *args, int i) { - char *sap = args->args[i]; + char *sap = (args->arg_count > (unsigned)i) ? args->args[i] : NULL; PJSON jsp; PJVAL jvp = new(g) JVALUE; @@ -358,7 +362,7 @@ my_bool Json_Array_Add_init(UDF_INIT *initid, UDF_ARGS *args, char *message) strcpy(message, "Json_Value_Add must have at least 2 arguments"); return true; } else if (!IsJson(args, 0)) { - strcpy(message, "Json_Value_Add first argument must be a json array"); + strcpy(message, "Json_Value_Add first argument must be a json item"); return true; } else CalcLen(args, false, reslen, memlen); @@ -438,6 +442,46 @@ void Json_Object_deinit(UDF_INIT* initid) } // end of Json_Object_deinit /***********************************************************************/ +/* Make a Json Oject containing all not null parameters. */ +/***********************************************************************/ +my_bool Json_Object_Nonull_init(UDF_INIT *initid, UDF_ARGS *args, + char *message) +{ + unsigned long reslen, memlen; + + CalcLen(args, true, reslen, memlen); + return JsonInit(initid, message, reslen, memlen); +} // end of Json_Object_Nonull_init + +char *Json_Object_Nonull(UDF_INIT *initid, UDF_ARGS *args, char *result, + unsigned long *res_length, char *is_null, char *error) +{ + char *str; + uint i; + PJOB objp; + PJVAL jvp; + PGLOBAL g = (PGLOBAL)initid->ptr; + + PlugSubSet(g, g->Sarea, g->Sarea_Size); + objp = new(g) JOBJECT; + + for (i = 0; i < args->arg_count; i++) + if (!(jvp = MakeValue(g, args, i))->IsNull()) + objp->SetValue(g, jvp, MakeKey(g, args, i)); + + if (!(str = Serialize(g, objp, NULL, 0))) + str = strcpy(result, g->Message); + + *res_length = strlen(str); + return str; +} // end of Json_Object_Nonull + +void Json_Object_Nonull_deinit(UDF_INIT* initid) +{ + PlugExit((PGLOBAL)initid->ptr); +} // end of Json_Object_nonull_deinit + +/***********************************************************************/ /* Make a Json array from values comming from rows. */ /***********************************************************************/ my_bool Json_Array_Grp_init(UDF_INIT *initid, UDF_ARGS *args, char *message) @@ -517,7 +561,7 @@ my_bool Json_Object_Grp_init(UDF_INIT *initid, UDF_ARGS *args, char *message) unsigned long reslen, memlen, n = GetJsonGrpSize(); if (args->arg_count != 2) { - strcpy(message, "Json_Array_Grp can only accept 2 argument"); + strcpy(message, "Json_Array_Grp can only accept 2 arguments"); return true; } else CalcLen(args, true, reslen, memlen); diff --git a/storage/connect/mysql-test/connect/r/json.result b/storage/connect/mysql-test/connect/r/json.result index e832685c855..ebcad699759 100644 --- a/storage/connect/mysql-test/connect/r/json.result +++ b/storage/connect/mysql-test/connect/r/json.result @@ -12,7 +12,7 @@ TRANSLATION CHAR(32), TRANSLATOR CHAR(80), PUBLISHER CHAR(32), DATEPUB int(4) -) ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='biblio.jsn'; +) ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='biblio.json'; SELECT * FROM t1; ISBN LANG SUBJECT AUTHOR TITLE TRANSLATION TRANSLATOR PUBLISHER DATEPUB 9782212090819 fr applications Jean-Christophe Bernadac Construire une application XML Eyrolles Paris 1999 @@ -34,7 +34,7 @@ Publisher CHAR(20) FIELD_FORMAT='PUBLISHER:NAME', Location CHAR(16) FIELD_FORMAT='PUBLISHER:PLACE', Year int(4) FIELD_FORMAT='DATEPUB' ) -ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='biblio.jsn'; +ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='biblio.json'; SELECT * FROM t1; ISBN Language Subject Authors Title Translation Translator Publisher Location Year 9782212090819 fr applications 2 Construire une application XML Eyrolles Paris 1999 @@ -57,7 +57,7 @@ Publisher CHAR(20) FIELD_FORMAT='PUBLISHER:NAME', Location CHAR(16) FIELD_FORMAT='PUBLISHER:PLACE', Year int(4) FIELD_FORMAT='DATEPUB' ) -ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='biblio.jsn'; +ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='biblio.json'; SELECT * FROM t1; ISBN Language Subject AuthorFN AuthorLN Title Translation Translator Publisher Location Year 9782212090819 fr applications Jean-Christophe and François Bernadac and Knab Construire une application XML Eyrolles Paris 1999 @@ -80,7 +80,7 @@ Publisher CHAR(20) FIELD_FORMAT='PUBLISHER:NAME', Location CHAR(16) FIELD_FORMAT='PUBLISHER:PLACE', Year int(4) FIELD_FORMAT='DATEPUB' ) -ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='biblio.jsn'; +ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='biblio.json'; SELECT * FROM t1; ISBN Language Subject AuthorFN AuthorLN Title Translation Translator Publisher Location Year 9782212090819 fr applications Jean-Christophe Bernadac Construire une application XML Eyrolles Paris 1999 @@ -97,7 +97,7 @@ ISBN Language Subject AuthorFN AuthorLN Title Translation Translator Publisher L CREATE TABLE t2 ( FIRSTNAME CHAR(32), LASTNAME CHAR(32)) -ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='biblio.jsn' OPTION_LIST='Object=[2]:AUTHOR'; +ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='biblio.json' OPTION_LIST='Object=[2]:AUTHOR'; SELECT * FROM t2; FIRSTNAME LASTNAME William J. Pardi @@ -117,7 +117,7 @@ CREATE TABLE t1 ( line char(255) ) -ENGINE=CONNECT TABLE_TYPE=DOS FILE_NAME='biblio.jsn'; +ENGINE=CONNECT TABLE_TYPE=DOS FILE_NAME='biblio.json'; SELECT * FROM t1; line [ @@ -178,7 +178,7 @@ WHO CHAR(12), WEEK INT(2) FIELD_FORMAT='WEEK:[X]:NUMBER', WHAT CHAR(32) FIELD_FORMAT='WEEK::EXPENSE:["+"]:WHAT', AMOUNT DOUBLE(8,2) FIELD_FORMAT='WEEK::EXPENSE:[+]:AMOUNT') -ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.jsn'; +ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.json'; SELECT * FROM t1; WHO WEEK WHAT AMOUNT Joe 3 Beer+Food+Food+Car 69.00 @@ -199,7 +199,7 @@ WHO CHAR(12), WEEK INT(2) FIELD_FORMAT='WEEK:[X]:NUMBER', WHAT CHAR(32) FIELD_FORMAT='WEEK:[X]:EXPENSE:[X]:WHAT', AMOUNT DOUBLE(8,2) FIELD_FORMAT='WEEK:[X]:EXPENSE:[X]:AMOUNT') -ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.jsn'; +ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.json'; SELECT * FROM t1; WHO WEEK WHAT AMOUNT Joe 3 Beer 18.00 @@ -228,6 +228,26 @@ Janet 5 Beer 19.00 Janet 5 Food 12.00 DROP TABLE t1; # +# A table showing many calculated results +# +CREATE TABLE t1 ( +WHO CHAR(12) NOT NULL, +WEEKS CHAR(12) NOT NULL FIELD_FORMAT='WEEK:[", "]:NUMBER', +SUMS CHAR(64) NOT NULL FIELD_FORMAT='WEEK:["+"]:EXPENSE:[+]:AMOUNT', +SUM DOUBLE(8,2) NOT NULL FIELD_FORMAT='WEEK:[+]:EXPENSE:[+]:AMOUNT', +AVGS CHAR(64) NOT NULL FIELD_FORMAT='WEEK:["+"]:EXPENSE:[!]:AMOUNT', +SUMAVG DOUBLE(8,2) NOT NULL FIELD_FORMAT='WEEK:[+]:EXPENSE:[!]:AMOUNT', +AVGSUM DOUBLE(8,2) NOT NULL FIELD_FORMAT='WEEK:[!]:EXPENSE:[+]:AMOUNT', +AVGAVG DOUBLE(8,2) NOT NULL FIELD_FORMAT='WEEK:[!]:EXPENSE:[!]:AMOUNT', +AVERAGE DOUBLE(8,2) NOT NULL FIELD_FORMAT='WEEK:[!]:EXPENSE:[X]:AMOUNT') +ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.json'; +SELECT * FROM t1; +WHO WEEKS SUMS SUM AVGS SUMAVG AVGSUM AVGAVG AVERAGE +Joe 3, 4, 5 69.00+83.00+26.00 178.00 17.25+16.60+13.00 46.85 59.33 15.62 16.18 +Beth 3, 4, 5 16.00+32.00+32.00 80.00 16.00+16.00+16.00 48.00 26.67 16.00 16.00 +Janet 3, 4, 5 55.00+17.00+57.00 129.00 18.33+17.00+14.25 49.58 43.00 16.53 16.12 +DROP TABLE t1; +# # Expand expense in 3 one week tables # CREATE TABLE t2 ( @@ -235,7 +255,7 @@ WHO CHAR(12), WEEK INT(2) FIELD_FORMAT='WEEK:[1]:NUMBER', WHAT CHAR(32) FIELD_FORMAT='WEEK:[1]:EXPENSE:[X]:WHAT', AMOUNT DOUBLE(8,2) FIELD_FORMAT='WEEK:[1]:EXPENSE:[X]:AMOUNT') -ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.jsn'; +ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.json'; SELECT * FROM t2; WHO WEEK WHAT AMOUNT Joe 3 Beer 18.00 @@ -251,7 +271,7 @@ WHO CHAR(12), WEEK INT(2) FIELD_FORMAT='WEEK:[2]:NUMBER', WHAT CHAR(32) FIELD_FORMAT='WEEK:[2]:EXPENSE:[X]:WHAT', AMOUNT DOUBLE(8,2) FIELD_FORMAT='WEEK:[2]:EXPENSE:[X]:AMOUNT') -ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.jsn'; +ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.json'; SELECT * FROM t3; WHO WEEK WHAT AMOUNT Joe 4 Beer 19.00 @@ -267,7 +287,7 @@ WHO CHAR(12), WEEK INT(2) FIELD_FORMAT='WEEK:[3]:NUMBER', WHAT CHAR(32) FIELD_FORMAT='WEEK:[3]:EXPENSE:[X]:WHAT', AMOUNT DOUBLE(8,2) FIELD_FORMAT='WEEK:[3]:EXPENSE:[X]:AMOUNT') -ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.jsn'; +ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.json'; SELECT * FROM t4; WHO WEEK WHAT AMOUNT Joe 5 Beer 14.00 @@ -322,7 +342,7 @@ WHO CHAR(12), WEEK INT(2), WHAT CHAR(32) FIELD_FORMAT='EXPENSE:[X]:WHAT', AMOUNT DOUBLE(8,2) FIELD_FORMAT='EXPENSE:[X]:AMOUNT') -ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='mulexp3.jsn'; +ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='mulexp3.json'; SELECT * FROM t2; WHO WEEK WHAT AMOUNT Joe 3 Beer 18.00 @@ -338,7 +358,7 @@ WHO CHAR(12), WEEK INT(2), WHAT CHAR(32) FIELD_FORMAT='EXPENSE:[X]:WHAT', AMOUNT DOUBLE(8,2) FIELD_FORMAT='EXPENSE:[X]:AMOUNT') -ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='mulexp4.jsn'; +ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='mulexp4.json'; SELECT * FROM t3; WHO WEEK WHAT AMOUNT Joe 4 Beer 19.00 @@ -354,7 +374,7 @@ WHO CHAR(12), WEEK INT(2), WHAT CHAR(32) FIELD_FORMAT='EXPENSE:[X]:WHAT', AMOUNT DOUBLE(8,2) FIELD_FORMAT='EXPENSE:[X]:AMOUNT') -ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='mulexp5.jsn'; +ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='mulexp5.json'; SELECT * FROM t4; WHO WEEK WHAT AMOUNT Joe 5 Beer 14.00 @@ -373,7 +393,7 @@ WHO CHAR(12), WEEK INT(2), WHAT CHAR(32) FIELD_FORMAT='EXPENSE:[X]:WHAT', AMOUNT DOUBLE(8,2) FIELD_FORMAT='EXPENSE:[X]:AMOUNT') -ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='mulexp*.jsn' MULTIPLE=1; +ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='mulexp*.json' MULTIPLE=1; SELECT * FROM t1 ORDER BY WHO, WEEK, WHAT, AMOUNT; WHO WEEK WHAT AMOUNT Beth 3 Beer 16.00 @@ -409,7 +429,7 @@ WHO CHAR(12), WEEK INT(2), WHAT CHAR(32) FIELD_FORMAT='EXPENSE:[X]:WHAT', AMOUNT DOUBLE(8,2) FIELD_FORMAT='EXPENSE:[X]:AMOUNT') -ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='mulexp%s.jsn'; +ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='mulexp%s.json'; ALTER TABLE t1 PARTITION BY LIST COLUMNS(WEEK) ( PARTITION `3` VALUES IN(3), diff --git a/storage/connect/mysql-test/connect/r/json_udf.result b/storage/connect/mysql-test/connect/r/json_udf.result new file mode 100644 index 00000000000..1455bac9017 --- /dev/null +++ b/storage/connect/mysql-test/connect/r/json_udf.result @@ -0,0 +1,167 @@ +# +# Test UDF's with constant arguments +# +SELECT Json_Array(); +Json_Array() +[] +SELECT Json_Object(56,3.1416,'foo',NULL); +Json_Object(56,3.1416,'foo',NULL) +{"56":56,"3.1416":3.141600,"foo":"foo","NULL":null} +SELECT Json_Object(56 qty,3.1416 price,'foo' truc, NULL garanty); +Json_Object(56 qty,3.1416 price,'foo' truc, NULL garanty) +{"qty":56,"price":3.141600,"truc":"foo","garanty":null} +SELECT Json_Array(56,3.1416,'My name is "Foo"',NULL); +Json_Array(56,3.1416,'My name is "Foo"',NULL) +[56,3.141600,"My name is \"Foo\"",null] +SELECT Json_Array_Add(Json_Array(56,3.1416,'foo',NULL)) Array; +ERROR HY000: Can't initialize function 'Json_Array_Add'; Json_Value_Add must have at least 2 arguments +SELECT Json_Array_Add(Json_Array(56,3.1416,'foo',NULL),'One more') Array; +Array +[56,3.141600,"foo",null,"One more"] +SELECT Json_Array_Add(Json_Value('one value'),'One more'); +Json_Array_Add(Json_Value('one value'),'One more') +["one value","One more"] +SELECT Json_Array_Add('one value','One more'); +ERROR HY000: Can't initialize function 'Json_Array_Add'; Json_Value_Add first argument must be a json item +SELECT Json_Array_Add('one value' json_,'One more'); +Json_Array_Add('one value' json_,'One more') +[null,"One more"] +Warnings: +Warning 1105 Bad 'o' character near one value +SELECT Json_Value(56,3.1416,'foo',NULL); +ERROR HY000: Can't initialize function 'Json_Value'; Json_Value cannot accept more than 1 argument +SELECT Json_Value(3.1416); +Json_Value(3.1416) +3.141600 +SELECT Json_Value('foo'); +Json_Value('foo') +"foo" +SELECT Json_Value(NULL); +Json_Value(NULL) +null +SELECT Json_Value(); +Json_Value() +null +SELECT Json_Object(); +Json_Object() +{} +SELECT Json_Object(Json_Array(56,3.1416,'foo'),NULL); +Json_Object(Json_Array(56,3.1416,'foo'),NULL) +{"Array(56,3.1416,'foo')":[56,3.141600,"foo"],"NULL":null} +SELECT Json_Array(Json_Array(56,3.1416,'foo'),NULL); +Json_Array(Json_Array(56,3.1416,'foo'),NULL) +[[56,3.141600,"foo"],null] +SELECT Json_Array(Json_Object(56 "qty",3.1416 "price",'foo'),NULL); +Json_Array(Json_Object(56 "qty",3.1416 "price",'foo'),NULL) +[{"qty":56,"price":3.141600,"foo":"foo"},null] +# +# Test UDF's with column arguments +# +CREATE TABLE t1 +( +ISBN CHAR(15), +LANG CHAR(2), +SUBJECT CHAR(32), +AUTHOR CHAR(64), +TITLE CHAR(32), +TRANSLATION CHAR(32), +TRANSLATOR CHAR(80), +PUBLISHER CHAR(32), +DATEPUB int(4) +) ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='biblio.json'; +SELECT Json_Array(AUTHOR, TITLE, DATEPUB) FROM t1; +Json_Array(AUTHOR, TITLE, DATEPUB) +["Jean-Christophe Bernadac","Construire une application XML",1999] +["William J. Pardi","XML en Action",1999] +SELECT Json_Object(AUTHOR, TITLE, DATEPUB) FROM t1; +Json_Object(AUTHOR, TITLE, DATEPUB) +{"AUTHOR":"Jean-Christophe Bernadac","TITLE":"Construire une application XML","DATEPUB":1999} +{"AUTHOR":"William J. Pardi","TITLE":"XML en Action","DATEPUB":1999} +SELECT Json_Array_Grp(TITLE, DATEPUB) FROM t1; +ERROR HY000: Can't initialize function 'Json_Array_Grp'; Json_Array_Grp can only accept 1 argument +SELECT Json_Array_Grp(TITLE) FROM t1; +Json_Array_Grp(TITLE) +["Construire une application XML","XML en Action"] +DROP TABLE t1; +CREATE TABLE t1 ( +SERIALNO CHAR(5) NOT NULL, +NAME VARCHAR(12) NOT NULL FLAG=6, +SEX SMALLINT(1) NOT NULL, +TITLE VARCHAR(15) NOT NULL FLAG=20, +MANAGER CHAR(5) DEFAULT NULL, +DEPARTMENT CHAr(4) NOT NULL FLAG=41, +SECRETARY CHAR(5) DEFAULT NULL FLAG=46, +SALARY DOUBLE(8,2) NOT NULL FLAG=52 +) ENGINE=CONNECT TABLE_TYPE=FIX BLOCK_SIZE=8 FILE_NAME='employee.dat' ENDING=1; +SELECT Json_Object(SERIALNO, NAME, TITLE, SALARY) FROM t1 WHERE NAME = 'MERCHANT'; +Json_Object(SERIALNO, NAME, TITLE, SALARY) +{"SERIALNO":"78943","NAME":"MERCHANT","TITLE":"SALESMAN","SALARY":8700.000000} +SELECT DEPARTMENT, Json_Array_Grp(NAME) FROM t1 GROUP BY DEPARTMENT; +DEPARTMENT Json_Array_Grp(NAME) +0021 ["STRONG","SHORTSIGHT"] +0318 ["BANCROFT","PLUMHEAD","HONEY","TONGHO","WALTER","SHRINKY","WERTHER","MERCHANT","WHEELFOR"] +0319 ["BULLOZER","QUINN","BROWNY","KITTY","MONAPENNY","MARTIN","FUNNIGUY","BUGHAPPY","FODDERMAN","MESSIFUL"] +2452 ["BIGHEAD","ORELLY","BIGHORN","SMITH","CHERRY"] +Warnings: +Warning 1105 Result truncated to json_grp_size values +set connect_json_grp_size=30; +SELECT Json_Array(DEPARTMENT, Json_Array_Grp(NAME)) FROM t1 GROUP BY DEPARTMENT; +Json_Array(DEPARTMENT, Json_Array_Grp(NAME)) +["0021",["STRONG","SHORTSIGHT"]] +["0318",["BANCROFT","PLUMHEAD","HONEY","TONGHO","WALTER","SHRINKY","WERTHER","MERCHANT","WHEELFOR"]] +["0319",["BULLOZER","QUINN","BROWNY","KITTY","MONAPENNY","MARTIN","FUNNIGUY","BUGHAPPY","FODDERMAN","MESSIFUL","GOOSEPEN"]] +["2452",["BIGHEAD","ORELLY","BIGHORN","SMITH","CHERRY"]] +SELECT Json_Object(DEPARTMENT, Json_Array_Grp(NAME) json_NAMES) FROM t1 GROUP BY DEPARTMENT; +Json_Object(DEPARTMENT, Json_Array_Grp(NAME) json_NAMES) +{"DEPARTMENT":"0021","NAMES":["STRONG","SHORTSIGHT"]} +{"DEPARTMENT":"0318","NAMES":["BANCROFT","PLUMHEAD","HONEY","TONGHO","WALTER","SHRINKY","WERTHER","MERCHANT","WHEELFOR"]} +{"DEPARTMENT":"0319","NAMES":["BULLOZER","QUINN","BROWNY","KITTY","MONAPENNY","MARTIN","FUNNIGUY","BUGHAPPY","FODDERMAN","MESSIFUL","GOOSEPEN"]} +{"DEPARTMENT":"2452","NAMES":["BIGHEAD","ORELLY","BIGHORN","SMITH","CHERRY"]} +SELECT Json_Object(DEPARTMENT, Json_Array_Grp(Json_Object(SERIALNO, NAME, TITLE, SALARY)) json_EMPLOYES) FROM t1 GROUP BY DEPARTMENT; +Json_Object(DEPARTMENT, Json_Array_Grp(Json_Object(SERIALNO, NAME, TITLE, SALARY)) json_EMPLOYES) +{"DEPARTMENT":"0021","EMPLOYES":[{"SERIALNO":"87777","NAME":"STRONG","TITLE":"DIRECTOR","SALARY":23000.000000},{"SERIALNO":"22222","NAME":"SHORTSIGHT","TITLE":"SECRETARY","SALARY":5500.000000}]} +{"DEPARTMENT":"0318","EMPLOYES":[{"SERIALNO":"74200","NAME":"BANCROFT","TITLE":"SALESMAN","SALARY":9600.000000},{"SERIALNO":"24888","NAME":"PLUMHEAD","TITLE":"TYPIST","SALARY":2800.000000},{"SERIALNO":"27845","NAME":"HONEY","TITLE":"SECRETARY","SALARY":4900.000000},{"SERIALNO":"73452","NAME":"TONGHO","TITLE":"ENGINEER","SALARY":6800.000000},{"SERIALNO":"74234","NAME":"WALTER","TITLE":"ENGINEER","SALARY":7400.000000},{"SERIALNO":"77777","NAME":"SHRINKY","TITLE":"ADMINISTRATOR","SALARY":7500.000000},{"SERIALNO":"70012","NAME":"WERTHER","TITLE":"DIRECTOR","SALARY":14500.000000},{"SERIALNO":"78943","NAME":"MERCHANT","TITLE":"SALESMAN","SALARY":8700.000000},{"SERIALNO":"73111","NAME":"WHEELFOR","TITLE":"SALESMAN","SALARY":10030.000000}]} +{"DEPARTMENT":"0319","EMPLOYES":[{"SERIALNO":"76543","NAME":"BULLOZER","TITLE":"SALESMAN","SALARY":14800.000000},{"SERIALNO":"40567","NAME":"QUINN","TITLE":"DIRECTOR","SALARY":14000.000000},{"SERIALNO":"00137","NAME":"BROWNY","TITLE":"ENGINEER","SALARY":10500.000000},{"SERIALNO":"12345","NAME":"KITTY","TITLE":"TYPIST","SALARY":3000.450000},{"SERIALNO":"33333","NAME":"MONAPENNY","TITLE":"SECRETARY","SALARY":3800.000000},{"SERIALNO":"00023","NAME":"MARTIN","TITLE":"ENGINEER","SALARY":10000.000000},{"SERIALNO":"07654","NAME":"FUNNIGUY","TITLE":"ADMINISTRATOR","SALARY":8500.000000},{"SERIALNO":"45678","NAME":"BUGHAPPY","TITLE":"PROGRAMMER","SALARY":8500.000000},{"SERIALNO":"56789","NAME":"FODDERMAN","TITLE":"SALESMAN","SALARY":7000.000000},{"SERIALNO":"55555","NAME":"MESSIFUL","TITLE":"SECRETARY","SALARY":5000.500000},{"SERIALNO":"98765","NAME":"GOOSEPEN","TITLE":"ADMINISTRATOR","SALARY":4700.000000}]} +{"DEPARTMENT":"2452","EMPLOYES":[{"SERIALNO":"34567","NAME":"BIGHEAD","TITLE":"SCIENTIST","SALARY":8000.000000},{"SERIALNO":"31416","NAME":"ORELLY","TITLE":"ENGINEER","SALARY":13400.000000},{"SERIALNO":"36666","NAME":"BIGHORN","TITLE":"SCIENTIST","SALARY":11000.000000},{"SERIALNO":"02345","NAME":"SMITH","TITLE":"ENGINEER","SALARY":9000.000000},{"SERIALNO":"11111","NAME":"CHERRY","TITLE":"SECRETARY","SALARY":4500.000000}]} +SELECT Json_Object(DEPARTMENT, TITLE, Json_Array_Grp(Json_Object(SERIALNO, NAME, SALARY)) json_EMPLOYES) FROM t1 GROUP BY DEPARTMENT, TITLE; +Json_Object(DEPARTMENT, TITLE, Json_Array_Grp(Json_Object(SERIALNO, NAME, SALARY)) json_EMPLOYES) +{"DEPARTMENT":"0021","TITLE":"DIRECTOR","EMPLOYES":[{"SERIALNO":"87777","NAME":"STRONG","SALARY":23000.000000}]} +{"DEPARTMENT":"0021","TITLE":"SECRETARY","EMPLOYES":[{"SERIALNO":"22222","NAME":"SHORTSIGHT","SALARY":5500.000000}]} +{"DEPARTMENT":"0318","TITLE":"ADMINISTRATOR","EMPLOYES":[{"SERIALNO":"77777","NAME":"SHRINKY","SALARY":7500.000000}]} +{"DEPARTMENT":"0318","TITLE":"DIRECTOR","EMPLOYES":[{"SERIALNO":"70012","NAME":"WERTHER","SALARY":14500.000000}]} +{"DEPARTMENT":"0318","TITLE":"ENGINEER","EMPLOYES":[{"SERIALNO":"73452","NAME":"TONGHO","SALARY":6800.000000},{"SERIALNO":"74234","NAME":"WALTER","SALARY":7400.000000}]} +{"DEPARTMENT":"0318","TITLE":"SALESMAN","EMPLOYES":[{"SERIALNO":"74200","NAME":"BANCROFT","SALARY":9600.000000},{"SERIALNO":"78943","NAME":"MERCHANT","SALARY":8700.000000},{"SERIALNO":"73111","NAME":"WHEELFOR","SALARY":10030.000000}]} +{"DEPARTMENT":"0318","TITLE":"SECRETARY","EMPLOYES":[{"SERIALNO":"27845","NAME":"HONEY","SALARY":4900.000000}]} +{"DEPARTMENT":"0318","TITLE":"TYPIST","EMPLOYES":[{"SERIALNO":"24888","NAME":"PLUMHEAD","SALARY":2800.000000}]} +{"DEPARTMENT":"0319","TITLE":"ADMINISTRATOR","EMPLOYES":[{"SERIALNO":"98765","NAME":"GOOSEPEN","SALARY":4700.000000},{"SERIALNO":"07654","NAME":"FUNNIGUY","SALARY":8500.000000}]} +{"DEPARTMENT":"0319","TITLE":"DIRECTOR","EMPLOYES":[{"SERIALNO":"40567","NAME":"QUINN","SALARY":14000.000000}]} +{"DEPARTMENT":"0319","TITLE":"ENGINEER","EMPLOYES":[{"SERIALNO":"00023","NAME":"MARTIN","SALARY":10000.000000},{"SERIALNO":"00137","NAME":"BROWNY","SALARY":10500.000000}]} +{"DEPARTMENT":"0319","TITLE":"PROGRAMMER","EMPLOYES":[{"SERIALNO":"45678","NAME":"BUGHAPPY","SALARY":8500.000000}]} +{"DEPARTMENT":"0319","TITLE":"SALESMAN","EMPLOYES":[{"SERIALNO":"76543","NAME":"BULLOZER","SALARY":14800.000000},{"SERIALNO":"56789","NAME":"FODDERMAN","SALARY":7000.000000}]} +{"DEPARTMENT":"0319","TITLE":"SECRETARY","EMPLOYES":[{"SERIALNO":"33333","NAME":"MONAPENNY","SALARY":3800.000000},{"SERIALNO":"55555","NAME":"MESSIFUL","SALARY":5000.500000}]} +{"DEPARTMENT":"0319","TITLE":"TYPIST","EMPLOYES":[{"SERIALNO":"12345","NAME":"KITTY","SALARY":3000.450000}]} +{"DEPARTMENT":"2452","TITLE":"ENGINEER","EMPLOYES":[{"SERIALNO":"31416","NAME":"ORELLY","SALARY":13400.000000},{"SERIALNO":"02345","NAME":"SMITH","SALARY":9000.000000}]} +{"DEPARTMENT":"2452","TITLE":"SCIENTIST","EMPLOYES":[{"SERIALNO":"34567","NAME":"BIGHEAD","SALARY":8000.000000},{"SERIALNO":"36666","NAME":"BIGHORN","SALARY":11000.000000}]} +{"DEPARTMENT":"2452","TITLE":"SECRETARY","EMPLOYES":[{"SERIALNO":"11111","NAME":"CHERRY","SALARY":4500.000000}]} +SELECT Json_Object_Grp(SALARY) FROM t1; +ERROR HY000: Can't initialize function 'Json_Object_Grp'; Json_Array_Grp can only accept 2 arguments +SELECT Json_Object_Grp(SALARY, NAME) FROM t1; +Json_Object_Grp(SALARY, NAME) +{"BANCROFT":9600.000000,"SMITH":9000.000000,"MERCHANT":8700.000000,"FUNNIGUY":8500.000000,"BUGHAPPY":8500.000000,"BIGHEAD":8000.000000,"SHRINKY":7500.000000,"WALTER":7400.000000,"FODDERMAN":7000.000000,"TONGHO":6800.000000,"SHORTSIGHT":5500.000000,"MESSIFUL":5000.500000,"HONEY":4900.000000,"GOOSEPEN":4700.000000,"CHERRY":4500.000000,"MONAPENNY":3800.000000,"KITTY":3000.450000,"PLUMHEAD":2800.000000,"STRONG":23000.000000,"BULLOZER":14800.000000,"WERTHER":14500.000000,"QUINN":14000.000000,"ORELLY":13400.000000,"BIGHORN":11000.000000,"BROWNY":10500.000000,"WHEELFOR":10030.000000,"MARTIN":10000.000000} +SELECT Json_Object(DEPARTMENT, Json_Object_Grp(SALARY, NAME) "Json_SALARIES") FROM t1 GROUP BY DEPARTMENT; +Json_Object(DEPARTMENT, Json_Object_Grp(SALARY, NAME) "Json_SALARIES") +{"DEPARTMENT":"0021","SALARIES":{"STRONG":23000.000000,"SHORTSIGHT":5500.000000}} +{"DEPARTMENT":"0318","SALARIES":{"BANCROFT":9600.000000,"PLUMHEAD":2800.000000,"HONEY":4900.000000,"TONGHO":6800.000000,"WALTER":7400.000000,"SHRINKY":7500.000000,"WERTHER":14500.000000,"MERCHANT":8700.000000,"WHEELFOR":10030.000000}} +{"DEPARTMENT":"0319","SALARIES":{"BULLOZER":14800.000000,"QUINN":14000.000000,"BROWNY":10500.000000,"KITTY":3000.450000,"MONAPENNY":3800.000000,"MARTIN":10000.000000,"FUNNIGUY":8500.000000,"BUGHAPPY":8500.000000,"FODDERMAN":7000.000000,"MESSIFUL":5000.500000,"GOOSEPEN":4700.000000}} +{"DEPARTMENT":"2452","SALARIES":{"BIGHEAD":8000.000000,"ORELLY":13400.000000,"BIGHORN":11000.000000,"SMITH":9000.000000,"CHERRY":4500.000000}} +SELECT Json_Array_Grp(NAME) from t1; +Json_Array_Grp(NAME) +["BANCROFT","SMITH","MERCHANT","FUNNIGUY","BUGHAPPY","BIGHEAD","SHRINKY","WALTER","FODDERMAN","TONGHO","SHORTSIGHT","MESSIFUL","HONEY","GOOSEPEN","CHERRY","MONAPENNY","KITTY","PLUMHEAD","STRONG","BULLOZER","WERTHER","QUINN","ORELLY","BIGHORN","BROWNY","WHEELFOR","MARTIN"] +DROP TABLE t1; +DROP FUNCTION Json_Array; +DROP FUNCTION Json_Array_Add; +DROP FUNCTION Json_Object; +DROP FUNCTION Json_Object_Nonull; +DROP FUNCTION Json_Value; +DROP FUNCTION Json_Array_Grp; +DROP FUNCTION Json_Object_Grp; diff --git a/storage/connect/mysql-test/connect/r/odbc_oracle.result b/storage/connect/mysql-test/connect/r/odbc_oracle.result index fff2f192184..96d8e53b8e5 100644 --- a/storage/connect/mysql-test/connect/r/odbc_oracle.result +++ b/storage/connect/mysql-test/connect/r/odbc_oracle.result @@ -126,7 +126,7 @@ TABNAME='T1'; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( - `A` decimal(38,0) DEFAULT NULL, + `A` decimal(40,0) DEFAULT NULL, `B` double(40,0) DEFAULT NULL ) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='DSN=ConnectEngineOracle;UID=mtr;PWD=mtr' `TABLE_TYPE`='ODBC' `TABNAME`='T1' SELECT * FROM t1 ORDER BY A; @@ -138,7 +138,7 @@ CREATE TABLE t2 AS SELECT * FROM t1; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( - `A` decimal(38,0) DEFAULT NULL, + `A` decimal(40,0) DEFAULT NULL, `B` double(40,0) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT * FROM t2; @@ -162,7 +162,7 @@ TABNAME='MTR.T1'; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( - `A` decimal(38,0) DEFAULT NULL, + `A` decimal(40,0) DEFAULT NULL, `B` double(40,0) DEFAULT NULL ) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='DSN=ConnectEngineOracle;UID=mtr;PWD=mtr' `TABLE_TYPE`='ODBC' `TABNAME`='MTR.T1' SELECT * FROM t1; @@ -178,7 +178,7 @@ TABNAME='MTR.V1'; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( - `A` decimal(38,0) DEFAULT NULL, + `A` decimal(40,0) DEFAULT NULL, `B` double(40,0) DEFAULT NULL ) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='DSN=ConnectEngineOracle;UID=mtr;PWD=mtr' `TABLE_TYPE`='ODBC' `TABNAME`='MTR.V1' SELECT * FROM t1; @@ -190,7 +190,7 @@ CREATE TABLE t2 AS SELECT * FROM t1; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( - `A` decimal(38,0) DEFAULT NULL, + `A` decimal(40,0) DEFAULT NULL, `B` double(40,0) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT * FROM t2; diff --git a/storage/connect/mysql-test/connect/std_data/biblio.jsn b/storage/connect/mysql-test/connect/std_data/biblio.json index bab8fd24305..bab8fd24305 100644 --- a/storage/connect/mysql-test/connect/std_data/biblio.jsn +++ b/storage/connect/mysql-test/connect/std_data/biblio.json diff --git a/storage/connect/mysql-test/connect/std_data/expense.jsn b/storage/connect/mysql-test/connect/std_data/expense.json index e65ad5261f1..e65ad5261f1 100644 --- a/storage/connect/mysql-test/connect/std_data/expense.jsn +++ b/storage/connect/mysql-test/connect/std_data/expense.json diff --git a/storage/connect/mysql-test/connect/std_data/mulexp3.jsn b/storage/connect/mysql-test/connect/std_data/mulexp3.json index c228448b073..c228448b073 100644 --- a/storage/connect/mysql-test/connect/std_data/mulexp3.jsn +++ b/storage/connect/mysql-test/connect/std_data/mulexp3.json diff --git a/storage/connect/mysql-test/connect/std_data/mulexp4.jsn b/storage/connect/mysql-test/connect/std_data/mulexp4.json index 0e43ffec07b..0e43ffec07b 100644 --- a/storage/connect/mysql-test/connect/std_data/mulexp4.jsn +++ b/storage/connect/mysql-test/connect/std_data/mulexp4.json diff --git a/storage/connect/mysql-test/connect/std_data/mulexp5.jsn b/storage/connect/mysql-test/connect/std_data/mulexp5.json index 7a707506c2f..7a707506c2f 100644 --- a/storage/connect/mysql-test/connect/std_data/mulexp5.jsn +++ b/storage/connect/mysql-test/connect/std_data/mulexp5.json diff --git a/storage/connect/mysql-test/connect/t/json.test b/storage/connect/mysql-test/connect/t/json.test index 91cb6308557..79588e9fe5b 100644 --- a/storage/connect/mysql-test/connect/t/json.test +++ b/storage/connect/mysql-test/connect/t/json.test @@ -3,11 +3,11 @@ let $MYSQLD_DATADIR= `select @@datadir`;
---copy_file $MTR_SUITE_DIR/std_data/biblio.jsn $MYSQLD_DATADIR/test/biblio.jsn
---copy_file $MTR_SUITE_DIR/std_data/expense.jsn $MYSQLD_DATADIR/test/expense.jsn
---copy_file $MTR_SUITE_DIR/std_data/mulexp3.jsn $MYSQLD_DATADIR/test/mulexp3.jsn
---copy_file $MTR_SUITE_DIR/std_data/mulexp4.jsn $MYSQLD_DATADIR/test/mulexp4.jsn
---copy_file $MTR_SUITE_DIR/std_data/mulexp5.jsn $MYSQLD_DATADIR/test/mulexp5.jsn
+--copy_file $MTR_SUITE_DIR/std_data/biblio.json $MYSQLD_DATADIR/test/biblio.json
+--copy_file $MTR_SUITE_DIR/std_data/expense.json $MYSQLD_DATADIR/test/expense.json
+--copy_file $MTR_SUITE_DIR/std_data/mulexp3.json $MYSQLD_DATADIR/test/mulexp3.json
+--copy_file $MTR_SUITE_DIR/std_data/mulexp4.json $MYSQLD_DATADIR/test/mulexp4.json
+--copy_file $MTR_SUITE_DIR/std_data/mulexp5.json $MYSQLD_DATADIR/test/mulexp5.json
--echo #
--echo # Testing doc samples
@@ -23,7 +23,7 @@ CREATE TABLE t1 TRANSLATOR CHAR(80),
PUBLISHER CHAR(32),
DATEPUB int(4)
-) ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='biblio.jsn';
+) ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='biblio.json';
SELECT * FROM t1;
DROP TABLE t1;
@@ -44,7 +44,7 @@ CREATE TABLE t1 Location CHAR(16) FIELD_FORMAT='PUBLISHER:PLACE',
Year int(4) FIELD_FORMAT='DATEPUB'
)
-ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='biblio.jsn';
+ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='biblio.json';
SELECT * FROM t1;
DROP TABLE t1;
@@ -65,7 +65,7 @@ CREATE TABLE t1 Location CHAR(16) FIELD_FORMAT='PUBLISHER:PLACE',
Year int(4) FIELD_FORMAT='DATEPUB'
)
-ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='biblio.jsn';
+ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='biblio.json';
SELECT * FROM t1;
DROP TABLE t1;
@@ -86,7 +86,7 @@ CREATE TABLE t1 Location CHAR(16) FIELD_FORMAT='PUBLISHER:PLACE',
Year int(4) FIELD_FORMAT='DATEPUB'
)
-ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='biblio.jsn';
+ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='biblio.json';
SELECT * FROM t1;
UPDATE t1 SET AuthorFN = 'Philippe' WHERE AuthorLN = 'Knab';
SELECT * FROM t1 WHERE ISBN = '9782212090819';
@@ -97,7 +97,7 @@ SELECT * FROM t1 WHERE ISBN = '9782212090819'; CREATE TABLE t2 (
FIRSTNAME CHAR(32),
LASTNAME CHAR(32))
-ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='biblio.jsn' OPTION_LIST='Object=[2]:AUTHOR';
+ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='biblio.json' OPTION_LIST='Object=[2]:AUTHOR';
SELECT * FROM t2;
INSERT INTO t2 VALUES('Charles','Dickens');
SELECT * FROM t1;
@@ -111,7 +111,7 @@ CREATE TABLE t1 (
line char(255)
)
-ENGINE=CONNECT TABLE_TYPE=DOS FILE_NAME='biblio.jsn';
+ENGINE=CONNECT TABLE_TYPE=DOS FILE_NAME='biblio.json';
SELECT * FROM t1;
DROP TABLE t1;
@@ -123,7 +123,7 @@ WHO CHAR(12), WEEK INT(2) FIELD_FORMAT='WEEK:[X]:NUMBER',
WHAT CHAR(32) FIELD_FORMAT='WEEK::EXPENSE:["+"]:WHAT',
AMOUNT DOUBLE(8,2) FIELD_FORMAT='WEEK::EXPENSE:[+]:AMOUNT')
-ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.jsn';
+ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.json';
SELECT * FROM t1;
DROP TABLE t1;
@@ -135,12 +135,29 @@ WHO CHAR(12), WEEK INT(2) FIELD_FORMAT='WEEK:[X]:NUMBER',
WHAT CHAR(32) FIELD_FORMAT='WEEK:[X]:EXPENSE:[X]:WHAT',
AMOUNT DOUBLE(8,2) FIELD_FORMAT='WEEK:[X]:EXPENSE:[X]:AMOUNT')
-ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.jsn';
+ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.json';
#--error ER_GET_ERRMSG
SELECT * FROM t1;
DROP TABLE t1;
--echo #
+--echo # A table showing many calculated results
+--echo #
+CREATE TABLE t1 (
+WHO CHAR(12) NOT NULL,
+WEEKS CHAR(12) NOT NULL FIELD_FORMAT='WEEK:[", "]:NUMBER',
+SUMS CHAR(64) NOT NULL FIELD_FORMAT='WEEK:["+"]:EXPENSE:[+]:AMOUNT',
+SUM DOUBLE(8,2) NOT NULL FIELD_FORMAT='WEEK:[+]:EXPENSE:[+]:AMOUNT',
+AVGS CHAR(64) NOT NULL FIELD_FORMAT='WEEK:["+"]:EXPENSE:[!]:AMOUNT',
+SUMAVG DOUBLE(8,2) NOT NULL FIELD_FORMAT='WEEK:[+]:EXPENSE:[!]:AMOUNT',
+AVGSUM DOUBLE(8,2) NOT NULL FIELD_FORMAT='WEEK:[!]:EXPENSE:[+]:AMOUNT',
+AVGAVG DOUBLE(8,2) NOT NULL FIELD_FORMAT='WEEK:[!]:EXPENSE:[!]:AMOUNT',
+AVERAGE DOUBLE(8,2) NOT NULL FIELD_FORMAT='WEEK:[!]:EXPENSE:[X]:AMOUNT')
+ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.json';
+SELECT * FROM t1;
+DROP TABLE t1;
+
+--echo #
--echo # Expand expense in 3 one week tables
--echo #
CREATE TABLE t2 (
@@ -148,7 +165,7 @@ WHO CHAR(12), WEEK INT(2) FIELD_FORMAT='WEEK:[1]:NUMBER',
WHAT CHAR(32) FIELD_FORMAT='WEEK:[1]:EXPENSE:[X]:WHAT',
AMOUNT DOUBLE(8,2) FIELD_FORMAT='WEEK:[1]:EXPENSE:[X]:AMOUNT')
-ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.jsn';
+ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.json';
SELECT * FROM t2;
CREATE TABLE t3 (
@@ -156,7 +173,7 @@ WHO CHAR(12), WEEK INT(2) FIELD_FORMAT='WEEK:[2]:NUMBER',
WHAT CHAR(32) FIELD_FORMAT='WEEK:[2]:EXPENSE:[X]:WHAT',
AMOUNT DOUBLE(8,2) FIELD_FORMAT='WEEK:[2]:EXPENSE:[X]:AMOUNT')
-ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.jsn';
+ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.json';
SELECT * FROM t3;
CREATE TABLE t4 (
@@ -164,7 +181,7 @@ WHO CHAR(12), WEEK INT(2) FIELD_FORMAT='WEEK:[3]:NUMBER',
WHAT CHAR(32) FIELD_FORMAT='WEEK:[3]:EXPENSE:[X]:WHAT',
AMOUNT DOUBLE(8,2) FIELD_FORMAT='WEEK:[3]:EXPENSE:[X]:AMOUNT')
-ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.jsn';
+ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.json';
SELECT * FROM t4;
--echo #
@@ -187,7 +204,7 @@ WHO CHAR(12), WEEK INT(2),
WHAT CHAR(32) FIELD_FORMAT='EXPENSE:[X]:WHAT',
AMOUNT DOUBLE(8,2) FIELD_FORMAT='EXPENSE:[X]:AMOUNT')
-ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='mulexp3.jsn';
+ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='mulexp3.json';
SELECT * FROM t2;
CREATE TABLE t3 (
@@ -195,7 +212,7 @@ WHO CHAR(12), WEEK INT(2),
WHAT CHAR(32) FIELD_FORMAT='EXPENSE:[X]:WHAT',
AMOUNT DOUBLE(8,2) FIELD_FORMAT='EXPENSE:[X]:AMOUNT')
-ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='mulexp4.jsn';
+ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='mulexp4.json';
SELECT * FROM t3;
CREATE TABLE t4 (
@@ -203,7 +220,7 @@ WHO CHAR(12), WEEK INT(2),
WHAT CHAR(32) FIELD_FORMAT='EXPENSE:[X]:WHAT',
AMOUNT DOUBLE(8,2) FIELD_FORMAT='EXPENSE:[X]:AMOUNT')
-ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='mulexp5.jsn';
+ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='mulexp5.json';
SELECT * FROM t4;
--echo #
@@ -214,7 +231,7 @@ WHO CHAR(12), WEEK INT(2),
WHAT CHAR(32) FIELD_FORMAT='EXPENSE:[X]:WHAT',
AMOUNT DOUBLE(8,2) FIELD_FORMAT='EXPENSE:[X]:AMOUNT')
-ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='mulexp*.jsn' MULTIPLE=1;
+ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='mulexp*.json' MULTIPLE=1;
SELECT * FROM t1 ORDER BY WHO, WEEK, WHAT, AMOUNT;
DROP TABLE t1;
@@ -226,7 +243,7 @@ WHO CHAR(12), WEEK INT(2),
WHAT CHAR(32) FIELD_FORMAT='EXPENSE:[X]:WHAT',
AMOUNT DOUBLE(8,2) FIELD_FORMAT='EXPENSE:[X]:AMOUNT')
-ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='mulexp%s.jsn';
+ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='mulexp%s.json';
ALTER TABLE t1
PARTITION BY LIST COLUMNS(WEEK) (
PARTITION `3` VALUES IN(3),
@@ -240,8 +257,8 @@ DROP TABLE t1, t2, t3, t4; #
# Clean up
#
---remove_file $MYSQLD_DATADIR/test/biblio.jsn
---remove_file $MYSQLD_DATADIR/test/expense.jsn
---remove_file $MYSQLD_DATADIR/test/mulexp3.jsn
---remove_file $MYSQLD_DATADIR/test/mulexp4.jsn
---remove_file $MYSQLD_DATADIR/test/mulexp5.jsn
+--remove_file $MYSQLD_DATADIR/test/biblio.json
+--remove_file $MYSQLD_DATADIR/test/expense.json
+--remove_file $MYSQLD_DATADIR/test/mulexp3.json
+--remove_file $MYSQLD_DATADIR/test/mulexp4.json
+--remove_file $MYSQLD_DATADIR/test/mulexp5.json
diff --git a/storage/connect/mysql-test/connect/t/json_udf.inc b/storage/connect/mysql-test/connect/t/json_udf.inc new file mode 100644 index 00000000000..572f8b58c72 --- /dev/null +++ b/storage/connect/mysql-test/connect/t/json_udf.inc @@ -0,0 +1,33 @@ +--disable_query_log
+#
+# Check if server has support for loading plugins
+#
+if (`SELECT @@have_dynamic_loading != 'YES'`) {
+ --skip UDF requires dynamic loading
+}
+
+let $is_win = `select convert(@@version_compile_os using latin1) IN ("Win32","Win64","Windows")`;
+
+if ($is_win)
+{
+--eval CREATE FUNCTION Json_Array RETURNS STRING SONAME 'ha_connect.dll';
+--eval CREATE FUNCTION Json_Array_Add RETURNS STRING SONAME 'ha_connect.dll';
+--eval CREATE FUNCTION Json_Object RETURNS STRING SONAME 'ha_connect.dll';
+--eval CREATE FUNCTION Json_Object_Nonull RETURNS STRING SONAME 'ha_connect.dll';
+--eval CREATE FUNCTION Json_Value returns STRING SONAME 'ha_connect.dll';
+--eval CREATE AGGREGATE FUNCTION Json_Array_Grp RETURNS STRING SONAME 'ha_connect.dll';
+--eval CREATE AGGREGATE FUNCTION Json_Object_Grp RETURNS STRING SONAME 'ha_connect.dll';
+}
+
+if (!$is_win)
+{
+--eval CREATE FUNCTION Json_Array RETURNS STRING SONAME 'ha_connect.so';
+--eval CREATE FUNCTION Json_Array_Add RETURNS STRING SONAME 'ha_connect.so';
+--eval CREATE FUNCTION Json_Object RETURNS STRING SONAME 'ha_connect.so';
+--eval CREATE FUNCTION Json_Object_Nonull RETURNS STRING SONAME 'ha_connect.so';
+--eval CREATE FUNCTION Json_Value returns STRING SONAME 'ha_connect.so';
+--eval CREATE AGGREGATE FUNCTION Json_Array_Grp RETURNS STRING SONAME 'ha_connect.so';
+--eval CREATE AGGREGATE FUNCTION Json_Object_Grp RETURNS STRING SONAME 'ha_connect.so';
+}
+--enable_query_log
+
diff --git a/storage/connect/mysql-test/connect/t/json_udf.test b/storage/connect/mysql-test/connect/t/json_udf.test new file mode 100644 index 00000000000..c5510bf9cc0 --- /dev/null +++ b/storage/connect/mysql-test/connect/t/json_udf.test @@ -0,0 +1,93 @@ +--source json_udf.inc
+
+let $MYSQLD_DATADIR= `select @@datadir`;
+
+--copy_file $MTR_SUITE_DIR/std_data/biblio.json $MYSQLD_DATADIR/test/biblio.json
+--copy_file $MTR_SUITE_DIR/std_data/employee.dat $MYSQLD_DATADIR/test/employee.dat
+
+--echo #
+--echo # Test UDF's with constant arguments
+--echo #
+SELECT Json_Array();
+SELECT Json_Object(56,3.1416,'foo',NULL);
+SELECT Json_Object(56 qty,3.1416 price,'foo' truc, NULL garanty);
+SELECT Json_Array(56,3.1416,'My name is "Foo"',NULL);
+--error ER_CANT_INITIALIZE_UDF
+SELECT Json_Array_Add(Json_Array(56,3.1416,'foo',NULL)) Array;
+SELECT Json_Array_Add(Json_Array(56,3.1416,'foo',NULL),'One more') Array;
+SELECT Json_Array_Add(Json_Value('one value'),'One more');
+--error ER_CANT_INITIALIZE_UDF
+SELECT Json_Array_Add('one value','One more');
+SELECT Json_Array_Add('one value' json_,'One more');
+--error ER_CANT_INITIALIZE_UDF
+SELECT Json_Value(56,3.1416,'foo',NULL);
+SELECT Json_Value(3.1416);
+SELECT Json_Value('foo');
+SELECT Json_Value(NULL);
+SELECT Json_Value();
+SELECT Json_Object();
+SELECT Json_Object(Json_Array(56,3.1416,'foo'),NULL);
+SELECT Json_Array(Json_Array(56,3.1416,'foo'),NULL);
+SELECT Json_Array(Json_Object(56 "qty",3.1416 "price",'foo'),NULL);
+
+--echo #
+--echo # Test UDF's with column arguments
+--echo #
+CREATE TABLE t1
+(
+ ISBN CHAR(15),
+ LANG CHAR(2),
+ SUBJECT CHAR(32),
+ AUTHOR CHAR(64),
+ TITLE CHAR(32),
+ TRANSLATION CHAR(32),
+ TRANSLATOR CHAR(80),
+ PUBLISHER CHAR(32),
+ DATEPUB int(4)
+) ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='biblio.json';
+
+SELECT Json_Array(AUTHOR, TITLE, DATEPUB) FROM t1;
+SELECT Json_Object(AUTHOR, TITLE, DATEPUB) FROM t1;
+--error ER_CANT_INITIALIZE_UDF
+SELECT Json_Array_Grp(TITLE, DATEPUB) FROM t1;
+SELECT Json_Array_Grp(TITLE) FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (
+ SERIALNO CHAR(5) NOT NULL,
+ NAME VARCHAR(12) NOT NULL FLAG=6,
+ SEX SMALLINT(1) NOT NULL,
+ TITLE VARCHAR(15) NOT NULL FLAG=20,
+ MANAGER CHAR(5) DEFAULT NULL,
+ DEPARTMENT CHAr(4) NOT NULL FLAG=41,
+ SECRETARY CHAR(5) DEFAULT NULL FLAG=46,
+ SALARY DOUBLE(8,2) NOT NULL FLAG=52
+) ENGINE=CONNECT TABLE_TYPE=FIX BLOCK_SIZE=8 FILE_NAME='employee.dat' ENDING=1;
+
+SELECT Json_Object(SERIALNO, NAME, TITLE, SALARY) FROM t1 WHERE NAME = 'MERCHANT';
+SELECT DEPARTMENT, Json_Array_Grp(NAME) FROM t1 GROUP BY DEPARTMENT;
+set connect_json_grp_size=30;
+SELECT Json_Array(DEPARTMENT, Json_Array_Grp(NAME)) FROM t1 GROUP BY DEPARTMENT;
+SELECT Json_Object(DEPARTMENT, Json_Array_Grp(NAME) json_NAMES) FROM t1 GROUP BY DEPARTMENT;
+SELECT Json_Object(DEPARTMENT, Json_Array_Grp(Json_Object(SERIALNO, NAME, TITLE, SALARY)) json_EMPLOYES) FROM t1 GROUP BY DEPARTMENT;
+SELECT Json_Object(DEPARTMENT, TITLE, Json_Array_Grp(Json_Object(SERIALNO, NAME, SALARY)) json_EMPLOYES) FROM t1 GROUP BY DEPARTMENT, TITLE;
+--error ER_CANT_INITIALIZE_UDF
+SELECT Json_Object_Grp(SALARY) FROM t1;
+SELECT Json_Object_Grp(SALARY, NAME) FROM t1;
+SELECT Json_Object(DEPARTMENT, Json_Object_Grp(SALARY, NAME) "Json_SALARIES") FROM t1 GROUP BY DEPARTMENT;
+SELECT Json_Array_Grp(NAME) from t1;
+DROP TABLE t1;
+
+DROP FUNCTION Json_Array;
+DROP FUNCTION Json_Array_Add;
+DROP FUNCTION Json_Object;
+DROP FUNCTION Json_Object_Nonull;
+DROP FUNCTION Json_Value;
+DROP FUNCTION Json_Array_Grp;
+DROP FUNCTION Json_Object_Grp;
+
+#
+# Clean up
+#
+--remove_file $MYSQLD_DATADIR/test/biblio.json
+--remove_file $MYSQLD_DATADIR/test/employee.dat
diff --git a/storage/connect/odbconn.cpp b/storage/connect/odbconn.cpp index fec14820ef6..ea20672eb29 100644 --- a/storage/connect/odbconn.cpp +++ b/storage/connect/odbconn.cpp @@ -102,7 +102,12 @@ static int GetSQLCType(int type) case TYPE_BIGINT: tp = SQL_C_SBIGINT; break; case TYPE_DOUBLE: tp = SQL_C_DOUBLE; break; case TYPE_TINY : tp = SQL_C_TINYINT; break; +//#if (ODBCVER >= 0x0300) +// case TYPE_DECIM: tp = SQL_C_NUMERIC; break; (CRASH!!!) +//#else case TYPE_DECIM: tp = SQL_C_CHAR; break; +//#endif + } // endswitch type return tp; @@ -923,13 +928,13 @@ ODBConn::ODBConn(PGLOBAL g, TDBODBC *tdbp) m_RowsetSize = (DWORD)((tdbp) ? tdbp->Rows : 10); m_Catver = (tdbp) ? tdbp->Catver : 0; m_Rows = 0; + m_Fetch = 0; m_Connect = NULL; m_User = NULL; m_Pwd = NULL; m_Updatable = true; m_Transact = false; m_Scrollable = (tdbp) ? tdbp->Scrollable : false; - m_First = true; m_Full = false; m_UseCnc = false; m_IDQuoteChar[0] = '"'; @@ -984,7 +989,7 @@ void ODBConn::ThrowDBX(RETCODE rc, PSZ msg, HSTMT hstmt) void ODBConn::ThrowDBX(PSZ msg) { - DBX* xp = new(m_G) DBX(0, msg); + DBX* xp = new(m_G) DBX(0, "Error"); xp->m_ErrMsg[0] = msg; throw xp; @@ -1099,8 +1104,7 @@ int ODBConn::Open(PSZ ConnectString, POPARM sop, DWORD options) // VerifyConnect(); Deprecated GetConnectInfo(); } catch(DBX *xp) { -// strcpy(g->Message, xp->m_ErrMsg[0]); - strcpy(g->Message, xp->GetErrorMessage(0)); + sprintf(g->Message, "%s: %s", xp->m_Msg, xp->GetErrorMessage(0)); Close(); // Free(); return -1; @@ -1356,7 +1360,7 @@ int ODBConn::ExecDirectSQL(char *sql, ODBCCOL *tocols) rc = SQLFreeStmt(m_hstmt, SQL_CLOSE); if (!Check(rc)) - ThrowDBX(rc, "SQLFreeStmt"); + ThrowDBX(rc, "SQLFreeStmt", m_hstmt); m_hstmt = NULL; } // endif m_hstmt @@ -1371,7 +1375,7 @@ int ODBConn::ExecDirectSQL(char *sql, ODBCCOL *tocols) (void*)SQL_SCROLLABLE, 0); if (!Check(rc)) - ThrowDBX(rc, "SQLSetStmtAttr"); + ThrowDBX(rc, "Scrollable", hstmt); } // endif m_Scrollable @@ -1422,7 +1426,7 @@ int ODBConn::ExecDirectSQL(char *sql, ODBCCOL *tocols) for (n = 0, colp = tocols; colp; colp = (PODBCCOL)colp->GetNext()) if (!colp->IsSpecial()) - n++; + n++; // n can be 0 for query such as Select count(*) from table if (n && n != (UWORD)ncol) @@ -1458,7 +1462,7 @@ int ODBConn::ExecDirectSQL(char *sql, ODBCCOL *tocols) for (int i = 0; i < MAX_NUM_OF_MSG && x->m_ErrMsg[i]; i++) htrc(x->m_ErrMsg[i]); - strcpy(m_G->Message, x->GetErrorMessage(0)); + sprintf(m_G->Message, "%s: %s", x->m_Msg, x->GetErrorMessage(0)); if (b) SQLCancel(hstmt); @@ -1521,7 +1525,7 @@ int ODBConn::GetResultSize(char *sql, ODBCCOL *colp) /***********************************************************************/ /* Fetch next row. */ /***********************************************************************/ -int ODBConn::Fetch() +int ODBConn::Fetch(int pos) { ASSERT(m_hstmt); int irc; @@ -1531,7 +1535,9 @@ int ODBConn::Fetch() try { // do { - if (m_RowsetSize) { + if (pos) { + rc = SQLExtendedFetch(m_hstmt, SQL_FETCH_ABSOLUTE, pos, &crow, NULL); + } else if (m_RowsetSize) { rc = SQLExtendedFetch(m_hstmt, SQL_FETCH_NEXT, 1, &crow, NULL); } else { rc = SQLFetch(m_hstmt); @@ -1544,29 +1550,22 @@ int ODBConn::Fetch() m_hstmt, m_RowsetSize, rc); if (!Check(rc)) - ThrowDBX(rc, "Fetch", m_hstmt); - - irc = (rc == SQL_NO_DATA_FOUND) ? 0 : (int)crow; - - if (m_First) { - // First fetch. Check whether the full table was read - if ((m_Full = irc < (signed)m_RowsetSize)) { - m_Tdb->Memory = 0; // Not needed anymore - m_Rows = irc; // Table size - } // endif m_Full - - m_First = false; - } // endif m_First + ThrowDBX(rc, "Fetching", m_hstmt); - if (m_Tdb->Memory == 1) - m_Rows += irc; + if (rc == SQL_NO_DATA_FOUND) { + m_Full = (m_Fetch == 1); + irc = 0; + } else + irc = (int)crow; + m_Fetch++; + m_Rows += irc; } catch(DBX *x) { if (trace) for (int i = 0; i < MAX_NUM_OF_MSG && x->m_ErrMsg[i]; i++) htrc(x->m_ErrMsg[i]); - strcpy(g->Message, x->GetErrorMessage(0)); + sprintf(g->Message, "%s: %s", x->m_Msg, x->GetErrorMessage(0)); irc = -1; } // end try/catch @@ -1602,7 +1601,7 @@ int ODBConn::PrepareSQL(char *sql) for (int i = 0; i < MAX_NUM_OF_MSG && x->m_ErrMsg[i]; i++) htrc(x->m_ErrMsg[i]); - strcpy(g->Message, x->GetErrorMessage(0)); + sprintf(g->Message, "%s: %s", x->m_Msg, x->GetErrorMessage(0)); } // end try/catch } // endif Mode @@ -1648,7 +1647,7 @@ int ODBConn::PrepareSQL(char *sql) for (int i = 0; i < MAX_NUM_OF_MSG && x->m_ErrMsg[i]; i++) htrc(x->m_ErrMsg[i]); - strcpy(g->Message, x->GetErrorMessage(0)); + sprintf(g->Message, "%s: %s", x->m_Msg, x->GetErrorMessage(0)); if (b) SQLCancel(hstmt); @@ -1700,7 +1699,7 @@ int ODBConn::ExecuteSQL(void) } // endif ncol } catch(DBX *x) { - strcpy(m_G->Message, x->GetErrorMessage(0)); + sprintf(m_G->Message, "%s: %s", x->m_Msg, x->GetErrorMessage(0)); SQLCancel(m_hstmt); rc = SQLFreeStmt(m_hstmt, SQL_DROP); m_hstmt = NULL; @@ -1737,7 +1736,7 @@ bool ODBConn::BindParam(ODBCCOL *colp) ThrowDBX(rc, "SQLDescribeParam", m_hstmt); } catch(DBX *x) { - strcpy(m_G->Message, x->GetErrorMessage(0)); + sprintf(m_G->Message, "%s: %s", x->m_Msg, x->GetErrorMessage(0)); colsize = colp->GetPrecision(); sqlt = GetSQLType(buftype); dec = IsTypeChar(buftype) ? 0 : colp->GetScale(); @@ -1845,7 +1844,7 @@ bool ODBConn::ExecSQLcommand(char *sql) for (int i = 0; i < MAX_NUM_OF_MSG && x->m_ErrMsg[i]; i++) htrc(x->m_ErrMsg[i]); - sprintf(g->Message, "Remote: %s", x->GetErrorMessage(0)); + sprintf(g->Message, "Remote %s: %s", x->m_Msg, x->GetErrorMessage(0)); if (b) SQLCancel(hstmt); @@ -1930,7 +1929,7 @@ PQRYRES ODBConn::GetMetaData(PGLOBAL g, char *dsn, char *src) } // endfor i } catch(DBX *x) { - strcpy(g->Message, x->GetErrorMessage(0)); + sprintf(g->Message, "%s: %s", x->m_Msg, x->GetErrorMessage(0)); goto err; } // end try/catch @@ -1981,7 +1980,7 @@ PQRYRES ODBConn::GetMetaData(PGLOBAL g, char *dsn, char *src) } // endfor i } catch(DBX *x) { - strcpy(g->Message, x->GetErrorMessage(0)); + sprintf(g->Message, "%s: %s", x->m_Msg, x->GetErrorMessage(0)); qrp = NULL; } // end try/catch @@ -2033,7 +2032,7 @@ bool ODBConn::GetDataSources(PQRYRES qrp) } // endfor i } catch(DBX *x) { - strcpy(m_G->Message, x->GetErrorMessage(0)); + sprintf(m_G->Message, "%s: %s", x->m_Msg, x->GetErrorMessage(0)); rv = true; } // end try/catch @@ -2084,7 +2083,7 @@ bool ODBConn::GetDrivers(PQRYRES qrp) } // endfor n } catch(DBX *x) { - strcpy(m_G->Message, x->GetErrorMessage(0)); + sprintf(m_G->Message, "%s: %s", x->m_Msg, x->GetErrorMessage(0)); rv = true; } // end try/catch @@ -2402,7 +2401,7 @@ int ODBConn::GetCatInfo(CATPARM *cap) for (int i = 0; i < MAX_NUM_OF_MSG && x->m_ErrMsg[i]; i++) htrc(x->m_ErrMsg[i]); - strcpy(g->Message, x->GetErrorMessage(0)); + sprintf(g->Message, "%s: %s", x->m_Msg, x->GetErrorMessage(0)); irc = -1; } // end try/catch @@ -2506,7 +2505,7 @@ int ODBConn::Rewind(char *sql, ODBCCOL *tocols) rbuf = (int)crow; } catch(DBX *x) { - strcpy(m_G->Message, x->GetErrorMessage(0)); + sprintf(m_G->Message, "%s: %s", x->m_Msg, x->GetErrorMessage(0)); rbuf = -1; } // end try/catch diff --git a/storage/connect/odbconn.h b/storage/connect/odbconn.h index b7c5e0c942d..41cc2439354 100644 --- a/storage/connect/odbconn.h +++ b/storage/connect/odbconn.h @@ -141,7 +141,7 @@ class ODBConn : public BLOCK { //void SetUserPwd(PSZ pwd) {m_Pwd = pwd;} int GetResultSize(char *sql, ODBCCOL *colp); int ExecDirectSQL(char *sql, ODBCCOL *tocols); - int Fetch(void); + int Fetch(int pos = 0); int PrepareSQL(char *sql); int ExecuteSQL(void); bool BindParam(ODBCCOL *colp); @@ -192,10 +192,10 @@ class ODBConn : public BLOCK { PSZ m_Pwd; int m_Catver; int m_Rows; + int m_Fetch; bool m_Updatable; bool m_Transact; bool m_Scrollable; bool m_UseCnc; - bool m_First; bool m_Full; }; // end of ODBConn class definition diff --git a/storage/connect/tabjson.cpp b/storage/connect/tabjson.cpp index f99b0d2ef1d..ccc21960bfd 100644 --- a/storage/connect/tabjson.cpp +++ b/storage/connect/tabjson.cpp @@ -117,7 +117,8 @@ TDBJSN::TDBJSN(PJDEF tdp, PTXF txfp) : TDBDOS(tdp, txfp) Jmode = tdp->Jmode; Xcol = tdp->Xcol; Fpos = -1; - Spos = N = 0; +//Spos = 0; + N = 0; Limit = tdp->Limit; NextSame = 0; SameRow = 0; @@ -134,7 +135,7 @@ TDBJSN::TDBJSN(TDBJSN *tdbp) : TDBDOS(NULL, tdbp) Jmode = tdbp->Jmode; Xcol = tdbp->Xcol; Fpos = tdbp->Fpos; - Spos = tdbp->Spos; +//Spos = tdbp->Spos; N = tdbp->N; Limit = tdbp->Limit; NextSame = tdbp->NextSame; @@ -222,7 +223,7 @@ bool TDBJSN::OpenDB(PGLOBAL g) /* Table already open replace it at its beginning. */ /*******************************************************************/ Fpos= -1; - Spos = 0; +// Spos = 0; NextSame = 0; SameRow = 0; } else { @@ -493,7 +494,7 @@ bool JSONCOL::SetArrayOptions(PGLOBAL g, char *p, int i, PSZ nm) if (!IsTypeChar(Buf_Type)) jnp->Valp = AllocateValue(g, Buf_Type, 0, GetPrecision()); else - jnp->Valp = AllocateValue(g, TYPE_DOUBLE); + jnp->Valp = AllocateValue(g, TYPE_DOUBLE, 0, 2); break; case OP_MIN: @@ -610,7 +611,7 @@ void JSONCOL::SetJsonValue(PGLOBAL g, PVAL vp, PJVAL val, int n) break; case TYPE_JOB: // if (!vp->IsTypeNum() || !Strict) { - vp->SetValue_psz(val->GetObject()->GetText(g)); + vp->SetValue_psz(val->GetObject()->GetText(g, NULL)); break; // } // endif Type @@ -1290,6 +1291,51 @@ int TDBJSON::MakeIndex(PGLOBAL g, PIXDEF pxdf, bool add) } // end of MakeIndex /***********************************************************************/ +/* Return the position in the table. */ +/***********************************************************************/ +int TDBJSON::GetRecpos(void) + { +#if 0 + union { + uint Rpos; + BYTE Spos[4]; + }; + + Rpos = htonl(Fpos); + Spos[0] = (BYTE)NextSame; + return Rpos; +#endif // 0 + return Fpos; + } // end of GetRecpos + +/***********************************************************************/ +/* Set the position in the table. */ +/***********************************************************************/ +bool TDBJSON::SetRecpos(PGLOBAL g, int recpos) + { +#if 0 + union { + uint Rpos; + BYTE Spos[4]; + }; + + Rpos = recpos; + NextSame = Spos[0]; + Spos[0] = 0; + Fpos = (signed)ntohl(Rpos); + +//if (Fpos != (signed)ntohl(Rpos)) { +// Fpos = ntohl(Rpos); +// same = false; +//} else +// same = true; +#endif // 0 + + Fpos = recpos - 1; + return false; + } // end of SetRecpos + +/***********************************************************************/ /* JSON Access Method opening routine. */ /***********************************************************************/ bool TDBJSON::OpenDB(PGLOBAL g) @@ -1299,7 +1345,6 @@ bool TDBJSON::OpenDB(PGLOBAL g) /* Table already open replace it at its beginning. */ /*******************************************************************/ Fpos= -1; - Spos = 0; NextSame = false; SameRow = 0; return false; diff --git a/storage/connect/tabjson.h b/storage/connect/tabjson.h index f46a59c5498..397c2cf5d3a 100644 --- a/storage/connect/tabjson.h +++ b/storage/connect/tabjson.h @@ -94,7 +94,7 @@ class TDBJSN : public TDBDOS { JMODE Jmode; // MODE_OBJECT by default char *Xcol; // Name of expandable column int Fpos; // The current row index - int Spos; // DELETE start index +//int Spos; // DELETE start index int N; // The current Rownum int Limit; // Limit of multiple values int Pretty; // Depends on file structure @@ -174,7 +174,9 @@ class TDBJSON : public TDBJSN { virtual int Cardinality(PGLOBAL g); virtual int GetMaxSize(PGLOBAL g); virtual void ResetSize(void); - virtual int GetRecpos(void) {return Fpos;} + virtual int GetProgCur(void) {return N;} + virtual int GetRecpos(void); + virtual bool SetRecpos(PGLOBAL g, int recpos); virtual bool OpenDB(PGLOBAL g); virtual int ReadDB(PGLOBAL g); virtual bool PrepareWriting(PGLOBAL g) {return false;} diff --git a/storage/connect/tabodbc.cpp b/storage/connect/tabodbc.cpp index bf76a124b70..3bf1238cebc 100644 --- a/storage/connect/tabodbc.cpp +++ b/storage/connect/tabodbc.cpp @@ -135,9 +135,16 @@ bool ODBCDEF::DefineAM(PGLOBAL g, LPCSTR am, int poff) //Options = ODBConn::noOdbcDialog | ODBConn::useCursorLib; Cto= GetIntCatInfo("ConnectTimeout", DEFAULT_LOGIN_TIMEOUT); Qto= GetIntCatInfo("QueryTimeout", DEFAULT_QUERY_TIMEOUT); - Scrollable = GetBoolCatInfo("Scrollable", false); + + if ((Scrollable = GetBoolCatInfo("Scrollable", false)) && !Elemt) + Elemt = 1; // Cannot merge SQLFetch and SQLExtendedFetch + UseCnc = GetBoolCatInfo("UseDSN", false); - Memory = GetBoolCatInfo("Memory", false); + + // Memory was Boolean, it is now integer + if (!(Memory = GetIntCatInfo("Memory", 0))) + Memory = GetBoolCatInfo("Memory", false) ? 1 : 0; + Pseudo = 2; // FILID is Ok but not ROWID return false; } // end of DefineAM @@ -206,7 +213,7 @@ TDBODBC::TDBODBC(PODEF tdp) : TDBASE(tdp) Quoted = MY_MAX(0, tdp->GetQuoted()); Rows = tdp->GetElemt(); Catver = tdp->Catver; - Memory = (tdp->Memory) ? 1 : 0; + Memory = tdp->Memory; Scrollable = tdp->Scrollable; Ops.UseCnc = tdp->UseCnc; } else { @@ -238,11 +245,13 @@ TDBODBC::TDBODBC(PODEF tdp) : TDBASE(tdp) DBQ = NULL; Qrp = NULL; Fpos = 0; + Curpos = 0; AftRows = 0; CurNum = 0; Rbuf = 0; BufSize = 0; Nparm = 0; + Placed = false; } // end of TDBODBC standard constructor TDBODBC::TDBODBC(PTDBODBC tdbp) : TDBASE(tdbp) @@ -267,15 +276,15 @@ TDBODBC::TDBODBC(PTDBODBC tdbp) : TDBASE(tdbp) Options = tdbp->Options; Quoted = tdbp->Quoted; Rows = tdbp->Rows; - Fpos = tdbp->Fpos; - AftRows = tdbp->AftRows; -//Tpos = tdbp->Tpos; -//Spos = tdbp->Spos; - CurNum = tdbp->CurNum; - Rbuf = tdbp->Rbuf; + Fpos = 0; + Curpos = 0; + AftRows = 0; + CurNum = 0; + Rbuf = 0; BufSize = tdbp->BufSize; Nparm = tdbp->Nparm; Qrp = tdbp->Qrp; + Placed = false; } // end of TDBODBC copy constructor // Method @@ -811,10 +820,12 @@ bool TDBODBC::OpenDB(PGLOBAL g) return true; } // endif Rewind - } // endif Memory + } else + Rbuf = Qrp->Nblin; CurNum = 0; Fpos = 0; + Curpos = 1; return false; } // endif use @@ -841,6 +852,37 @@ bool TDBODBC::OpenDB(PGLOBAL g) /* Make the command and allocate whatever is used for getting results. */ /*********************************************************************/ if (Mode == MODE_READ || Mode == MODE_READX) { + if (Memory > 1 && !Srcdef) { + char *Sql; + int n; + + if ((Sql = MakeSQL(g, true))) { + // Allocate a Count(*) column + Cnp = new(g) ODBCCOL; + Cnp->InitValue(g); + + if ((n = Ocp->GetResultSize(Sql, Cnp)) < 0) { + strcpy(g->Message, "Cannot get result size"); + return true; + } // endif n + + Ocp->m_Rows = n; + + if ((Qrp = Ocp->AllocateResult(g))) + Memory = 2; // Must be filled + else { + strcpy(g->Message, "Memory allocation failed"); + return true; + } // endif n + + Ocp->m_Rows = 0; + } else { + strcpy(g->Message, "MakeSQL failed"); + return true; + } // endif Sql + + } // endif Memory + if ((Query = MakeSQL(g, false))) { for (PODBCCOL colp = (PODBCCOL)Columns; colp; colp = (PODBCCOL)colp->GetNext()) @@ -882,10 +924,41 @@ bool TDBODBC::OpenDB(PGLOBAL g) /***********************************************************************/ int TDBODBC::GetRecpos(void) { - return Fpos; // To be really implemented + return Fpos; } // end of GetRecpos /***********************************************************************/ +/* SetRecpos: set the position of next read record. */ +/***********************************************************************/ +bool TDBODBC::SetRecpos(PGLOBAL g, int recpos) + { + if (Ocp->m_Full) { + Fpos = 0; + CurNum = recpos - 1; + } else if (Memory == 3) { + Fpos = recpos; + CurNum = -1; + } else if (Scrollable) { + // Is new position in the current row set? + if (recpos >= Curpos && recpos < Curpos + Rbuf) { + CurNum = recpos - Curpos; + Fpos = 0; + } else { + Fpos = recpos; + CurNum = 0; + } // endif recpos + + } else { + strcpy(g->Message, "This action requires a scrollable cursor"); + return true; + } // endif's + + // Indicate the table position was externally set + Placed = true; + return false; + } // end of SetRecpos + +/***********************************************************************/ /* VRDNDOS: Data Base read routine for odbc access method. */ /***********************************************************************/ int TDBODBC::ReadDB(PGLOBAL g) @@ -924,22 +997,32 @@ int TDBODBC::ReadDB(PGLOBAL g) /* Now start the reading process. */ /* Here is the place to fetch the line(s). */ /*********************************************************************/ - if (Memory != 3) { - if (++CurNum >= Rbuf) { - Rbuf = Ocp->Fetch(); - CurNum = 0; - } // endif CurNum + if (Placed) { + if (Fpos && CurNum >= 0) + Rbuf = Ocp->Fetch((Curpos = Fpos)); rc = (Rbuf > 0) ? RC_OK : (Rbuf == 0) ? RC_EF : RC_FX; - } else // Getting result from memory - rc = (Fpos < Qrp->Nblin) ? RC_OK : RC_EF; + Placed = false; + } else { + if (Memory != 3) { + if (++CurNum >= Rbuf) { + Rbuf = Ocp->Fetch(); + Curpos = Fpos + 1; + CurNum = 0; + } // endif CurNum - if (rc == RC_OK) { - if (Memory == 2) - Qrp->Nblin++; + rc = (Rbuf > 0) ? RC_OK : (Rbuf == 0) ? RC_EF : RC_FX; + } else // Getting result from memory + rc = (Fpos < Qrp->Nblin) ? RC_OK : RC_EF; - Fpos++; // Used for memory - } // endif rc + if (rc == RC_OK) { + if (Memory == 2) + Qrp->Nblin++; + + Fpos++; // Used for memory and pos + } // endif rc + + } // endif Placed if (trace > 1) htrc(" Read: Rbuf=%d rc=%d\n", Rbuf, rc); diff --git a/storage/connect/tabodbc.h b/storage/connect/tabodbc.h index eb20eb6efde..b8c9d85aae5 100644 --- a/storage/connect/tabodbc.h +++ b/storage/connect/tabodbc.h @@ -64,8 +64,8 @@ class DllExport ODBCDEF : public TABDEF { /* Logical table description */ int Quoted; /* Identifier quoting level */ int Maxerr; /* Maxerr for an Exec table */ int Maxres; /* Maxres for a catalog table */ + int Memory; /* Put result set in memory */ bool Scrollable; /* Use scrollable cursor */ - bool Memory; /* Put result set in memory */ bool Xsrc; /* Execution type */ bool UseCnc; /* Use SQLConnect (!SQLDriverConnect) */ }; // end of ODBCDEF @@ -93,6 +93,7 @@ class TDBODBC : public TDBASE { // Methods virtual PTDB CopyOne(PTABS t); virtual int GetRecpos(void); + virtual bool SetRecpos(PGLOBAL g, int recpos); virtual PSZ GetFile(PGLOBAL g); virtual void SetFile(PGLOBAL g, PSZ fn); virtual void ResetSize(void); @@ -148,6 +149,7 @@ class TDBODBC : public TDBASE { int Qto; // Query timeout int Quoted; // The identifier quoting level int Fpos; // Position of last read record + int Curpos; // Cursor position of last fetch int AftRows; // The number of affected rows int Rows; // Rowset size int Catver; // Catalog ODBC version @@ -157,6 +159,7 @@ class TDBODBC : public TDBASE { int Nparm; // The number of statement parameters int Memory; // 0: No 1: Alloc 2: Put 3: Get bool Scrollable; // Use scrollable cursor + bool Placed; // True for position reading bool UseCnc; // Use SQLConnect (!SQLDriverConnect) PQRYRES Qrp; // Points to storage result }; // end of class TDBODBC diff --git a/storage/connect/tabutil.cpp b/storage/connect/tabutil.cpp index c1c112633fd..c114497aa69 100644 --- a/storage/connect/tabutil.cpp +++ b/storage/connect/tabutil.cpp @@ -1,7 +1,7 @@ /************* Tabutil cpp Declares Source Code File (.CPP) ************/ -/* Name: TABUTIL.CPP Version 1.0 */ +/* Name: TABUTIL.CPP Version 1.1 */ /* */ -/* (C) Copyright to the author Olivier BERTRAND 2013 */ +/* (C) Copyright to the author Olivier BERTRAND 2013 - 2015 */ /* */ /* Utility function used by the PROXY, XCOL, OCCUR, and TBL tables. */ /***********************************************************************/ @@ -9,7 +9,8 @@ /***********************************************************************/ /* Include relevant section of system dependant header files. */ /***********************************************************************/ -#include "my_global.h" +#define MYSQL_SERVER 1 +#include <my_global.h> #include "sql_class.h" #include "table.h" #include "field.h" @@ -108,6 +109,9 @@ TABLE_SHARE *GetTableShare(PGLOBAL g, THD *thd, const char *db, } // endif is_view } else { + if (thd->is_error()) + thd->clear_error(); // Avoid stopping info commands + sprintf(g->Message, "Error %d opening share\n", s->error); free_table_share(s); return NULL; |