Err 210 while executing request (Case Update)

[Olive]

Hi

I have just migrate from TD5.2 to TD6.2, I have on problem with generic request
I use one function to do only the same thing : Prepare, Execute one initial request (I call it : IR) returning request to execute (I call it : Rexec), then execute the returned request (Rexec)

This request seems some thing like this :
"SELECT 'Insert into .... values (....)'
FROM MyTable
into :MyIR"
Then I execute
If Not SqlPrepareAndExecute ( hSql, R1)
...
If Not SqlFetchNext( hSql )
...
If Not SqlPrepareAndExecute ( hSql, MyIR )


First case :
my bind variable datatype is String
If result is shortest than 254 cars. It is nok ok, it fetches 2 rows ... empty (see OracleStringR1-nok.log)
If result is longest than 254 cars. It is not ok, it fetch no row. Worse, I never track any SqlError, unless oracle.Log. (see OracleStringR2-nok.log)

Second case
my bind variable datatype is Long String
if the result is shortest than 254 cars. It is ok. It fetch 2 rows (see OracleLongStringR1-ok.log)
if the result is longest than 254 cars. It is not ok. Error 210 into Oracle log, no error into app source (see OracleLongStringR2.log)


I don't know what to do, because, I want keep the genericity of my code
This run in production environment, please, help me quickly

Environment :
- TD 6.2 SP3
- Windows 7
- Oracle 10

Olivier
Best regards

[Jeff Luther]

Olivier -- As a review, let me give you a basic reply first with a little background, then a conclusion:

Code: Select all

> TD requirements for a database character (text or binary) column fetch:
    - if the DB column is *not* a LONG-type char column, then the TD INTO var. type must be String
    - if the DB column *is* a LONG-type char or binary column, then the TD INTO var. type must be Long String

> The reason? It's because TD does a lookup of the INTO var's data type during runtime and if it's a Long String variable, then it uses a certain set of C/API functions to setup and fetch the LONG column's value. If the var. = String type, then it uses different C/API functions.

> It is your responsibility to have a Long String INTO for any LONG-type column (and String for NON-LONG type). If you do not, this is a coding error.

> It does not matter if your LONG DB column has only a 10-char value in it, it still requires a Long String INTO variable.

> It is a not a SQL error if you mismatch the TD var. type with the DB column type, so your Oracle LOG files won't show the reason and no SAM_SqlError is sent.

> TD defines a LONG value as a column length (not data length) which is > 254 chars, as you note.

CONCLUSION: Your 'generic request' function needs to know which type of DB column is being fetched (LONG-type or not). If your code knows this in advance when you call your function , the easiest way is to add a Boolean parameter to your function , like:

Code: Select all

Parameters
    ...
    Boolean: bIsLongType
    ...

and the code in the function's Actions section would include something like this:
If bIsLongType
    ... use the SQL command to fetch into a Long String variable
Else
    ... use the SQL command to fetch into a String variable

[Olive]

Hi Jeff
Thanks for your remind.
I know the difference between long string and string.

The problem is not "to know or not the data type return by the request", because I never use long char or Lob, but only varchar2.
Here is my request :

Code: Select all

         SELECT   distinct
            'CREATE ' ||
            decode   (:cmbTypeSynonymMU7
               ,'PUBLIC', 'PUBLIC SYNONYM '
               ,'PRIVATE', 'SYNONYM ' || fu.user_id || '.'
               ,'DEFINI', decode(   fo.fobj_synonym_type
                     ,'PUBLIC', 'PUBLIC SYNONYM '
                     ,'PRIVATE', 'SYNONYM ' || fu.user_id || '.' )
               ) ||
            decode   (:cmbTypeSynonymMU7
               ,'PUBLIC', fo.object_name
               ,'PRIVATE', fo.object_name
               ,'DEFINI', fo.fobj_synonym_name
               ) ||
            ' FOR ' || fo.object_owner || '.' || fo.object_name
         FROM   fonc_object   fo,
            fonc_user   fu,
            fonction      f,
            module m
         WHERE   fu.user_id = :strwUserMU7
         AND   fo.fonc_num = fu.fonc_num
         AND   fo.tobj_code IN ('TABLE', 'VIEW', 'SEQUENCE', 'PROCEDURE', 'PACKAGE', 'FUNCTION')
         AND   f.fonc_num = fu.fonc_num
         AND   m.modu_num = f.modu_num
         AND   m.appl_num = :nwNumAppliMU7
         AND   NOT EXISTS
               (
               SELECT   'X'
               FROM   sys.dba_synonyms a
               WHERE   decode   (:cmbTypeSynonymMU7
                     ,'PUBLIC', 'PUBLIC '
                     ,'PRIVATE', fu.user_id
                     ,fu.user_id
                     ) = a.owner
               AND   rtrim(fo.fobj_synonym_name) = rtrim(a.synonym_name)
               AND   rtrim(fo.object_owner) = rtrim(a.table_owner)
               AND   rtrim(fo.object_name) = rtrim(a.table_name)
               AND   a.db_link is null
               )
         INTO :strwOrdreSQLMU7
         ORDER BY 1"

