Re: reading longvarchar(484), data always empty in Excel Options

On Oct 25, 8:43 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> On Oct 25, 1:27 am, jodleren <sonn..._at_hot.ee> wrote:
> > On Oct 24, 7:41 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> > > On Oct 23, 11:39 pm, Sonnich Jensen <sonnich.jen..._at_jotautomation.com>
> > > wrote:
>
> > > > I have a problem with reading data from Oracle in Excel. When reading
> > > > a
> > > > longvarchar, I get nothing - it does not read any data out.
> > > > The code (VBA in Excel) below is in use, and when reading a normal
> > > > varchar2 field it
> > > > works. The current is a longvarchar(484), and it is always empty,
> > > > causing EOF to be true - always - trying to to read data before reads
> > > > nothing...
> > > > The SQL works well in PHP, DB Explorer and such.... but M$ does not
> > > > like it,
>
> > > > Help, please...
>
> > > > Set oWS = CreateWorkspace("server", "me", "qwerty", dbUseODBC)
> > > > oWS.DefaultCursorDriver = dbUseODBCCursor
> > > > oWS.LoginTimeout = 280
>
> > > > Set Connection = oWS.OpenConnection("server", dbDriverNoPrompt,
> > > > True, _
> > > > "ODBC;DSN=datastuff;UID=me;PWD=qwerty")
> > > > Connection.QueryTimeout = 480
>
> > > > sSQL = "select something from whatever"
> > > > Set oRec = oConn.OpenRecordset(sSQL, dbOpenDynaset)
>
> > > > If oRec.EOF Then '<- always false, even that SQL returns data
> > > > sResult = ""
>
> > > What, exactly, is a 'longvarchar(484)'? That isn't a valid datatype
> > > in Oracle.
>
> > Well, that is what DB explorer shows me... anyway, some kind of long
> > varchar, which shows as a "MEMO". I dont have that well access to the
> > DB to tell you much more.
> > When reading those long strings I get those problems.
>
> And LONG columns don't play well with some interfaces. Which version
> of Excel are you using? I can easily retrieve LONG data through an
> ODBC connection (using Micro$ofts own driver for Oracle). It may be
> VBA causing your 'problems' with LONG columns, but I'm not that
> familiar with VBA to make such an 'educated guess'.

Excel 2003.
As of VBA, I wrote to the m$.excel newsgroup too, but got absolutely no response.

I used the oracle driver, changed that to M$, and the result is the same.

In general I have not got much response on this, seems like noone knows.

WBR
Sonnich Received on Thu Oct 25 2007 - 23:26:39 CDT