TSQLQuery how to determine if SQL is a select(open) or update,insert,delete lazarus / 16-Databases ---------------------------------------------------------------------- snorkel · 2013-03-04 16:41 Hi, Does anyone know if there is a way to determine if the sql being executed needs a open i.e. returns a result set or is e execute i.e. execsql? I am writing a simply query editor, and right now I have to use two buttons, one for open and one for execsql.  I would like to just have one button. ---------------------------------------------------------------------- taazz · 2013-03-04 17:18 with out a complete sql evaluator that knows what ever the server knows it is impossible to do that. You see the server might have stored procedures user made that return a dataset which you can't know unless you execute the statement on server. I usually go for the easy solution execute the statement if it raises an exception then execute it if it still raises an exception so the exception message to the end user. OF course you can try to recognize ddl statements and make sure that they executed instead of opened. If I remember correctly an sql parser comes with lazarus which you can use. ---------------------------------------------------------------------- Nebula · 2013-03-04 21:17 I vaguely remember mucking about with SQL from VB a few years ago, and the method I used took anything I threw at it. Sometimes the resulting recordset was closed, or had zero rows, but it never error-ed. Does it work differently here in Laz-land? ---------------------------------------------------------------------- goodname · 2013-03-04 23:19 Know that for PostgreSQL the pglib library has a function that will tell you if a query has a result set or not. It is used by the patch that enables the use of Insert into ... returning id; Don't recall the function name or know if there is a direct way of accessing this function with SQLdb or know about other database engines. It could be a place to start if your willing to go down into the internals of SQLdb. ---------------------------------------------------------------------- Lacak2 · 2013-03-05 06:12 You can use: 1. TSQLQuery.Prepare 2. test TSQLQuery.StatementType, if = stSelect (or stExecProcedure)  (of course in case of stored procedure you can not determine with this technique if SP returns resultset or not) ---------------------------------------------------------------------- snorkel · 2013-03-06 00:18 Thanks Lacak2 I will give that a try. ---------------------------------------------------------------------- snorkel · 2013-03-06 00:35 Seems the statement type property is no longer there. I can catch a exception that is raised if I try to open a non select statement, not ideal, but I can get that to work. ---------------------------------------------------------------------- BigChimp · 2013-03-06 07:06 Snorkel, WHAT VERSION OF FPC ARE YOU USING? See signature. You never post your versions which is not helpful sometimes when trying to assist. AFAIR, Lacak2 has incorporated those statementtypes in FPC trunk. Don't think they've been backported to 2.6.2 but he'll be able to correct me ---------------------------------------------------------------------- Nebula · 2013-03-06 10:32 I've had a little play and indeed it errors when a non-SELECT is issued. Can I ask how you catch the error? This still gives an error, on the query.open line (Debugger Exception Notification), before the message appears! (does that only appear when coding, and not in the finished app?) Code: [Select]      query.SQL.Text := 'delete from my_empty_table';       query.PacketRecords := -1;  // otherwise we only get .RecordCount = 10!       try         query.open;       except           ShowMessage('Unknown error encountered');       end; (I had to wade through a lot of forum posts that Google gave me (for lazarus try finally) just to get this far - shame it didn't take me straight to a definitive wiki piece) ---------------------------------------------------------------------- taazz · 2013-03-06 10:36 all exception are captured by the debugger before they reach your application if you run your application with out the debugger you will see your own message only. That is expected for a debugger so you can track the problems in your application. ---------------------------------------------------------------------- Nebula · 2013-03-06 11:04 Thanks - hadn't played with that before  Is there a way to turn it off, if it gets annoying? ---------------------------------------------------------------------- BigChimp · 2013-03-06 13:00 There's a checkbox that you can enable in the exception message dialog so it won't inform you of those kinds of exceptions again. IIRC, these "exception exceptions" are stored in the project options somewhere. ---------------------------------------------------------------------- Nebula · 2013-03-06 14:58 Thanks ---------------------------------------------------------------------- snorkel · 2013-03-06 15:38 Quote from: BigChimp on March 06, 2013, 08:06:27 amSnorkel, WHAT VERSION OF FPC ARE YOU USING? See signature. You never post your versions which is not helpful sometimes when trying to assist. AFAIR, Lacak2 has incorporated those statementtypes in FPC trunk. Don't think they've been backported to 2.6.2 but he'll be able to correct me Sorry BigChimp, I thought this was a fairly generic question, I usually do post the versions I am using. I will add it to my signature so I don't forget. Thanks. ---------------------------------------------------------------------- snorkel · 2013-03-06 15:40 Quote from: Nebula on March 06, 2013, 11:32:11 amI've had a little play and indeed it errors when a non-SELECT is issued. Can I ask how you catch the error? except             on e:EDatabaseError do                begin                     if e.message = 'OpenQuery : Cannot open a non- select statement' then                        begin                             try                                aform.ExecQuery.SQL.clear;                                aform.ExecQuery.SQL.Add(query);                                aform.ExecQuery.ExecSQL;                             finally                                   aform.Trans.CommitRetaining;                             end;                             //show rows affected                             aform.ResultsPC.ActivePageIndex:=1;                             msgstr:=format('%d row(s) affected',[aform.ExecQuery.RowsAffected]);                             aform.addmsg(msgstr,info);                        end else                            raise; //some other exception so re raise it.                end; ---------------------------------------------------------------------- Nebula · 2013-03-07 12:45 I've been experimenting. It is indeed different in .net .... it doesn't error if you attempt to read from a command that doesn't SELECT. DataAdapter.Fill method is OK Code: [Select]        Dim sqlConnection As New SqlClient.SqlConnection("server=MYSERVER; database=MYDB; Integrated Security=SSPI")         Dim sqlAdapter As New SqlClient.SqlDataAdapter("DELETE FROM mytable WHERE field=whatever", sqlConnection)         Dim sqlDataTable As New DataTable         sqlAdapter.Fill(sqlDataTable) DataReader method is OK Code: [Select]        Using sqlConnection As New SqlClient.SqlConnection("server=MYSERVER; database=MYDB; Integrated Security=SSPI")             sqlConnection.Open()             Using objComm As New SqlClient.SqlCommand("INSERT INTO SomeType (id,name) VALUES (99,'hi')", sqlConnection)                 objComm.CommandType = CommandType.Text                 Dim reader As SqlClient.SqlDataReader = objComm.ExecuteReader()                 Try                     While reader.Read()                         Console.WriteLine(reader(0))                     End While                 Finally    ' Always call Close when done reading.                     reader.Close()                 End Try             End Using         End Using No problem. So, why does it have to be different in FPC? What's the point of throwing an exception? Is it a philosophical difference, or can one approach be regarded as a bug? ---------------------------------------------------------------------- Lacak2 · 2013-03-07 13:08 Quote from: snorkel on March 06, 2013, 01:35:38 amSeems the statement type property is no longer there. Sorry StatementType was protected and only 6 weeks ago Michael made it public in TRUNK (2.7.1) ---------------------------------------------------------------------- snorkel · 2013-03-07 16:02 Quote from: Nebula on March 07, 2013, 01:45:24 pmI've been experimenting. It is indeed different in .net .... it doesn't error if you attempt to read from a command that doesn't SELECT. No problem. So, why does it have to be different in FPC? What's the point of throwing an exception? Is it a philosophical difference, or can one approach be regarded as a bug? I think it's just the way the tdataset stuff in delphi/FPC is designed.  I know in 3rd party database controls for Lazarus from Devart.com (PGDac) works a bit differently, you can just call execsql for all statements and it will return a result set if there is one. ---------------------------------------------------------------------- Nebula · 2013-03-07 19:13 Hmmm... I just can't think of any reason why an exception is a good thing. Why not just return an empty rowset (or reader at eof)  like in .net? ---------------------------------------------------------------------- taazz · 2013-03-08 02:07 Because some servers do not return any rowset and no rowset is different than an empty one. This means that the dataset component does not have any idea what is the underline schema so any action like insert or update will raise an exception. Better to inform the user that there is no direct connection to the database than to let him thing there is one. ---------------------------------------------------------------------- BigChimp · 2013-03-08 06:35 Quote from: Nebula on March 07, 2013, 08:13:17 pmHmmm... I just can't think of any reason why an exception is a good thing. Why not just return an empty rowset (or reader at eof)  like in .net? I really don't understand what the problem is. Why would you want to try to read records if you already know there are none? If you use an execute type statement (DELETE FROM SomeTable etc) and try to .open it, you're making a programming error IMO. Better that you get an exception at run time that warns you of errors than letting you try and get data that does not exist because the SQL statement was wrong. If you want to dynamically run SQL queries etc (where you don't know in advance if they return data or not), use StatementType (or in older versions of FPC, just catch the proper exception on .Open and run .Execute). Can you tell me what functionality .Net has that FPC SQLDB misses? ---------------------------------------------------------------------- Nebula · 2013-03-08 09:45 Quote from: BigChimp on March 08, 2013, 07:35:11 amCan you tell me what functionality .Net has that FPC SQLDB misses? Just the ability to squirt any SQL at it without an error  Just makes it friendlier and more versatile IMHO. 9 times out of 10 the programmer will know not to expect any results, so it's no big deal. It's good enough for MS ---------------------------------------------------------------------- snorkel · 2013-03-08 16:58 I think having the statement type property surfaced is sufficient, would have been nice if it was in 2.6.2 though. For most apps you are going to know well in advance if you need to open or execsql, it's only cumbersome if you are doing something that lets the user free form enter SQL to execute with a single button. Does anyone know if it's possible to do something similar with Zeos? ---------------------------------------------------------------------- BigChimp · 2013-03-09 06:39 Quote from: snorkel on March 08, 2013, 05:58:55 pmFor most apps you are going to know well in advance if you need to open or execsql, it's only cumbersome if you are doing something that lets the user free form enter SQL to execute with a single button. And as a well behaved application should catch exceptions there anyway (i.e. user entered ridiculous SQL or MS SQL Server SQL when accessing a MySQL database).... it's not that much extra work. IMO not more than adding that read loop and then having to check for empty values. ... but I'm afraid I'm repeating myself, so I'll shut up now ---------------------------------------------------------------------- Nebula · 2013-03-09 08:37 i did ask "Is it a philosophical difference?" and it seems it is  Thanks for the debate anyway! ---------------------------------------------------------------------- BigChimp · 2013-03-09 09:25 Quote from: Nebula on March 09, 2013, 09:37:23 ami did ask "Is it a philosophical difference?" and it seems it is  Thanks for the debate anyway! Yep Always nice talking with you