1 /*
2 This file is part of libodbc++.
3
4 Copyright (C) 1999-2000 Manush Dodunekov <manush@stendahls.net>
5
6 This library is free software; you can redistribute it and/or
7 modify it under the terms of the GNU Library General Public
8 License as published by the Free Software Foundation; either
9 version 2 of the License, or (at your option) any later version.
10
11 This library is distributed in the hope that it will be useful,
12 but WITHOUT ANY WARRANTY; without even the implied warranty of
13 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
14 Library General Public License for more details.
15
16 You should have received a copy of the GNU Library General Public License
17 along with this library; see the file COPYING. If not, write to
18 the Free Software Foundation, Inc., 59 Temple Place - Suite 330,
19 Boston, MA 02111-1307, USA.
20 */
21
22 #include "isql++.h"
23
24 #include <cstdlib>
25 #include <cstdio>
26 #include <iostream>
27
28 extern "C" {
29 #if defined(ODBCXX_DISABLE_READLINE_HACK)
30 #include <readline/readline.h>
31 #include <readline/history.h>
32 #else
33
34 /* readline.h doesn't contain proper function prototypes, which makes
35 newer gcc versions (>=2.95) barf with certain flags. This could
36 help the situation.
37 */
38 extern char* rl_readline_name;
39
40 typedef char** (*CPPFunction)(char*,char*);
41
42 extern CPPFunction rl_completion_entry_function;
43 extern int rl_initialize(void);
44 extern char* readline(const char*);
45 extern void add_history(const char*);
46
47 #ifdef SPACE
48 #undef SPACE
49 #endif
50 #define SPACE ' '
51
52 #endif
53 }
54
55 using namespace odbc;
56 using namespace std;
57
58 const char* SQLPROMPT1="SQL> ";
59 const char* SQLPROMPT2=" +> ";
60 const char* BLOB_FIELD="<BLOB>";
61 const char* NULL_FIELD="<NULL>";
62 const char* INNER_SEPARATOR=" ";
63 const char* OUTER_SEPARATOR=" ";
64 const char SPACE_CHAR=' ';
65 const char LINE_CHAR='=';
66 const char END_OF_STATEMENT=';';
67 const char* WS=" \r\n\t";
68
69 const int LONGVARCHAR_WIDTH=20;
70 const int MIN_COL_WIDTH_ON_SCREEN=4;
71
72 // this effectively disables filename completion
73 // with readline
noCompletion(char *,char *)74 static char **noCompletion (char *,char*)
75 {
76 // no completion of filenames
77 return (char**)NULL;
78 }
79
getTypeName(int sqlType)80 static const char* getTypeName(int sqlType) {
81 static struct {
82 int id;
83 const char* name;
84 } sqlTypes[] = {
85 { Types::BIGINT, "BIGINT" },
86 { Types::BINARY, "BINARY" },
87 { Types::BIT, "BIT" },
88 { Types::CHAR, "CHAR" },
89 { Types::DATE, "DATE" },
90 { Types::DECIMAL, "DECIMAL" },
91 { Types::DOUBLE, "DOUBLE" },
92 { Types::FLOAT, "FLOAT" },
93 { Types::INTEGER, "INTEGER" },
94 { Types::LONGVARBINARY, "LONGVARBINARY" },
95 { Types::LONGVARCHAR, "LONGVARCHAR" },
96 { Types::NUMERIC, "NUMERIC" },
97 { Types::REAL, "REAL" },
98 { Types::SMALLINT, "SMALLINT" },
99 { Types::TIME, "TIME" },
100 { Types::TIMESTAMP, "TIMESTAMP" },
101 { Types::TINYINT, "TINYINT" },
102 { Types::VARBINARY, "VARBINARY" },
103 { Types::VARCHAR, "VARCHAR" },
104 {0, NULL }
105 };
106
107 for(unsigned int i=0; sqlTypes[i].name!=NULL; i++) {
108 if(sqlTypes[i].id==sqlType) {
109 return sqlTypes[i].name;
110 }
111 }
112
113 return "UNKNOWN";
114 }
115
116 // split string on any of schars
117 inline vector<string>
splitString(const string & str,const char * schars)118 splitString(const string& str, const char* schars)
119 {
120 vector<string> res;
121
122 if(str.length()==0) {
123 return res;
124 }
125
126 if(strlen(schars)==0) {
127 res.push_back(str);
128 return res;
129 }
130
131 string::size_type e=0;
132
133 string::size_type s=str.find_first_not_of(schars);
134
135 while(s!=string::npos) {
136 e=str.find_first_of(schars,s);
137 if(e==string::npos) {
138 res.push_back(str.substr(s));
139 } else {
140 res.push_back(str.substr(s,e-s));
141 }
142 s=str.find_first_not_of(schars,e);
143 }
144
145 for(vector<string>::iterator i=res.begin();
146 i!=res.end(); i++) {
147 }
148
149 return res;
150 }
151
152 // lowercase a string
toLowerCase(const string & str)153 inline string toLowerCase(const string& str) {
154 string ret;
155
156 if(str.length()>0) {
157 ret.resize(str.length());
158 for(int i=0; i<str.length(); i++) {
159 ret[i]=tolower(str[i]);
160 }
161 }
162 return ret;
163 }
164
toUpperCase(const string & str)165 inline string toUpperCase(const string& str) {
166 string ret;
167
168 if(str.length()>0) {
169 ret.resize(str.length());
170 for(int i=0; i<str.length(); i++) {
171 ret[i]=toupper(str[i]);
172 }
173 }
174 return ret;
175 }
176
177
178 // split a fully qualified sql table or procedure
179 // identifier into catalog,schema and name
180 // returns a vector of all three of them
splitIdentifier(const string & id,string & catalog,string & schema,string & name)181 void Isql::splitIdentifier(const string& id,
182 string& catalog,
183 string& schema,
184 string& name)
185 {
186 vector<string> ret;
187
188 DatabaseMetaData* md=con_->getMetaData();
189
190 string str=id; //local copy, we are going to modify it
191 if(supportsCatalogs_) {
192 string catSep=md->getCatalogSeparator();
193 string::size_type catStart, catEnd;
194 if(md->isCatalogAtStart()) {
195 catStart=0;
196 catEnd=str.find(catSep);
197 if(catEnd!=string::npos) {
198 catalog=str.substr(0,catEnd);
199 str=str.substr(catEnd+catSep.length());
200 }
201 } else {
202 catStart=str.rfind(catSep);
203 if(catStart!=string::npos) {
204 catalog=str.substr(catStart+1);
205 str=str.substr(0,catStart);
206 }
207 }
208 } else {
209 catalog="";
210 }
211
212 // check for schemas
213 if(supportsSchemas_) {
214 string schemaSep=".";
215
216 string::size_type schemaEnd=str.find(schemaSep);
217 if(schemaEnd!=string::npos) {
218 schema=str.substr(0,schemaEnd);
219 str=str.substr(schemaEnd+schemaSep.length());
220 }
221 } else {
222 schema="";
223 }
224
225 name=str;
226
227 //now, check if we are to perform some case-transforms on this
228 //since some drivers can't refer to table test using TEST
229 //when metadata information is requested
230
231 if(md->storesLowerCaseIdentifiers()) {
232 name=toLowerCase(name);
233 schema=toLowerCase(schema);
234 catalog=toLowerCase(catalog);
235 } else if(md->storesUpperCaseIdentifiers()) {
236 name=toUpperCase(name);
237 schema=toUpperCase(schema);
238 catalog=toUpperCase(catalog);
239 }
240
241 //otherwise, we don't touch them
242 }
243
makeIdentifier(const string & cat,const string & schema,const string & name)244 string Isql::makeIdentifier(const string& cat,
245 const string& schema,
246 const string& name)
247 {
248 string id=name;
249 DatabaseMetaData* md=con_->getMetaData();
250
251 if(supportsSchemas_ && schema.length()>0) {
252 string schemaSep=".";
253 id=schema+schemaSep+id;
254 }
255
256 if(supportsCatalogs_ && cat.length()>0) {
257 string catSep=md->getCatalogSeparator();
258 if(md->isCatalogAtStart()) {
259 id=cat+catSep+id;
260 } else {
261 id+=(catSep+cat);
262 }
263 }
264 return id;
265 }
266
267
268
269
Isql(Connection * con)270 Isql::Isql(Connection* con)
271 :con_(con),termWidth_(80),
272 maxRows_(0)
273 {
274 rl_readline_name="isqlxx";
275 rl_completion_entry_function=(CPPFunction)noCompletion;
276 rl_initialize();
277
278 commands_["set"]=&Isql::setCmd;
279 commands_["show"]=&Isql::showCmd;
280 commands_["commit"]=&Isql::commitCmd;
281 commands_["rollback"]=&Isql::rollbackCmd;
282 commands_["describe"]=&Isql::describeCmd;
283
284 // a cheap way to check if this supports schemas
285 try {
286 ResultSet* rs=con_->getMetaData()->getSchemas();
287 Deleter<ResultSet> _rs(rs);
288 supportsSchemas_=rs->next();
289 } catch(SQLException& e) {
290 supportsSchemas_=true;
291 }
292
293 // another cheap trick
294 try {
295 ResultSet* rs=con_->getMetaData()->getCatalogs();
296 Deleter<ResultSet> _rs(rs);
297 supportsCatalogs_=rs->next();
298 } catch(SQLException& e) {
299 supportsCatalogs_=true;
300 }
301
302 }
303
~Isql()304 Isql::~Isql()
305 {
306 //safety
307 if(con_->getMetaData()->supportsTransactions()) {
308 con_->rollback();
309 }
310 }
311
readInput(string & out)312 bool Isql::readInput(string& out)
313 {
314 const char* prompt=(buffer_.length()>0?SQLPROMPT2:SQLPROMPT1);
315 char* s=readline(prompt);
316
317 if(s!=NULL) {
318
319 if(s[0]!=0) {
320 add_history(s);
321 out=s;
322 } else {
323 out = "";
324 }
325 //free it (it is malloced)
326 free(s);
327
328 return true;
329
330 } else {
331 return false;
332 }
333 }
334
335
336
run()337 void Isql::run()
338 {
339 string input;
340
341 while(this->readInput(input)) {
342 buffer_=buffer_+(buffer_.length()>0?"\n"+input:input);
343 string s;
344 StatementType st=this->extractStatement(s);
345
346 switch(st) {
347 case STATEMENT_SQL:
348 try {
349 if(s.length()>1 &&
350 s[0]=='"' && s[s.length()-1]=='"') {
351 //the whole thing is quoted, strip the quotes
352 s=s.substr(1,s.length()-2);
353 }
354 PreparedStatement* pstmt=con_->prepareStatement(s);
355 Deleter<PreparedStatement> _pstmt(pstmt);
356
357 //apply maxrows if needed
358 if(maxRows_>0) {
359 pstmt->setMaxRows(maxRows_);
360 }
361
362 this->execute(pstmt);
363 } catch(SQLException& e) {
364 cout << e.getMessage() << endl;
365 }
366 break;
367
368 case STATEMENT_COMMAND:
369 try {
370 this->executeCommand(s);
371 } catch(exception& e) {
372 cout << e.what() << endl;
373 }
374 break;
375 case STATEMENT_NONE:
376 break;
377 }
378 }
379 }
380
execute(PreparedStatement * pstmt)381 void Isql::execute(PreparedStatement* pstmt)
382 {
383 if(pstmt->execute()) {
384 ResultSet* rs=pstmt->getResultSet();
385 Deleter<ResultSet> _rs(rs);
386 this->printResultSet(rs);
387 }
388
389 try {
390 int ar=pstmt->getUpdateCount();
391 cout << OUTER_SEPARATOR << ar << " rows affected" << flush;
392 } catch(SQLException& e) {} //ignore it
393 cout << endl;
394 }
395
printResultSet(ResultSet * rs)396 void Isql::printResultSet(ResultSet* rs)
397 {
398 int cnt=0;
399
400 ResultSetMetaData* md=rs->getMetaData();
401
402 int totalWidth=0, shrinkableWidth=0;
403 vector<int> widths;
404 for(int i=1; i<=md->getColumnCount(); i++) {
405 int l;
406 switch(md->getColumnType(i)) {
407 case Types::LONGVARCHAR:
408 l=LONGVARCHAR_WIDTH;
409 break;
410 case Types::LONGVARBINARY:
411 l=strlen(BLOB_FIELD);
412 break;
413 default:
414 l=md->getColumnDisplaySize(i);
415 break;
416 }
417 widths.push_back(l);
418 totalWidth+=l;
419 if ( i > 1 ) totalWidth += strlen(INNER_SEPARATOR);
420 }
421 totalWidth += 2 * strlen(OUTER_SEPARATOR);
422
423 if(totalWidth>(int)termWidth_) {
424 //we'll need to shrink some column widths
425 //we only do that on char and varchar columns
426
427 for(int i=1; i<=md->getColumnCount(); i++) {
428 switch(md->getColumnType(i)) {
429 case Types::CHAR:
430 case Types::VARCHAR:
431 shrinkableWidth+=md->getColumnDisplaySize(i);
432 break;
433 default:
434 break;
435 }
436 }
437
438 if(shrinkableWidth>0) {
439 //ok, now try shrinking
440 float shrinkFactor = (1.0 - ((float)(totalWidth - (int)termWidth_)) / (float)shrinkableWidth);
441
442 // if shrinkFactor is negative will use a minimal width
443 int w;
444 for(int i=0; i < (int)widths.size(); i++) {
445 switch( md->getColumnType(i+1) ) {
446 case Types::CHAR:
447 case Types::VARCHAR:
448 // cout << "Column " << md->getColumnName(i+1)
449 // << "; shrinked from " << widths[i] << flush;
450 w = (int)( ((float)widths[i]) * shrinkFactor);
451 widths[i] = ( w > MIN_COL_WIDTH_ON_SCREEN ) ? w : MIN_COL_WIDTH_ON_SCREEN;
452 // cout << " to " << widths[i] << endl;
453 break;
454 default:
455 break;
456 }
457 }
458 }
459 }
460
461 //now we have all widths, print column names
462 cout << OUTER_SEPARATOR << flush;
463 for(int i=1; i<=md->getColumnCount(); i++) {
464 int w=widths[i-1];
465 string colName=md->getColumnName(i);
466 //fix the width
467 colName.resize(w,SPACE);
468 if(i>1) {
469 cout << INNER_SEPARATOR << flush;
470 }
471 cout << colName << flush;
472 }
473 cout << OUTER_SEPARATOR << endl;
474
475 //print a line
476 cout << OUTER_SEPARATOR << flush;
477 for(int i=1; i<=md->getColumnCount(); i++) {
478 int w=widths[i-1];
479 string s;
480 s.resize(w,LINE_CHAR);
481 if(i>1) {
482 cout << INNER_SEPARATOR << flush;
483 }
484 cout << s << flush;
485 }
486 cout << OUTER_SEPARATOR << endl;
487
488
489 //and finally, actually print the results
490 while(rs->next()) {
491 cnt++;
492 cout << OUTER_SEPARATOR << flush;
493 for(int i=1; i<=md->getColumnCount(); i++) {
494 int w=widths[i-1];
495 string val=rs->getString(i);
496 if(rs->wasNull()) {
497 val=NULL_FIELD;
498 }
499 val.resize(w,SPACE);
500 if(i>1) {
501 cout << INNER_SEPARATOR << flush;
502 }
503 cout << val << flush;
504 }
505 cout << OUTER_SEPARATOR << endl;
506 }
507 cout << endl
508 << OUTER_SEPARATOR << cnt << " rows fetched." << endl;
509 }
510
511
512
513 Isql::StatementType
extractStatement(string & out)514 Isql::extractStatement(string& out)
515 {
516 // clear out
517 out="";
518
519 if(buffer_.length()==0) {
520 return STATEMENT_NONE;
521 }
522
523 //first, find the first non-whitespace char
524
525 string::size_type first=
526 buffer_.find_first_not_of(WS);
527
528 if(first==string::npos) {
529 return STATEMENT_NONE;
530 }
531
532 StatementType st=STATEMENT_NONE;
533
534
535 string::size_type firstEnd=
536 buffer_.find_first_of(WS,first);
537
538 if(firstEnd!=string::npos ||
539 buffer_.length()-1>first) {
540
541 // we need to check whether we brought an ; with us
542
543 string::size_type eosp=buffer_.find_first_of
544 (END_OF_STATEMENT,first);
545
546 if(eosp!=string::npos &&
547 (firstEnd==string::npos || firstEnd>eosp)) {
548 firstEnd=eosp;
549 }
550
551 string cmd=buffer_.substr(first,firstEnd-first);
552
553 CmdMap::iterator i;
554
555 if((i=commands_.find(cmd))!=commands_.end()) {
556 st = STATEMENT_COMMAND;
557 }
558 }
559
560 if(st==STATEMENT_NONE) {
561 // our only choice now
562 st=STATEMENT_SQL;
563 }
564
565 //ok, we know what we have in there.
566 //now, let's search for a terminator
567
568 const char* str=buffer_.c_str();
569
570 int inQ=0; //this is either '\'', '"' or 0 while iterating
571 int i=0;
572
573 while(str[i]!=0) {
574 char c=str[i];
575 switch(c) {
576 case '\'':
577 if(inQ==0) {
578 inQ='\'';
579 } else if(inQ=='\'') {
580 inQ=0;
581 }
582 break;
583
584 case '"':
585 if(inQ==0) {
586 inQ='"';
587 } else if(inQ=='"') {
588 inQ=0;
589 }
590 break;
591
592 case END_OF_STATEMENT:
593 if(inQ==0) {
594 out=buffer_.substr(0,(string::size_type)i);
595 if(str[i+1]!=0) {
596 buffer_=buffer_.substr((string::size_type)i+1);
597 } else {
598 buffer_="";
599 }
600 return st;
601 }
602 break;
603
604 default:
605 break;
606 };
607 i++;
608 }
609
610 return STATEMENT_NONE;
611 }
612
613
executeCommand(const string & cmd)614 void Isql::executeCommand(const string& cmd)
615 {
616 // we need to split up cmd into tokens
617
618 vector<string> tokens=splitString(cmd,WS);
619 if(tokens.size()==0) {
620 cout << "Whoops: 0 tokens for executeCommand() " << endl;
621 return;
622 }
623
624 vector<string>::iterator i=tokens.begin();
625 string name=*i;
626 vector<string> args;
627 while((++i)!=tokens.end()) {
628 args.push_back(*i);
629 }
630
631 CmdMap::iterator cmdi=commands_.find(name);
632 if(cmdi!=commands_.end()) {
633 Command cmd=(*cmdi).second;
634 (this->*cmd)(args);
635 }
636 }
637
638
639
640
641
setCmd(const vector<string> & args)642 void Isql::setCmd(const vector<string>& args)
643 {
644 if(args.size()!=2) {
645 throw CommandException
646 ("invalid number of arguments to 'set'");
647 }
648
649 string name=toLowerCase(args[0]);
650 string value=args[1];
651
652 if(name=="maxrows") {
653 int i=stringToInt(value);
654 if(i<0) {
655 throw CommandException
656 ("maxrows must be >= 0");
657 }
658 maxRows_=i;
659 } else if(name=="catalog") {
660 con_->setCatalog(value);
661 } else if(name=="autocommit") {
662 string v=toLowerCase(value);
663 bool ac=(v=="on"?true:false);
664 con_->setAutoCommit(ac);
665 } else if(name=="trace") {
666 string v=toLowerCase(value);
667 bool t=(v=="on"?true:false);
668 con_->setTrace(t);
669 } else if(name=="tracefile") {
670 con_->setTraceFile(value);
671 } else {
672 throw CommandException
673 ("set: unknown variable '"+name+"'");
674 }
675 }
676
showCmd(const vector<string> & args)677 void Isql::showCmd(const vector<string>& args)
678 {
679 if(args.size()!=1) {
680 throw CommandException
681 ("invalid number of arguments to 'show'");
682 }
683
684 string name=toLowerCase(args[0]);
685
686 string value;
687
688 if(name=="maxrows") {
689 value=intToString(maxRows_);
690 } else if(name=="catalog") {
691 value=con_->getCatalog();
692 } else if(name=="autocommit") {
693 value=(con_->getAutoCommit()?"on":"off");
694 } else if(name=="trace") {
695 value=(con_->getTrace()?"on":"off");
696 } else if(name=="tracefile") {
697 value=(con_->getTraceFile());
698 } else if(name=="types") {
699 this->showTypesCmd();
700 return;
701 } else {
702 throw CommandException
703 ("show: unknown variable '"+name+"'");
704 }
705 cout << name << " is " << value << endl;
706 }
707
708
showTypesCmd()709 void Isql::showTypesCmd()
710 {
711 DatabaseMetaData* md=con_->getMetaData();
712 try {
713 ResultSet* rs=md->getTypeInfo();
714 Deleter<ResultSet> _rs(rs);
715
716 if(rs->next()) {
717 int nameLen=25;
718 int odbcTypeLen=13;
719 int maxLenLen=15;
720 int paramsLen=20;
721 int nullableLen=4;
722 int searchableLen=12;
723
724 string nameTitle="Name";
725 string odbcTypeTitle="ODBC Type";
726 string maxLenTitle="Max size";
727 string paramsTitle="Create with";
728 string nullableTitle="Null";
729 string searchableTitle="Searchable";
730
731 nameTitle.resize(nameLen,SPACE_CHAR);
732 odbcTypeTitle.resize(odbcTypeLen,SPACE_CHAR);
733 maxLenTitle.resize(maxLenLen,SPACE_CHAR);
734 paramsTitle.resize(paramsLen,SPACE_CHAR);
735 nullableTitle.resize(nullableLen,SPACE_CHAR);
736 searchableTitle.resize(searchableLen,SPACE_CHAR);
737
738 cout << OUTER_SEPARATOR << nameTitle
739 << INNER_SEPARATOR << odbcTypeTitle
740 << INNER_SEPARATOR << maxLenTitle
741 << INNER_SEPARATOR << paramsTitle
742 << INNER_SEPARATOR << nullableTitle
743 << INNER_SEPARATOR << searchableTitle
744 << OUTER_SEPARATOR << endl;
745
746 string t;
747 t.resize(nameLen,LINE_CHAR);
748 cout << OUTER_SEPARATOR << t;
749 t.resize(odbcTypeLen,LINE_CHAR);
750 cout << INNER_SEPARATOR << t;
751 t.resize(maxLenLen,LINE_CHAR);
752 cout << INNER_SEPARATOR << t;
753 t.resize(paramsLen,LINE_CHAR);
754 cout << INNER_SEPARATOR << t;
755 t.resize(nullableLen,LINE_CHAR);
756 cout << INNER_SEPARATOR << t;
757 t.resize(searchableLen,LINE_CHAR);
758 cout << INNER_SEPARATOR << t
759 << OUTER_SEPARATOR << endl;
760 do {
761 string name=rs->getString(1);
762 string odbcType=getTypeName(rs->getShort(2));
763 string params=rs->getString(6);
764
765 if(rs->wasNull()) {
766 params="";
767 }
768
769 int ml=rs->getInt(3);
770 string maxLen;
771 if(!rs->wasNull()) {
772 maxLen=intToString(ml);
773 }
774
775 string nullable;
776 switch(rs->getShort(7)) {
777 case DatabaseMetaData::typeNoNulls:
778 nullable="No"; break;
779 case DatabaseMetaData::typeNullable:
780 nullable="Yes"; break;
781 case DatabaseMetaData::typeNullableUnknown:
782 default:
783 nullable="?"; break;
784 }
785
786 string searchable;
787 switch(rs->getShort(9)) {
788 case DatabaseMetaData::typePredChar:
789 searchable="LIKE only"; break;
790 case DatabaseMetaData::typePredBasic:
791 searchable="except LIKE"; break;
792 case DatabaseMetaData::typeSearchable:
793 searchable="Yes"; break;
794 case DatabaseMetaData::typePredNone:
795 default:
796 searchable="No"; break;
797 }
798
799 name.resize(nameLen,SPACE_CHAR);
800 odbcType.resize(odbcTypeLen,SPACE_CHAR);
801 maxLen.resize(maxLenLen,SPACE_CHAR);
802 params.resize(paramsLen,SPACE_CHAR);
803 nullable.resize(nullableLen,SPACE_CHAR);
804 searchable.resize(searchableLen,SPACE_CHAR);
805
806 cout << OUTER_SEPARATOR << name
807 << INNER_SEPARATOR << odbcType
808 << INNER_SEPARATOR << maxLen
809 << INNER_SEPARATOR << params
810 << INNER_SEPARATOR << nullable
811 << INNER_SEPARATOR << searchable
812 << OUTER_SEPARATOR << endl;
813
814 } while(rs->next());
815 }
816
817 } catch(SQLException& e) {
818 cout << e.getMessage() << endl;
819 }
820 }
821
822
commitCmd(const vector<string> & args)823 void Isql::commitCmd(const vector<string>& args)
824 {
825 //we don't care about arguments, there are like 20 different
826 //commit syntaxes
827 try {
828 con_->commit();
829 cout << "Transaction committed." << endl;
830 } catch(SQLException& e) {
831 cout << e.getMessage() << endl;
832 }
833 }
834
rollbackCmd(const vector<string> & args)835 void Isql::rollbackCmd(const vector<string>& args)
836 {
837 //same here
838 try {
839 con_->rollback();
840 cout << "Transaction rolled back." << endl;
841 } catch(SQLException& e) {
842 cout << e.getMessage() << endl;
843 }
844 }
845
846
describeCmd(const vector<string> & args)847 void Isql::describeCmd(const vector<string>& args)
848 {
849 if(args.size()!=1) {
850 throw CommandException
851 ("Invalid number of arguments to 'describe'");
852 }
853
854 string arg=args[0];
855
856 DatabaseMetaData* md=con_->getMetaData();
857
858 string catalog;
859 string schema;
860 string name;
861
862 this->splitIdentifier(arg,catalog,schema,name);
863
864 vector<string> tableTypes;
865
866 // fetch a list of all table types
867 {
868 ResultSet* rs=md->getTableTypes();
869 Deleter<ResultSet> _rs(rs);
870 while(rs->next()) {
871 tableTypes.push_back(rs->getString("TABLE_TYPE"));
872 }
873 }
874
875 // first, we check for a table or view
876 bool wasTable;
877 {
878
879 ResultSet* tableRs=md->getTables(catalog,schema,name,tableTypes);
880 Deleter<ResultSet> _tableRs(tableRs);
881
882 if((wasTable=tableRs->next())) {
883 do {
884 cout << this->makeIdentifier
885 (tableRs->getString(1),tableRs->getString(2),tableRs->getString(3));
886
887 cout << " (" << tableRs->getString(4) << ")" << endl;
888
889 string desc=tableRs->getString(5);
890 bool descIsNull=tableRs->wasNull();
891
892 int nameLength=30;
893 int nullabilityLength=10;
894 int typeLength=25;
895
896 string nameTitle="Column name";
897 string nullabilityTitle="Nullable";
898 string typeTitle="Data type";
899
900 nameTitle.resize(nameLength,SPACE_CHAR);
901 typeTitle.resize(typeLength,SPACE_CHAR);
902 nullabilityTitle.resize(nullabilityLength,SPACE_CHAR);
903
904 cout << OUTER_SEPARATOR << nameTitle
905 << INNER_SEPARATOR << typeTitle
906 << INNER_SEPARATOR << nullabilityTitle
907 << OUTER_SEPARATOR << endl;
908
909 string t;
910 cout << OUTER_SEPARATOR;
911 t.resize(nameLength,LINE_CHAR);
912 cout << t << INNER_SEPARATOR;
913 t.resize(typeLength,LINE_CHAR);
914 cout << t << INNER_SEPARATOR;
915 t.resize(nullabilityLength,LINE_CHAR);
916 cout << t << OUTER_SEPARATOR << endl;
917
918 //now, fetch the columns
919 {
920 ResultSet* rs=md->getColumns
921 (catalog,schema,tableRs->getString(3),"%");
922 Deleter<ResultSet> _rs(rs);
923
924 while(rs->next()) {
925 cout << OUTER_SEPARATOR;
926
927 string colName=rs->getString(4);
928 colName.resize(nameLength,SPACE_CHAR);
929
930 string typeName=rs->getString(6);
931 typeName.resize(typeLength,SPACE_CHAR);
932
933 string nullInfo;
934 switch(rs->getInt(11)) {
935 case DatabaseMetaData::columnNoNulls:
936 nullInfo="No";
937 break;
938 case DatabaseMetaData::columnNullable:
939 nullInfo="Yes";
940 break;
941 default:
942 nullInfo="Unknown";
943 break;
944 }
945
946 nullInfo.resize(nullabilityLength);
947
948 cout << colName << INNER_SEPARATOR
949 << typeName << INNER_SEPARATOR
950 << nullInfo << OUTER_SEPARATOR
951 << endl;
952 }
953 }
954
955 cout << endl;
956
957 try {
958 ResultSet* rs=md->getPrimaryKeys(tableRs->getString(1),
959 tableRs->getString(2),
960 tableRs->getString(3));
961 Deleter<ResultSet> _rs(rs);
962 string colNames;
963 string keyName;
964 bool keyNameIsNull=true;
965 if(rs->next()) {
966 try {
967 keyName=rs->getString(6);
968 keyNameIsNull=rs->wasNull();
969 } catch(SQLException&) {} //temporary workaround for OL driver
970
971 do {
972 if(colNames.length()>0) {
973 colNames+=",";
974 }
975 colNames+=rs->getString(4);
976 } while(rs->next());
977 }
978
979 if(colNames.length()>0) {
980 cout << OUTER_SEPARATOR << "PRIMARY KEY "
981 << "(" << colNames << ")" << endl;
982
983 }
984 } catch(SQLException& e) {
985 }
986
987 //fetch the foreign keys
988 try {
989 ResultSet* rs=md->getImportedKeys(tableRs->getString(1),
990 tableRs->getString(2),
991 tableRs->getString(3));
992
993 Deleter<ResultSet> _rs(rs);
994
995 bool goon=rs->next();
996 // here, we trust that the result set is ordered
997 // by key_seq
998 while(goon) {
999 string pkTable=this->makeIdentifier
1000 (rs->getString(1),rs->getString(2),rs->getString(3));
1001
1002 string pkCols;
1003 string fkCols;
1004 short ks=rs->getShort(9);
1005
1006 pkCols+=rs->getString(4);
1007 fkCols+=rs->getString(8);
1008
1009 while(goon=rs->next()) {
1010 if(rs->getShort(9)==1) {
1011 //this is part of another key
1012 break;
1013 }
1014
1015 pkCols+=",";
1016 fkCols+=",";
1017
1018 pkCols+=rs->getString(4);
1019 fkCols+=rs->getString(8);
1020 }
1021
1022 cout << OUTER_SEPARATOR << "FOREIGN KEY ("
1023 << fkCols << ") REFERENCES "
1024 << pkTable << " (" << pkCols << ")"
1025 << OUTER_SEPARATOR << endl;
1026 }
1027 } catch(SQLException& e) {
1028 // ignore, probably not supported
1029 }
1030
1031 try {
1032 ResultSet* rs=md->getIndexInfo(tableRs->getString(1),
1033 tableRs->getString(2),
1034 tableRs->getString(3),
1035 false,false);
1036 Deleter<ResultSet> _rs(rs);
1037 bool goon=rs->next();
1038
1039 while(goon) {
1040 //ignore statistics
1041 if(rs->getShort(7)==DatabaseMetaData::tableIndexStatistic) {
1042 goon=rs->next();
1043 break;
1044 }
1045
1046 string idxName=rs->getString(6);
1047 string cols=rs->getString(9);
1048 string order=(rs->getString(10)=="A"?"ASC":"DESC");
1049 string unique=(rs->getBoolean(4)?"":"UNIQUE ");
1050
1051 while(goon=rs->next()) {
1052 if(rs->getShort(8)==1) {
1053 // part of next index
1054 break;
1055 }
1056 cols+=","+rs->getString(9);
1057 }
1058
1059 cout << OUTER_SEPARATOR << unique << order
1060 << " INDEX " << idxName << " ON (" << cols << ")" << endl;
1061 }
1062
1063
1064 } catch(SQLException&) {}
1065
1066 try {
1067 ResultSet* rs=md->getBestRowIdentifier(tableRs->getString(1),
1068 tableRs->getString(2),
1069 tableRs->getString(3),
1070 DatabaseMetaData::bestRowTemporary,
1071 true);
1072 Deleter<ResultSet> _rs(rs);
1073
1074 if(rs->next()) {
1075
1076 cout << endl;
1077
1078 int nameLength=30;
1079 int pseudoLength=10;
1080 int scopeLength=30;
1081
1082 string nameTitle="Column name";
1083 string pseudoTitle="Pseudo";
1084 string scopeTitle="Scope";
1085
1086 cout << OUTER_SEPARATOR << "Best row identifiers" << endl;
1087
1088 nameTitle.resize(nameLength,SPACE_CHAR);
1089 pseudoTitle.resize(pseudoLength,SPACE_CHAR);
1090 scopeTitle.resize(scopeLength,SPACE_CHAR);
1091
1092 cout << OUTER_SEPARATOR << nameTitle
1093 << INNER_SEPARATOR << pseudoTitle
1094 << INNER_SEPARATOR << scopeTitle
1095 << OUTER_SEPARATOR << endl;
1096
1097 string t;
1098 cout << OUTER_SEPARATOR;
1099 t.resize(nameLength,LINE_CHAR);
1100 cout << t << INNER_SEPARATOR;
1101 t.resize(pseudoLength,LINE_CHAR);
1102 cout << t << INNER_SEPARATOR;
1103 t.resize(scopeLength,LINE_CHAR);
1104 cout << t << OUTER_SEPARATOR << endl;
1105
1106 do {
1107 string colName=rs->getString(2);
1108 string pseudo;
1109 string scope;
1110
1111 switch(rs->getInt(8)) {
1112 case DatabaseMetaData::bestRowPseudo:
1113 pseudo="Yes";
1114 break;
1115 case DatabaseMetaData::bestRowNotPseudo:
1116 pseudo="No";
1117 break;
1118 case DatabaseMetaData::bestRowUnknown:
1119 pseudo="Unknown";
1120 break;
1121 default:
1122 pseudo="?";
1123 break;
1124 }
1125
1126 switch(rs->getInt(1)) {
1127 case DatabaseMetaData::bestRowTemporary:
1128 scope="While positioned on row";
1129 break;
1130 case DatabaseMetaData::bestRowTransaction:
1131 scope="Current transaction";
1132 break;
1133 case DatabaseMetaData::bestRowSession:
1134 scope="Session (across transactions)";
1135 break;
1136
1137 default:
1138 scope="?";
1139 break;
1140 }
1141
1142 colName.resize(nameLength,SPACE_CHAR);
1143 pseudo.resize(pseudoLength,SPACE_CHAR);
1144 scope.resize(scopeLength,SPACE_CHAR);
1145
1146 cout << OUTER_SEPARATOR << colName
1147 << INNER_SEPARATOR << pseudo
1148 << INNER_SEPARATOR << scope
1149 << OUTER_SEPARATOR << endl;
1150
1151 } while(rs->next());
1152 }
1153 } catch(SQLException& e) {
1154 // ignore
1155 }
1156
1157 cout << endl;
1158
1159 if(!descIsNull && desc.length()>0) {
1160 cout << OUTER_SEPARATOR << "Description" << endl;
1161 cout << OUTER_SEPARATOR << "===========" << endl;
1162 cout << OUTER_SEPARATOR << desc << endl;
1163 cout << endl;
1164 }
1165 } while(tableRs->next());
1166 }
1167 }
1168
1169 bool wasProcedure=false;
1170
1171 if(!wasTable) {
1172 // this might be a procedure
1173 try {
1174 ResultSet* procRs=md->getProcedures(catalog,schema,name);
1175 Deleter<ResultSet> _procRs(procRs);
1176
1177 if(wasProcedure=procRs->next()) {
1178 do {
1179 cout << this->makeIdentifier
1180 (procRs->getString(1),procRs->getString(2),procRs->getString(3))
1181 << " (PROCEDURE)" << endl;
1182
1183 ResultSet* colRs=md->getProcedureColumns(procRs->getString(1),
1184 procRs->getString(2),
1185 procRs->getString(3),
1186 "%");
1187 Deleter<ResultSet> _colRs(colRs);
1188
1189 if(colRs->next()) {
1190 int nameLen=30;
1191 int dataTypeLen=15;
1192 int nullableLen=8;
1193 int typeLen=10;
1194
1195 string nameTitle="Column";
1196 string dataTypeTitle="Data type";
1197 string nullableTitle="Nullable";
1198 string typeTitle="Type";
1199
1200 nameTitle.resize(nameLen,SPACE_CHAR);
1201 dataTypeTitle.resize(dataTypeLen,SPACE_CHAR);
1202 nullableTitle.resize(nullableLen);
1203 typeTitle.resize(typeLen);
1204
1205 cout << OUTER_SEPARATOR << nameTitle
1206 << INNER_SEPARATOR << dataTypeTitle
1207 << INNER_SEPARATOR << nullableTitle
1208 << INNER_SEPARATOR << typeTitle
1209 << OUTER_SEPARATOR << endl;
1210 string t;
1211 t.resize(nameLen,LINE_CHAR);
1212 cout << OUTER_SEPARATOR << t;
1213 t.resize(dataTypeLen,LINE_CHAR);
1214 cout << INNER_SEPARATOR << t;
1215 t.resize(nullableLen,LINE_CHAR);
1216 cout << INNER_SEPARATOR << t;
1217 t.resize(typeLen,LINE_CHAR);
1218 cout << t << OUTER_SEPARATOR << endl;
1219 do {
1220 string name=colRs->getString(4);
1221 string dataType=colRs->getString(7);
1222
1223 string nullable;
1224 switch(colRs->getShort(12)) {
1225 case DatabaseMetaData::procedureNoNulls:
1226 nullable="No";
1227 break;
1228 case DatabaseMetaData::procedureNullable:
1229 nullable="Yes";
1230 break;
1231 case DatabaseMetaData::procedureNullableUnknown:
1232 default:
1233 nullable="?";
1234 break;
1235 }
1236
1237 string type;
1238 switch(colRs->getShort(5)) {
1239 case DatabaseMetaData::procedureColumnIn:
1240 type="IN"; break;
1241 case DatabaseMetaData::procedureColumnOut:
1242 type="OUT"; break;
1243 case DatabaseMetaData::procedureColumnInOut:
1244 type="IN OUT"; break;
1245 case DatabaseMetaData::procedureColumnReturn:
1246 type="RETURN"; break;
1247 case DatabaseMetaData::procedureColumnResult:
1248 type="RESULT"; break;
1249 default:
1250 type="?";
1251 }
1252
1253 name.resize(nameLen,SPACE_CHAR);
1254 dataType.resize(dataTypeLen,SPACE_CHAR);
1255 nullable.resize(nullableLen,SPACE_CHAR);
1256 type.resize(typeLen,SPACE_CHAR);
1257
1258 cout << OUTER_SEPARATOR << name
1259 << INNER_SEPARATOR << dataType
1260 << INNER_SEPARATOR << nullable
1261 << INNER_SEPARATOR << type
1262 << OUTER_SEPARATOR << endl;
1263
1264 } while(colRs->next());
1265 }
1266
1267 } while(procRs->next());
1268 }
1269
1270 } catch(SQLException& e) {
1271 //apparently no procedures
1272 }
1273 }
1274
1275 if(!(wasProcedure || wasTable)) {
1276 cout << "Could not find any table or procedure named " << arg << endl;
1277 }
1278 }
1279
1280
1281
1282
main(int argc,char ** argv)1283 int main(int argc, char** argv)
1284 {
1285 if(argc!=2 && argc!=4) {
1286 cerr << "Usage: " << argv[0] << " connect-string" << endl
1287 << "or " << argv[0] << " dsn username password" << endl;
1288 return 0;
1289 }
1290 try {
1291 Connection* con;
1292 if(argc==2) {
1293 con=DriverManager::getConnection(argv[1]);
1294 } else {
1295 con=DriverManager::getConnection(argv[1],argv[2],argv[3]);
1296 }
1297
1298 Deleter<Connection> _con(con);
1299 DatabaseMetaData* md=con->getMetaData();
1300 cout << "Connected to "
1301 << md->getDatabaseProductName() << " "
1302 << md->getDatabaseProductVersion()
1303 << " using " << md->getDriverName() << " "
1304 << md->getDriverVersion() << " (ODBC Version "
1305 << md->getDriverMajorVersion() << "."
1306 << md->getDriverMinorVersion() << ")" << endl;
1307
1308
1309 {
1310 Isql isql(con);
1311 isql.run();
1312 }
1313
1314 cout << endl;
1315 } catch(SQLException& e) {
1316 cerr << endl << "Whoops: " << e.getMessage() << endl;
1317 return 1;
1318 }
1319
1320 return 0;
1321 }
1322