See below the datatype of my columns used into the select clause

Code: Select all

cmbTypeSynonymMU7      ComboBox, String:
fu.user_id             VARCHAR2(8)
fo.fobj_synonym_type   VARCHAR2(8)
fo.object_name         VARCHAR2(32)
fo.fobj_synonym_name   VARCHAR2(32)
fo.object_owner        VARCHAR2(8)

see below the select clause, I append on each concatened element, the length max of the data and a sample betwen '|' (using max length)

Code: Select all

'CREATE ' ||                                                       |'CREATE |  (8 chars)
decode   (:cmbTypeSynonymMU7
,'PUBLIC', 'PUBLIC SYNONYM '
,'PRIVATE', 'SYNONYM ' || fu.user_id || '.'
,'DEFINI', decode(   fo.fobj_synonym_type
,'PUBLIC', 'PUBLIC SYNONYM '
,'PRIVATE', 'SYNONYM ' || fu.user_id || '.' )
) ||                                                               |SYNONYM Userxxxx.|    (17 chars max)
decode   (:cmbTypeSynonymMU7
,'PUBLIC', fo.object_name
,'PRIVATE', fo.object_name
,'DEFINI', fo.fobj_synonym_name
) ||                                                               |Objectxxxxxxxxxxxxxxxxxxxxxxxxxx|   (32 chars max)
' FOR ' || fo.object_owner || '.' || fo.object_name                | FOR Userxxxx.Objectxxxxxxxxxxxxxxxxxxxxxxxxxx'|         (47 chars max)

The max length string obtained is this :
'CREATE SYNONYM Userxxxx.Objectxxxxxxxxxxxxxxxxxxxxxxxxxx FOR Userxxxx.Objectxxxxxxxxxxxxxxxxxxxxxxxxxx'
its lenght is 104 char max
Obviously, I use a String into variable
I doesn't work
in a long string, it doesn't work either, unless I force a concatenation whith a long string bind variable (no doubt in this case, I'm sure to use a long string)

One moment, I though it was a Unicode problem. So the max lenght would be 208 , anyway, it is less than 254.

I still do not understand.

Best regards

[Jeff Luther]

I wrote the overview of String vs. Long String for you as well as other users.

Concerning your latest reply, it has a lot more SQL in it than your first msg. did, so I am not sure what to recommend. It first sounded like you had a sometimes-String and sometimes-Long String issue and had it working based on your "First case" and "Second case" examples.

Now you write that "I use a String into variable I[t] doesn't work in a long string, it doesn't work either..." So the situation is now different from your first msg.

If I were testing this and trying to learn what the issues were and how to solve the problem with TD code:
** I would first try using SQLTalk and running the SQL commands there, for example, as a test to see how your SQL commands were handled by SQLTalk and what value(s) was/were returned. This might provide a clue as to how to approach this in TD. What is Oracle actually returning from your SELECT statement?

** I would start with a simple TD test case and learn from that. Your "SELECT distinct 'CREATE ' ..." example has so much in it that it is not a good way to test the problem. You are trying to debug a production SQL command (did this work in TD v5.2 but now does not with TD v6.3???) and if were me I would try to figure this out in in a simple test that I slowly built up as I learned, maybe with a simple test DB table as well. As we say in English, "Begin with baby steps."

** I see your VARCHAR2 declarations in the SELECT, but clearly something else in Oracle is going on to create your problem. An implicit type CAST by Oracle, for example? Again, what is Oracle actually returning from your SELECT statement? SQLTalk might give you the answer.