1 2(********************************************************************) 3(* *) 4(* sql7.sd7 Sql command line tool *) 5(* Copyright (C) 2014, 2017 Thomas Mertes *) 6(* *) 7(* This program is free software; you can redistribute it and/or *) 8(* modify it under the terms of the GNU General Public License as *) 9(* published by the Free Software Foundation; either version 2 of *) 10(* the License, or (at your option) any later version. *) 11(* *) 12(* This program is distributed in the hope that it will be useful, *) 13(* but WITHOUT ANY WARRANTY; without even the implied warranty of *) 14(* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the *) 15(* GNU General Public License for more details. *) 16(* *) 17(* You should have received a copy of the GNU General Public *) 18(* License along with this program; if not, write to the *) 19(* Free Software Foundation, Inc., 51 Franklin Street, *) 20(* Fifth Floor, Boston, MA 02110-1301, USA. *) 21(* *) 22(********************************************************************) 23 24 25$ include "seed7_05.s7i"; 26 include "keybd.s7i"; 27 include "console.s7i"; 28 include "osfiles.s7i"; 29 include "editline.s7i"; 30 include "sql_base.s7i"; 31 include "utf8.s7i"; 32 33 34const proc: writeHelp is func 35 begin 36 writeln("usage: sql7 driver dbName user [password [sqlFile]]"); 37 writeln; 38 writeln("The following drivers are supported: "); 39 writeln(" Driver Shortcut Database"); 40 writeln(" MYSQL MY MySql/MariaDb"); 41 writeln(" SQLITE LITE SQLLite"); 42 writeln(" POSTGRESQL POST PostgreSQL"); 43 writeln(" ORACLE OCI Oracle"); 44 writeln(" ODBC Odbc"); 45 writeln(" FIREBIRD FIRE Firebird/Interbase"); 46 writeln(" DB2 Db2"); 47 writeln(" SQL_SERVER SQLSRV SQL Server"); 48 writeln; 49 writeln("The database name (dbName) can be specified with or without host name:"); 50 writeln(" myDb"); 51 writeln(" www.example.org/myDb"); 52 writeln(" 192.0.1.250/myDb"); 53 writeln; 54 writeln("Sql7 accepts SQL commands of the connected database."); 55 writeln("Commands to exit sql7: exit, quit"); 56 writeln; 57 end func; 58 59 60const func boolean: failed (in proc: statement) is func 61 result 62 var boolean: failed is FALSE; 63 begin 64 block 65 statement; 66 exception 67 catch RANGE_ERROR: 68 failed := TRUE; 69 catch FILE_ERROR: 70 failed := TRUE; 71 catch DATABASE_ERROR: 72 failed := TRUE; 73 end block; 74 end func; 75 76 77const proc: displayResult (in sqlStatement: statement) is func 78 local 79 const integer: width is 20; 80 var integer: index is 0; 81 begin 82 for index range 1 to columnCount(statement) do 83 write(columnName(statement, index) rpad width <& " "); 84 end for; 85 writeln; 86 writeln(("-" mult width & " ") mult columnCount(statement)); 87 while fetch(statement) do 88 for index range 1 to columnCount(statement) do 89 if failed(write(noCtrlChars(column(statement, index, string)[1 len width]) rpad width <& " ")) and 90 failed(write( column(statement, index, integer) lpad width <& " ")) and 91 failed(write( column(statement, index, bigInteger) lpad width <& " ")) and 92 failed(write( column(statement, index, bigRational) lpad width <& " ")) and 93 failed(write( column(statement, index, float) sci width - 7 exp 2 lpad width <& " ")) and 94 failed(write(strDateTime(column(statement, index, time)) rpad width <& " ")) and 95 failed(write( column(statement, index, duration) rpad width <& " ")) and 96 failed(write( column(statement, index, boolean) lpad width <& " ")) then 97 write("*" mult width <& " "); 98 end if; 99 end for; 100 writeln; 101 end while; 102 end func; 103 104 105const proc: doExecute (in database: currDb, in var string: command) is func 106 local 107 var sqlStatement: statement is sqlStatement.value; 108 var boolean: failed is FALSE; 109 begin 110 if endsWith(command, ";") then 111 command := command[.. pred(length(command))]; 112 end if; 113 block 114 statement := prepare(currDb, command); 115 execute(statement); 116 exception 117 catch DATABASE_ERROR: 118 writeln; 119 writeln(command); 120 writeln(" *** Database error: " <& errMessage(DATABASE_ERROR)); 121 writeln; 122 failed := TRUE; 123 otherwise: 124 writeln; 125 writeln(command); 126 writeln(" *** Error occurred"); 127 writeln; 128 failed := TRUE; 129 end block; 130 if not failed then 131 if columnCount(statement) <> 0 then 132 displayResult(statement); 133 else 134 writeln; 135 writeln("Success."); 136 writeln; 137 end if; 138 end if; 139 end func; 140 141 142const func string: getSqlStatement (inout file: inFile) is func 143 result 144 var string: sqlStatement is ""; 145 local 146 var char: ch is ' '; 147 var char: delimiter is ' '; 148 begin 149 ch := getc(inFile); 150 while ch <> ';' and ch <> EOF do 151 if ch = ''' or ch = '"' then 152 delimiter := ch; 153 sqlStatement &:= delimiter; 154 ch := getc(inFile); 155 while ch <> delimiter and ch <> EOF do 156 sqlStatement &:= ch; 157 ch := getc(inFile); 158 end while; 159 if ch = delimiter then 160 sqlStatement &:= delimiter; 161 ch := getc(inFile); 162 end if; 163 elsif ch = '/' then 164 ch := getc(inFile); 165 if ch <> '*' or ch = EOF then 166 sqlStatement &:= '/'; 167 else 168 ch := getc(inFile); 169 repeat 170 while ch <> '*' and ch <> EOF do 171 ch := getc(inFile); 172 end while; 173 ch := getc(inFile); 174 until ch = '/' or ch = EOF; 175 ch := getc(inFile); 176 end if; 177 elsif ch = '-' then 178 ch := getc(inFile); 179 if ch <> '-' or ch = EOF then 180 sqlStatement &:= '-'; 181 else 182 ch := getc(inFile); 183 while ch <> '\n' and ch <> EOF do 184 ch := getc(inFile); 185 end while; 186 end if; 187 else 188 sqlStatement &:= ch; 189 ch := getc(inFile); 190 end if; 191 end while; 192 inFile.bufferChar := ch; 193 end func; 194 195 196const proc: main is func 197 local 198 var string: fileName is ""; 199 var file: sqlFile is STD_NULL; 200 var string: driverName is ""; 201 var dbCategory: driver is NO_DB; 202 var string: password is ""; 203 var database: currDb is database.value; 204 var string: command is ""; 205 begin 206 OUT := STD_CONSOLE; 207 IN := openEditLine(KEYBOARD, OUT); 208 if length(argv(PROGRAM)) = 5 then 209 fileName := convDosPath(argv(PROGRAM)[5]); 210 sqlFile := openUtf8(fileName, "r"); 211 end if; 212 if sqlFile = STD_NULL then 213 writeln("Sql7 - Sql command line tool"); 214 writeln("Copyright (C) 2014 Thomas Mertes"); 215 writeln("This is free software; see the source for copying conditions. There is NO"); 216 writeln("warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE."); 217 writeln("Sql7 is written in the Seed7 programming language"); 218 writeln("Homepage: http://seed7.sourceforge.net"); 219 writeln; 220 end if; 221 if length(argv(PROGRAM)) = 0 then 222 writeln("Use sql7 -? to get more information about sql7."); 223 writeln; 224 elsif length(argv(PROGRAM)) = 1 and argv(PROGRAM)[1] = "-?" then 225 writeHelp; 226 elsif length(argv(PROGRAM)) not in {3, 4, 5} then 227 writeln("usage: sql7 driver dbName user [password [sqlfile]]"); 228 elsif length(argv(PROGRAM)) = 5 and sqlFile = STD_NULL then 229 writeln(" *** Cannot open " <& fileName); 230 else 231 driverName := argv(PROGRAM)[1]; 232 driver := dbCategory(driverName); 233 if driver = NO_DB then 234 writeln("*** " <& literal(driverName) <& " is not a valid database driver."); 235 else 236 if length(argv(PROGRAM)) = 3 then 237 write("Password: "); 238 flush(OUT); 239 password := readPassword(IN); 240 else 241 password := argv(PROGRAM)[4]; 242 end if; 243 block 244 currDb := openDatabase(driver, argv(PROGRAM)[2], argv(PROGRAM)[3], password); 245 exception 246 catch DATABASE_ERROR: 247 writeln(" *** Database error: " <& errMessage(DATABASE_ERROR)); 248 otherwise: 249 writeln(" *** Cannot open " <& argv(PROGRAM)[2]); 250 end block; 251 if currDb <> database.value then 252 if sqlFile <> STD_NULL then 253 while hasNext(sqlFile) do 254 command := getSqlStatement(sqlFile); 255 command := trim(command); 256 if command <> "" then 257 # writeln(literal(command)); 258 doExecute(currDb, command); 259 end if; 260 end while; 261 close(sqlFile); 262 else 263 write("Sql7? "); 264 readln(command); 265 while lower(command) <> "exit" and lower(command) <> "quit" do 266 if command <> "" then 267 doExecute(currDb, command); 268 end if; 269 write("Sql7? "); 270 readln(command); 271 end while; 272 end if; 273 close(currDb); 274 end if; 275 end if; 276 end if; 277 end func; 278