1 module dpq2.query; 2 3 @trusted: 4 5 import dpq2; 6 7 enum ValueFormat : ubyte { 8 TEXT, 9 BINARY 10 } 11 12 /// Query parameters 13 struct QueryParams 14 { 15 string sqlCommand; /// SQL command 16 QueryArg[] args; /// SQL command arguments 17 ValueFormat resultFormat = ValueFormat.BINARY; /// Result value format 18 } 19 20 /// Query argument 21 struct QueryArg 22 { 23 Oid type = 0; 24 private ubyte[] valueBin; 25 26 /// s can be null for SQL NULL value 27 @property void value( string s ) 28 { 29 if( s == null ) 30 valueBin = null; 31 else 32 valueBin = cast(ubyte[])( s ~ '\0' ); 33 } 34 } 35 36 /// Connection 37 // Inheritance used here for separation of query code from connection code 38 final class Connection: BaseConnection 39 { 40 /// Perform SQL query to DB 41 immutable (Answer) exec( string SQLcmd ) 42 { 43 auto pgResult = PQexec(conn, toStringz( SQLcmd )); 44 45 // is guaranteed by libpq that the result will not be changed until it will not be destroyed 46 auto container = createResultContainer(cast(immutable) pgResult); 47 48 return new immutable Answer(container); 49 } 50 51 /// Perform SQL query to DB 52 immutable (Answer) exec(in QueryParams p) 53 { 54 auto a = prepareArgs( p ); 55 auto pgResult = PQexecParams ( 56 conn, 57 cast(const char*)toStringz( p.sqlCommand ), 58 cast(int)p.args.length, 59 a.types.ptr, 60 a.values.ptr, 61 cast(int*)a.lengths.ptr, 62 cast(int*)a.formats.ptr, 63 cast(int)p.resultFormat 64 ); 65 66 // is guaranteed by libpq that the result will not be changed until it will not be destroyed 67 auto container = createResultContainer(cast(immutable) pgResult); 68 69 return new immutable Answer(container); 70 } 71 72 /// Submits a command to the server without waiting for the result(s) 73 void sendQuery( string SQLcmd ) 74 { 75 const size_t r = PQsendQuery( conn, toStringz(SQLcmd) ); 76 if( r != 1 ) throw new ConnectionException(this, __FILE__, __LINE__); 77 } 78 79 /// Submits a command and separate parameters to the server without waiting for the result(s) 80 void sendQuery( in QueryParams p ) 81 { 82 auto a = prepareArgs( p ); 83 size_t r = PQsendQueryParams ( 84 conn, 85 cast(const char*)toStringz( p.sqlCommand ), 86 cast(int)p.args.length, 87 a.types.ptr, 88 a.values.ptr, 89 cast(int*)a.lengths.ptr, 90 cast(int*)a.formats.ptr, 91 cast(int)p.resultFormat 92 ); 93 94 if( !r ) throw new ConnectionException(this, __FILE__, __LINE__); 95 } 96 97 /// Returns null if no notifies was received 98 Notify getNextNotify() 99 { 100 consumeInput(); 101 auto n = PQnotifies(conn); 102 return n is null ? null : new Notify( n ); 103 } 104 105 private struct PreparedArgs 106 { 107 Oid[] types; 108 size_t[] formats; 109 size_t[] lengths; 110 const(ubyte)*[] values; 111 } 112 113 // For PQxxxParams need especially prepared arguments 114 private PreparedArgs* prepareArgs(ref const QueryParams p) 115 { 116 PreparedArgs* a = new PreparedArgs; 117 a.types = new Oid[p.args.length]; 118 a.formats = new size_t[p.args.length]; 119 a.lengths = new size_t[p.args.length]; 120 a.values = new const(ubyte)*[p.args.length]; 121 122 for( int i = 0; i < p.args.length; ++i ) 123 { 124 a.types[i] = p.args[i].type; 125 a.formats[i] = ValueFormat.TEXT; 126 a.values[i] = p.args[i].valueBin.ptr; 127 a.lengths[i] = p.args[i].valueBin.length; 128 } 129 130 return a; 131 } 132 } 133 134 void _integration_test( string connParam ) 135 { 136 auto conn = new Connection; 137 conn.connString = connParam; 138 conn.connect(); 139 140 { 141 string sql_query = 142 "select now() as time, 'abc'::text as string, 123, 456.78\n"~ 143 "union all\n"~ 144 "select now(), 'абвгд'::text, 777, 910.11\n"~ 145 "union all\n"~ 146 "select NULL, 'ijk'::text, 789, 12345.115345"; 147 148 auto a = conn.exec( sql_query ); 149 150 assert( a.cmdStatus.length > 2 ); 151 assert( a.columnCount == 4 ); 152 assert( a.length == 3 ); 153 assert( a.columnFormat(1) == ValueFormat.TEXT ); 154 assert( a.columnFormat(2) == ValueFormat.TEXT ); 155 } 156 157 { 158 const string sql_query = 159 "select $1::text, $2::integer, $3::text"; 160 161 QueryArg[3] args; 162 args[0].value = "абвгд"; 163 args[1].value = null; 164 args[2].value = "123"; 165 166 QueryParams p; 167 p.sqlCommand = sql_query; 168 p.args = args[]; 169 170 auto a = conn.exec( p ); 171 172 assert( a.columnFormat(1) == ValueFormat.BINARY ); 173 assert( a.columnFormat(2) == ValueFormat.BINARY ); 174 175 destroy(a); 176 } 177 178 conn.disconnect(); 179 180 { 181 bool exceptionFlag = false; 182 183 try conn.exec("SELECT 'abc'::text").getAnswer; 184 catch(ConnectionException e) 185 { 186 exceptionFlag = true; 187 assert(e.msg.length > 15); // error message check 188 } 189 finally 190 assert(exceptionFlag); 191 } 192 }