Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

D6/Oracle 7.3.4 SELECT Fails with VARCHAR2(2000)

HI there!

Problem is, using TQuery like
qry := TQuery.Create( nil );
qry.DatabaseName := 'ORA';
qry.SQL.Text := 'SELECT TITLE, COMMNT_LNG FROM COMMNTS WHERE KY = ' +
QuotedStr( 'M0014' );
qry.Open;
if qry.RecordCount = 1 then
begin
end;
qry.Close; // and Free ...

works fine if I eXclude COMMNT_LNG from Fields to be returned but
fails if it's done like above.
Fails means RecordCount returns -1 not even zero.

For a different table in this database also having a long field
doesn't fail at all.

Any hints??? Somehow urgent!

TIA, Tim

Comments

  • MasterijnMasterijn Member Posts: 188
    [b][red]This message was edited by Masterijn at 2004-1-22 4:47:58[/red][/b][hr]
    : HI there!
    :
    : Problem is, using TQuery like
    : qry := TQuery.Create( nil );
    : qry.DatabaseName := 'ORA';
    : qry.SQL.Text := 'SELECT TITLE, COMMNT_LNG FROM COMMNTS WHERE KY = ' +
    : QuotedStr( 'M0014' );
    : qry.Open;
    : if qry.RecordCount = 1 then
    : begin
    : end;
    : qry.Close; // and Free ...
    :
    : works fine if I eXclude COMMNT_LNG from Fields to be returned but
    : fails if it's done like above.
    : Fails means RecordCount returns -1 not even zero.
    :
    : For a different table in this database also having a long field
    : doesn't fail at all.
    :
    : Any hints??? Somehow urgent!
    :
    : TIA, Tim
    :
    :
    Hai Tim,
    When testing if there are records found, it's much more safe to do it with
    [code]
    if not (qry.Eof and qry.Bof) then
    do the record handling
    [/code]
    I think the type of cursor/record set is different when selecting long columns. Some type of cursors don't support the RecordCount property, in that case -1 is returned.
    One tip: its a good idea to use parameters when doing dynamic sql.




  • bockhopbockhop Member Posts: 6
    : :
    : : Problem is, using TQuery like
    : : qry := TQuery.Create( nil );
    : : qry.DatabaseName := 'ORA';
    : : qry.SQL.Text := 'SELECT TITLE, COMMNT_LNG FROM COMMNTS WHERE KY = ' +
    : : QuotedStr( 'M0014' );
    : : qry.Open;
    : : if qry.RecordCount = 1 then
    : : begin
    : : end;
    : : qry.Close; // and Free ...
    : :
    : : works fine if I eXclude COMMNT_LNG from Fields to be returned but
    : : fails if it's done like above.
    : : Fails means RecordCount returns -1 not even zero.
    : :
    : : For a different table in this database also having a long field
    : : doesn't fail at all.
    : :
    : : Any hints??? Somehow urgent!
    : :
    : : TIA, Tim
    : :
    : :
    : Hai Tim,
    : When testing if there are records found, it's much more safe to do it with
    : [code]
    : if not (qry.Eof and qry.Bof) then
    : do the record handling
    : [/code]
    : I think the type of cursor/record set is different when selecting long columns. Some type of cursors don't support the RecordCount property, in that case -1 is returned.
    : One tip: its a good idea to use parameters when doing dynamic sql.
    :
    Anyway the SELECT doesn't return lines, for the above mentioned table
    but it does for a different table.

    If I use SQL-Plus the query returns the requested record!

    Any further ideas,

    thx, Tim
  • MasterijnMasterijn Member Posts: 188
    : : :
    : : : Problem is, using TQuery like
    : : : qry := TQuery.Create( nil );
    : : : qry.DatabaseName := 'ORA';
    : : : qry.SQL.Text := 'SELECT TITLE, COMMNT_LNG FROM COMMNTS WHERE KY = ' +
    : : : QuotedStr( 'M0014' );
    : : : qry.Open;
    : : : if qry.RecordCount = 1 then
    : : : begin
    : : : end;
    : : : qry.Close; // and Free ...
    : : :
    : : : works fine if I eXclude COMMNT_LNG from Fields to be returned but
    : : : fails if it's done like above.
    : : : Fails means RecordCount returns -1 not even zero.
    : : :
    : : : For a different table in this database also having a long field
    : : : doesn't fail at all.
    : : :
    : : : Any hints??? Somehow urgent!
    : : :
    : : : TIA, Tim
    : : :
    : : :
    : : Hai Tim,
    : : When testing if there are records found, it's much more safe to do it with
    : : [code]
    : : if not (qry.Eof and qry.Bof) then
    : : do the record handling
    : : [/code]
    : : I think the type of cursor/record set is different when selecting long columns. Some type of cursors don't support the RecordCount property, in that case -1 is returned.
    : : One tip: its a good idea to use parameters when doing dynamic sql.
    : :
    : Anyway the SELECT doesn't return lines, for the above mentioned table
    : but it does for a different table.
    :
    : If I use SQL-Plus the query returns the requested record!
    :
    : Any further ideas,
    :
    : thx, Tim
    :
    This is a good candidate for a stored procedure (a procedure created in the database). Prossibly the fast and nice workaround.
    You can also try a design time query with parameters and persistent fields. The sql should be something like this:
    [code]
    SELECT TITLE
    , COMMNT_LNG
    FROM COMMNTS
    WHERE KY = :KEY
    [/code]
    You can create persistent fields with right clicking the TQuery component, the field editor (popupmenu).
  • bockhopbockhop Member Posts: 6
    Thanks, so far. Using SELECT ..., SUBSTR( COMMNT, 1, 250 ), SUBSTR( COMMNT, 251, 250 ) ... it works, but that's no sulution...
    I just wonder why the same statement on a different table works!

    Bye, Tim
  • MasterijnMasterijn Member Posts: 188
    : Thanks, so far. Using SELECT ..., SUBSTR( COMMNT, 1, 250 ), SUBSTR( COMMNT, 251, 250 ) ... it works, but that's no sulution...
    : I just wonder why the same statement on a different table works!
    :
    : Bye, Tim
    :
    Maybe you can load your string data the blobway. If your long varchar field is of type TMemoField, than you can use LoadFromStream and SaveToStream. In your case create a MemoryStream. Call SaveToStream(MemStream); And something like this:
    [code]
    var
    AStr: string;
    begin
    SetLength(AStr, MemStream.Size);
    MemStream.Read(Pointer(AStr)^, MemStream.Size);
    [/code]
Sign In or Register to comment.