Topic
  • 14 replies
  • Latest Post - ‏2014-02-25T17:20:16Z by RainerRoss
RainerRoss
RainerRoss
11 Posts

Pinned topic OmniFind - SQL Error -418 in embedded SQL

‏2014-02-20T10:19:06Z |

Hi,

I've installed OmniFind 1.2 on my new Power720 7+ with V7R1, SMP, the latest PTF-Level for the Database and OmniFind and I want to integrate 

OmniFind in my RPG-Programs. In the whitepaper from Kent Milligan 'Exploring the IBM OmniFind Text Search Server' is the following example:

char search_arg[100];
EXEC SQL DECLARE C1 CURSOR FOR SELECT custkey FROM customers WHERE CONTAINS( comments, :search_arg, 'RESULTLIMIT = 100') = 1 ;
EXEC SQL SET :search_arg = 'product change';
EXEC SQL OPEN C1;

this works fine with my own database. I changed the example like this:

char sqlcommand[256];
EXEC SQL SET :sqlcommand = 'SELECT custkey FROM customers WHERE CONTAINS( comments, ?, 'RESULTLIMIT = 100') = 1 ';
EXEC SQL PREPARE SQLCMD from :sqlcommand;
EXEC SQL DECLARE C1 CURSOR FOR SQLCMD;
EXEC SQL SET :search_arg = 'product change';
EXEC SQL OPEN C1 using :search_arg;

After the PREPARE Statement Error -418 appears and I don't know what's been wrong.

Thanks

Rainer

 

 

 

 

