ORA-01745: invalid host/bind variable name ERROR in Delphi when we use Oracle DB

-
I received the above error message when TADOQuery.Open method is called. I checked the SQL of TAdoquery component which looked fine to me but was still getting errors. SQL was as follow

AdoQuery1.Sql.text := 'SELECT * FROM EMPLOYEE E WHERE E.JOINDATE < :DATE';
AdoQuery.Open; //---ABOVE ERROR RAISED----//

After researching the issue, I found that the issue is with :DATE parameter that I have used in SQL. Apparently the word "DATE" is a reserved word in Oracle, and therefore cannot be used as a SQL parameter or bind variable name. You can not use a key word as parameter name. 

So I replaced the :DATE parameter with :JDATE in SQL as follow and it worked fine without any error.

AdoQuery1.Sql.text := 'SELECT * FROM EMPLOYEE E WHERE E.JOINDATE < :JDATE';
AdoQuery.Open; //---ERROR RESOLVED----//

So please note* that you should not use any reserve keywords as  parameter while writing SQL in Delphi side.

Some of the common Oracle reserve keywords we use as parameter in SQL in Delphi side which we should avoid.
Example
:Start, :Date, :end, :number, :user, :rowid, :Rownum, :desc

For detail list of Oracle reserved words please visit following link...


Comments

Post a Comment

Popular posts from this blog

ShellExecute in Delphi

-
ShellExecute in Delphi – Launch external applications. ShellExecute is Delphi Windows API function that is mostly used for launch external applications from our Delphi application. This function is linked to the ShellExecute Windows  API function. The function returns an integer that corresponds to an error code which is very useful when we need to show some status  if the function worked  or not . By using ShellExecute we can also do following operations.... Can print documents from within my program, without explicitly starting the application  that created the document, such as: print a Word-document without starting Word. Can open browser with a local HTML page Can surf to a site i.e. open an external URL link from a Delphi application Can send mails thorugh outlook Syntax of Windows API function HINSTANCE ShellExecute(   _In_opt_        HWND hwnd,   _In_opt_        LPCTSTR lpOperation,   _In_              LPCTSTR lpFile,   _In_opt_        LPC
Read more

How to send Email in Delphi?

-
Today in most of software program it is required to send mail to others with attached data. As some time we need to update clients about their account details, status or any other information through mail. So Delphi provides so many easiest ways to send mail from our Delphi application. I have collected some of ways which are very useful for Delphi developers. In Delphi we don't required any third party components to send mails. We can send mail to single or group of peoples with CC, BCC and attachments. Even we can send HTML mails from Delphi. So here are the ways... 1. ShellExecute Sends Email using default mail client software installed on user's system. But it will not work with attachment for every mail client. And you have to use ShellAPI  unit in uses list. ShellExecute(Self.Handle,              nil,              'mailto:' +              'jiten.g.s001@gmail.com' +              '?Subject=Test Message Subject' +            
Read more

Variants in Delphi. Use of Variant Array. How to check a Variant is unassigned or empty or clear ?

-
Sometimes it is necessary to store and change data whose type varies or cannot be determined at compile time. In these cases, we can use variables and parameters of type Variant, which represents values that can change type at run time. Variants are used to store any data type value and can  perform numerous operations at run time. For variants type values the compiler automatically performs type conversions. Once you have the variant value, you can assign it to any compatible-or incompatible-data type. If you assign a value to an incompatible data type, Delphi performs a conversion, if it can. Otherwise it issues a run-time error. By default, Variants can hold values of any type except records, sets, static arrays, files, classes, class references, and pointers. Use System.Variants unit. Example We declare a variant variable such as the following: var   V: Variant;    You can assign to it values of several different types: V := UnAssigned; V := NULL; V := 10; V := '
Read more