SQL has result or not lazarus / 16-Databases ---------------------------------------------------------------------- aradeonas · 2016-05-18 18:42 Hi, I want to find out that a SQL code returned result or not but if I do Open and it has no result (for example a insert code) it makes error, I want to somehow check results count for this query and when it 0 so I can find out I should not Open it and just execute it. Any library is good SQLDB or Zeos or whatever, I just want to know it has result records or not. ---------------------------------------------------------------------- balazsszekely · 2016-05-18 20:18 Check SQLQuery1.RowsAffected after the insert, then make a rollback if necessary. You can handle any error in the meantime. ---------------------------------------------------------------------- aradeonas · 2016-05-18 20:23 It seems you didn't understand the case properly. I dont know what SQL is, a select or a update so I dont know to call open or execute and I want to find out that. ---------------------------------------------------------------------- balazsszekely · 2016-05-18 20:56 @aradeonas QuoteIt seems you didn't understand the case properly.Fair enough! But please read your first question again and tell me what do you understand? ---------------------------------------------------------------------- aradeonas · 2016-05-18 21:02 It seems confusing but as I said "find out I should not Open it and just execute it." So I would not make a conclusion like you either but anyway sorry for confusing question, now you seems understand the problem do you know what is the answer? ---------------------------------------------------------------------- paweld · 2016-05-18 21:17 Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i try  query1.Open;except  ShowMessage('query don''t return results');end;if not query1.Active then exit; ---------------------------------------------------------------------- aradeonas · 2016-05-18 21:20 thanks paweld but its not a clear answer and it even makes debugging a problem. I want a standard way to check query has a result or not so I can prevent Query error when you open one of them with a SQL that will not has a result like a insert command. ---------------------------------------------------------------------- mangakissa · 2016-05-19 07:54 QuoteI want to find out that a SQL code returned result or not but if I do Open and it has no result (for example a insert code) it makes error, I want to somehow check results count for this query and when it 0 so I can find out I should not Open it and just execute it. QuoteI dont know what SQL is, a select or a update so I dont know to call open or execute and I want to find out that. You are contradicting yourself A error only occurs when your query is wrong. When opened the dataset, and there are no records, recordcount will be 0. Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i query1.append;query1.fieldbyname('somefield').Value = 'I am confused';query1.post;if not query1.locate(somefield,'I am confused',[]) then  showmessage('record not found'); If you are using an insert as query: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i Query1.SQL.text := 'INSERT INTO (somefields) VALUES ('I am confused')';Query1.ExecSQL;if Query1.rowsaffected = -1 then  showmessage('record not inserted'); ---------------------------------------------------------------------- aradeonas · 2016-05-19 08:51 Let me tell you by example : Code: [Select]Select * from test; What should I do? Open or Execute the query? Open obviously. Code: [Select]Insert into test('test'); What should I do? Open or Execute  the query? Execute obviously. Code: [Select]Call testprocedure(); What should I do? Open or Execute the query? I dont know, if it will have result I should do Open but if it is a procedure without any result if I call Open, it will raise an error that you should not open a query without any resultset. ---------------------------------------------------------------------- mangakissa · 2016-05-19 09:22 1 . Open your dataset 2.  Use property execSQL, which results no dataset. 3.  Use TZStoredProc (ZEOS) or TSQLQuery (SQLdb) Please read http://wiki.freepascal.org/SQLdb_Package ---------------------------------------------------------------------- aradeonas · 2016-05-19 10:25 It seems that I cant explain what I want or you miss my point. Anyway I will try to find a way. Thanks. ---------------------------------------------------------------------- rvk · 2016-05-19 10:37 Quote from: aradeonas on May 19, 2016, 12:25:53 pmIt seems that I cant explain what I want or you miss my point. Quote from: mangakissa on May 19, 2016, 11:22:22 am1 . Open your dataset 2.  Use property execSQL, which results no dataset. 3.  Use TZStoredProc (ZEOS) or TSQLQuery (SQLdb) He wants to know beforehand, regardless of SQL-statement, WHAT option he should choose. I don't think this is possible. For the SELECT and INSERT you could scan the SQL-text yourself and take the appropriate method. But for a procedure you will need to execute it first and see if it produces an error  (like paweld showed you). You could of course try to retrieve the procedure-source and see/scan if that returns a result-set. ---------------------------------------------------------------------- mangakissa · 2016-05-19 11:08 I'll buy a lottery ticket if the result is a huge price........ ---------------------------------------------------------------------- aradeonas · 2016-05-19 11:23 That exactly that I wanted @rvk and it seems I couldn't explain it correctly because of lack of my English skills. Thanks. ---------------------------------------------------------------------- LacaK · 2016-05-19 11:38 You can Prepare and then examine StatementType (not perfect, but may help in most cases) ---------------------------------------------------------------------- goodname · 2016-05-19 12:25 There is a PostgreSQL specific function called PQdescribePrepared that would work in this case. It is used by SQLdb to change insert statements (eg. INSERT INTO ... RETURNING id;) into select type statements so returning data can be captured. Not sure how to call it from SQLdb or if there is something similar for other database engines. pqconnection Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i procedure TPQConnection.PrepareStatement...      if (FStatementType in [stInsert,stUpdate,stDelete]) and (pos('RETURNING', upcase(s)) > 0) then        begin        PQclear(res);        res := PQdescribePrepared(tr.PGConn,pchar(StmtName));        if (PQresultStatus(res) = PGRES_COMMAND_OK) and (PQnfields(res) > 0) then          FStatementType := stSelect;        end; http://www.postgresql.org/docs/9.0/static/libpq-exec.html QuotePQdescribePrepared     Submits a request to obtain information about the specified prepared statement, and waits for completion.     PGresult *PQdescribePrepared(PGconn *conn, const char *stmtName);     PQdescribePrepared allows an application to obtain information about a previously prepared statement. PQdescribePrepared is supported only in protocol 3.0 and later connections; it will fail when using protocol 2.0.     stmtName can be "" or NULL to reference the unnamed statement, otherwise it must be the name of an existing prepared statement. On success, a PGresult with status PGRES_COMMAND_OK is returned. The functions PQnparams and PQparamtype can be applied to this PGresult to obtain information about the parameters of the prepared statement, and the functions PQnfields, PQfname, PQftype, etc provide information about the result columns (if any) of the statement. ---------------------------------------------------------------------- aradeonas · 2016-05-19 16:04 Thanks all but problem is procedures as rvk said.