Updated on 2014-02-22T13:53:41Z at 2014-02-22T13:53:41Z by RainerRoss
  • RainerRoss
    RainerRoss
    11 Posts
    ACCEPTED ANSWER

    Re: OmniFind - SQL Error -418 in embedded SQL

    ‏2014-02-25T17:20:16Z  

     s#sqlcmd = 'select empid,skill from tsto.employee where contains(skill , ? and ? ) = 1';

    To use a parameter marker on a function call in a dynamic SQL statement, DB2 needs to know the data type of the argument. In addition, the search argument (2nd parameter) of the CONTAINS function must be a character or graphic string data type.

    The CONTAINS should should be something like  CONTAINS(skill, CAST(? AS VARCHAR(500)))

    The host variable for the parameter marker should be supplied at the time the cursor is open. (I don't see that code)

    The type of the host variable must be 'castable' to the type indicated for the parameter marker, but does not have to be exactly the same type...as long as DB2 can perform the conversion.

    Suppose you want to do a search for   'term1 AND term2'  where term1 and term2 are supplied by different parameter markers.  This is a little more complicated, since you now have to build the search expression...but you can still do it with something like.

    CONTAINS(skill, CAST(? AS VARCHAR(2000)) CONCAT ' AND ' CONCAT  CAST(? AS VARCHAR(2000))) = 1

    NOTE:  If you are using DB2 for i 7.1, the above syntax can often be simplified. DB2 for i allows untyped parameter markers when they are used as arguments to the CONCAT operator (DB2 for i will assign a default type in this case) http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/index.jsp?topic=%2Fdb2%2Frbafzpreph2.htm

    The extra support makes SQL statements somewhat easier to code and read.

    a lot of thanks, Nick - it works

  • tomliotta
    tomliotta
    49 Posts

    Re: OmniFind - SQL Error -418 in embedded SQL

    ‏2014-02-20T11:40:16Z  

    Well, SQLCODE -418 means "A parameter marker is not allowed." Whether that's the actual problem or it appears because of another problem is hard to know from here. First thing I'd try is to correct the quotes that seem to be wrong in your sqlcommand SELECT statement. Since you turned it into a literal constant, the embedded apostrophes should be doubled up.

    Tom

  • RainerRoss
    RainerRoss
    11 Posts

    Re: OmniFind - SQL Error -418 in embedded SQL

    ‏2014-02-20T12:02:50Z  
    • tomliotta
    • ‏2014-02-20T11:40:16Z

    Well, SQLCODE -418 means "A parameter marker is not allowed." Whether that's the actual problem or it appears because of another problem is hard to know from here. First thing I'd try is to correct the quotes that seem to be wrong in your sqlcommand SELECT statement. Since you turned it into a literal constant, the embedded apostrophes should be doubled up.

    Tom

    Hi Tom,

    I changed it to:

    EXEC SQL SET :sqlcommand = 'SELECT custkey FROM customers WHERE CONTAINS( comments, ? ) = 1 ';

    the same Error appears

    I changed it to:

    EXEC SQL SET :sqlcommand = 'SELECT custkey FROM customers WHERE comments like ? ';

    this command works

    Rainer

    Updated on 2014-02-20T12:07:12Z at 2014-02-20T12:07:12Z by RainerRoss
  • tomliotta
    tomliotta
    49 Posts

    Re: OmniFind - SQL Error -418 in embedded SQL

    ‏2014-02-20T12:38:26Z  

    Hi Tom,

    I changed it to:

    EXEC SQL SET :sqlcommand = 'SELECT custkey FROM customers WHERE CONTAINS( comments, ? ) = 1 ';

    the same Error appears

    I changed it to:

    EXEC SQL SET :sqlcommand = 'SELECT custkey FROM customers WHERE comments like ? ';

    this command works

    Rainer

    Did you try:

    EXEC SQL SET :sqlcommand = 'SELECT custkey FROM customers WHERE CONTAINS( comments, ?, ''RESULTLIMIT = 100'') = 1 ';

    (Doubled embedded apostrophes around {RESULTLIMIT = 100}.)

  • RainerRoss
    RainerRoss
    11 Posts

    Re: OmniFind - SQL Error -418 in embedded SQL

    ‏2014-02-20T12:44:39Z  
    • tomliotta
    • ‏2014-02-20T12:38:26Z

    Did you try:

    EXEC SQL SET :sqlcommand = 'SELECT custkey FROM customers WHERE CONTAINS( comments, ?, ''RESULTLIMIT = 100'') = 1 ';

    (Doubled embedded apostrophes around {RESULTLIMIT = 100}.)

    I did it - same Error appears

  • tomliotta
    tomliotta
    49 Posts

    Re: OmniFind - SQL Error -418 in embedded SQL

    ‏2014-02-20T13:17:41Z  

    I did it - same Error appears

    I don't see any examples with "?" used as a parameter marker. I do see that "?" is a meaningful character in a text search-argument.

    So, I'd change it to use static SQL with a :host-variable for the variable portion. I do see examples with :host-variables for search-arguments.

    Something like:

    EXEC SQL DECLARE C1 CURSOR FOR SELECT custkey FROM customers
                     WHERE CONTAINS( comments, :mysearch, 'RESULTLIMIT = 100') = 1 ;
    strcpy(mysearch, "some text") ;
    EXEC SQL OPEN C1 using :mysearch ;

    Updated on 2014-02-20T13:30:20Z at 2014-02-20T13:30:20Z by tomliotta
  • RainerRoss
    RainerRoss
    11 Posts

    Re: OmniFind - SQL Error -418 in embedded SQL

    ‏2014-02-20T13:51:21Z  
    • tomliotta
    • ‏2014-02-20T13:17:41Z

    I don't see any examples with "?" used as a parameter marker. I do see that "?" is a meaningful character in a text search-argument.

    So, I'd change it to use static SQL with a :host-variable for the variable portion. I do see examples with :host-variables for search-arguments.

    Something like:

    EXEC SQL DECLARE C1 CURSOR FOR SELECT custkey FROM customers
                     WHERE CONTAINS( comments, :mysearch, 'RESULTLIMIT = 100') = 1 ;
    strcpy(mysearch, "some text") ;
    EXEC SQL OPEN C1 using :mysearch ;

    Thanks Tom,

    this statement works, but I need the dynamic SQL making flexible search statements. In my application
    I made it like this:

    select custkey from customers where comments like ? and comments like ?

    EXEC SQL OPEN C1 using :search_arg1, :search_ag2;

    with OmniFind it looks like this

    select custkey from customers contains(comments, ? and ?) = 1

    It works with static Statements

    select custkey from customers contains(comments, :search_arg1 and :search_arg2 ) = 1

    but not in the flexible way

     

     

  • tomliotta
    tomliotta
    49 Posts

    Re: OmniFind - SQL Error -418 in embedded SQL

    ‏2014-02-21T13:55:53Z  

    Thanks Tom,

    this statement works, but I need the dynamic SQL making flexible search statements. In my application
    I made it like this:

    select custkey from customers where comments like ? and comments like ?

    EXEC SQL OPEN C1 using :search_arg1, :search_ag2;

    with OmniFind it looks like this

    select custkey from customers contains(comments, ? and ?) = 1

    It works with static Statements

    select custkey from customers contains(comments, :search_arg1 and :search_arg2 ) = 1

    but not in the flexible way

     

     

    I'm not clear on how it is any less flexible. Can you give an example of what you can do one way but not the other? Is it a matter of needing multiple functions at different times? Perhaps additional suggestions can be made with better understanding of what is needed.

    Tom

  • NickLawrence
    NickLawrence
    69 Posts

    Re: OmniFind - SQL Error -418 in embedded SQL

    ‏2014-02-21T15:00:22Z  

    Thanks Tom,

    this statement works, but I need the dynamic SQL making flexible search statements. In my application
    I made it like this:

    select custkey from customers where comments like ? and comments like ?

    EXEC SQL OPEN C1 using :search_arg1, :search_ag2;

    with OmniFind it looks like this

    select custkey from customers contains(comments, ? and ?) = 1

    It works with static Statements

    select custkey from customers contains(comments, :search_arg1 and :search_arg2 ) = 1

    but not in the flexible way

     

     

    When a parameter marker is used as an argument on a function call, DB2 needs to be able to determine the data type of the parameter marker in order to perform function resolution.  (A host variable always has an SQL type associated with the data, so no need to tell DB2 what the data-type is - the percompiler handles that).

    Have you tried CONTAINS(comments, CAST(? AS VARCHAR(2000)) = 1 ?

    Typed parameter markers are a promise to DB2 to provide data that can be casted  to the specified type, and not a promise to provide that specific type.  (In other words, the variable provided for the parameter marker doesn't really have to be 2000 bytes in the above example).

    This is documented in the infocenter: http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/index.jsp?topic=%2Fsqlp%2Frbafyuseparameter.htm

     

     

  • RainerRoss
    RainerRoss
    11 Posts

    Re: OmniFind - SQL Error -418 in embedded SQL

    ‏2014-02-21T15:44:12Z  

    When a parameter marker is used as an argument on a function call, DB2 needs to be able to determine the data type of the parameter marker in order to perform function resolution.  (A host variable always has an SQL type associated with the data, so no need to tell DB2 what the data-type is - the percompiler handles that).

    Have you tried CONTAINS(comments, CAST(? AS VARCHAR(2000)) = 1 ?

    Typed parameter markers are a promise to DB2 to provide data that can be casted  to the specified type, and not a promise to provide that specific type.  (In other words, the variable provided for the parameter marker doesn't really have to be 2000 bytes in the above example).

    This is documented in the infocenter: http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/index.jsp?topic=%2Fsqlp%2Frbafyuseparameter.htm

     

     

    I've tried this and error -104 illegal token appears

    Updated on 2014-02-21T15:52:57Z at 2014-02-21T15:52:57Z by RainerRoss
  • RainerRoss
    RainerRoss
    11 Posts

    Re: OmniFind - SQL Error -418 in embedded SQL

    ‏2014-02-21T15:51:11Z  
    • tomliotta
    • ‏2014-02-21T13:55:53Z

    I'm not clear on how it is any less flexible. Can you give an example of what you can do one way but not the other? Is it a matter of needing multiple functions at different times? Perhaps additional suggestions can be made with better understanding of what is needed.

    Tom

    in my application the number of searcharguments is flexible. It can be that one or twenty searcharguments are entered. I do this with the using of SQL descriptors http://www.ibm.com/developerworks/data/library/techarticle/dm-0811forstie/index.html

     

    Updated on 2014-02-21T15:51:40Z at 2014-02-21T15:51:40Z by RainerRoss
  • NickLawrence
    NickLawrence
    69 Posts

    Re: OmniFind - SQL Error -418 in embedded SQL

    ‏2014-02-23T23:37:58Z  

    I've tried this and error -104 illegal token appears

    We would need to see the SQL statement you are attempting to run to be certain, but the error message implies that you have a syntax error in the SQL statement.

  • RainerRoss
    RainerRoss
    11 Posts

    Re: OmniFind - SQL Error -418 in embedded SQL

    ‏2014-02-25T11:48:46Z  

    We would need to see the SQL statement you are attempting to run to be certain, but the error message implies that you have a syntax error in the SQL statement.

    the attached programm contains the LIKE and the CONTAINS statement

    running with LIKE: comment the line 107 and 108

    running with CONTAINS: comment the line 94, 102, 103

    if you run the programm with CONTAINS statement after the 'exec sql prepare sqlcmd' error -199 illegal use of keyword appears

    Attachments

    Updated on 2014-02-25T12:30:46Z at 2014-02-25T12:30:46Z by RainerRoss
  • NickLawrence
    NickLawrence
    69 Posts

    Re: OmniFind - SQL Error -418 in embedded SQL

    ‏2014-02-25T16:16:32Z  

    the attached programm contains the LIKE and the CONTAINS statement

    running with LIKE: comment the line 107 and 108

    running with CONTAINS: comment the line 94, 102, 103

    if you run the programm with CONTAINS statement after the 'exec sql prepare sqlcmd' error -199 illegal use of keyword appears

     s#sqlcmd = 'select empid,skill from tsto.employee where contains(skill , ? and ? ) = 1';

    To use a parameter marker on a function call in a dynamic SQL statement, DB2 needs to know the data type of the argument. In addition, the search argument (2nd parameter) of the CONTAINS function must be a character or graphic string data type.

    The CONTAINS should should be something like  CONTAINS(skill, CAST(? AS VARCHAR(500)))

    The host variable for the parameter marker should be supplied at the time the cursor is open. (I don't see that code)

    The type of the host variable must be 'castable' to the type indicated for the parameter marker, but does not have to be exactly the same type...as long as DB2 can perform the conversion.

    Suppose you want to do a search for   'term1 AND term2'  where term1 and term2 are supplied by different parameter markers.  This is a little more complicated, since you now have to build the search expression...but you can still do it with something like.

    CONTAINS(skill, CAST(? AS VARCHAR(2000)) CONCAT ' AND ' CONCAT  CAST(? AS VARCHAR(2000))) = 1

    NOTE:  If you are using DB2 for i 7.1, the above syntax can often be simplified. DB2 for i allows untyped parameter markers when they are used as arguments to the CONCAT operator (DB2 for i will assign a default type in this case) http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/index.jsp?topic=%2Fdb2%2Frbafzpreph2.htm

    The extra support makes SQL statements somewhat easier to code and read.

  • RainerRoss
    RainerRoss
    11 Posts

    Re: OmniFind - SQL Error -418 in embedded SQL

    ‏2014-02-25T17:20:16Z  

     s#sqlcmd = 'select empid,skill from tsto.employee where contains(skill , ? and ? ) = 1';

    To use a parameter marker on a function call in a dynamic SQL statement, DB2 needs to know the data type of the argument. In addition, the search argument (2nd parameter) of the CONTAINS function must be a character or graphic string data type.

    The CONTAINS should should be something like  CONTAINS(skill, CAST(? AS VARCHAR(500)))

    The host variable for the parameter marker should be supplied at the time the cursor is open. (I don't see that code)

    The type of the host variable must be 'castable' to the type indicated for the parameter marker, but does not have to be exactly the same type...as long as DB2 can perform the conversion.

    Suppose you want to do a search for   'term1 AND term2'  where term1 and term2 are supplied by different parameter markers.  This is a little more complicated, since you now have to build the search expression...but you can still do it with something like.

    CONTAINS(skill, CAST(? AS VARCHAR(2000)) CONCAT ' AND ' CONCAT  CAST(? AS VARCHAR(2000))) = 1

    NOTE:  If you are using DB2 for i 7.1, the above syntax can often be simplified. DB2 for i allows untyped parameter markers when they are used as arguments to the CONCAT operator (DB2 for i will assign a default type in this case) http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/index.jsp?topic=%2Fdb2%2Frbafzpreph2.htm

    The extra support makes SQL statements somewhat easier to code and read.

    a lot of thanks, Nick - it works