Re: ODBC error 9900

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Re: ODBC error 9900

Joshua Fletcher
Jeff Edwards wrote:
> it throws an error 9900

Does ODBC GET LAST ERROR report any more information besides the error
number?

http://www.4d.com/docs/CMU/CMU00825.HTM

Kind regards,

Josh Fletcher

--
Josh Fletcher
Technical Support Engineer
4D, Inc.

**********************************************************************
Develop Faster!  With 4D Macro Pack. Free for 4D 2004.
http://www.4d.com/2004/macropack.html

4th Dimension Internet Users Group (4D iNUG)
FAQ:  <http://www.4d.com/support/faqnug.html>
Archive:  <http://dir.gmane.org/gmane.comp.lang.inug-4d.tech>
Unsub:  mailto:  [hidden email]
**********************************************************************


Reply | Threaded
Open this post in threaded view
|

Re: ODBC error 9900

jeffFromOz
Unfortunately not.  I do have an error handler installed . . BTW  . I  
noticed that the documentation is wrong in that the errSQLServer  
parameter is meant to be an integer rather than text.   Nevertheless  
the only populated variable is errCode with 9900.

On 23/08/2006, at 5:00 AM, Josh Fletcher (4D, Inc.) wrote:

>
>> it throws an error 9900
>
> Does ODBC GET LAST ERROR report any more information besides the  
> error number?
>


**********************************************************************
Develop Faster!  With 4D Macro Pack. Free for 4D 2004.
http://www.4d.com/2004/macropack.html

4th Dimension Internet Users Group (4D iNUG)
FAQ:  <http://www.4d.com/support/faqnug.html>
Archive:  <http://dir.gmane.org/gmane.comp.lang.inug-4d.tech>
Unsub:  mailto:  [hidden email]
**********************************************************************


Reply | Threaded
Open this post in threaded view
|

Re: ODBC error 9900

jeffFromOz
Any other takers on this question?

To recap:

When running the following code (in its own process or user/runtime  
process . . it does not matter) . . I get an errorCode of 9900.


$sql:="create table Letter (ClientID integer, DateWritten datetime,  
Category varchar(5), _SYNC_ID varchar(14))"
ODBC EXECUTE($sql)

When looking at the last error with an error handler it shows:

ODBC GET LAST ERROR (errCode; errText; errODBC; errSQLServer)

errCode=9900
errText=""
errODBC=""
errSQLServer=0

and without the on err call it displays the ODBC Error dialog with  
Error - Error #9900, Action = ODBC Error, SQLState - Native Error:0

The Table IS created in SQL Server.

The sql seems fine for SQL Server since when it is pasted and  
executed in another app, it executes without any visible error messages.



**********************************************************************
Develop Faster!  With 4D Macro Pack. Free for 4D 2004.
http://www.4d.com/2004/macropack.html

4th Dimension Internet Users Group (4D iNUG)
FAQ:  <http://www.4d.com/support/faqnug.html>
Archive:  <http://dir.gmane.org/gmane.comp.lang.inug-4d.tech>
Unsub:  mailto:  [hidden email]
**********************************************************************


Reply | Threaded
Open this post in threaded view
|

Re: ODBC error 9900

John Flynn-2
In reply to this post by Joshua Fletcher
Hi Jeff,

> A little problem I hope you can help me with.  I am using 4D2004.3 on
> OSX tiger as a client to MS SQL Server.  The data source is fine and
> connection is up.  However . .
>
> When I try to do a simple statement like this:
>
> ODBC LOGIN
> (×JBE_ODBC_TF_DataSource;×JBE_ODBC_TF_UserName;×JBE_ODBC_TF_Password)
> $sql:="create table Letter (ClientID integer, DateWritten datetime,
> Category varchar(5), _SYNC_ID varchar(14))"
> ODBC EXECUTE($sql)
>
> it throws an error 9900
>
> The strange thing is that I can paste this sql into the SQL Server
> Manager Studio, and it has no problem with it.

I also have seen error 9900. I saw it when I was on v2004.2. BTW I am on
Windows, connecting to a Sybase database. By now I've upgraded to
4Dv2004.4.

My note to myself from a few months back says this: "There's a special
case when code=9900 (in v2004.2) or code=0 (in v2004.3), text=blank,
errODBC=blank, and errSQLServer=0. The built-in ODBC stuff returns this in
a couple of different situations that we know of: When there's two or more
SELECTs in the SQL batch and the first one is setting variables rather
than returning rows, or when the first statement in the SQL batch is a
DELETE that deletes no rows."

I'm not sure what all has changed in this from 2004.2 to 2004.3 to 2004.4.
And of course the differences between Mac vs. Windows, and MS SQL Server
vs. Sybase ASE, could come into play.

The issue about "multiple SELECTs in a batch" is a well-known limitation
of the built-in 4D ODBC commands, and it's been discussed here before, and
also I've sent in a feature request to 4D about it. However, the DELETE
thing was very strange. As I recall, when I passed in a SQL DELETE command
that deleted no rows, that triggered the 9900. I worked around it by
including a dummy command preceding the DELETE in the SQL batch (a command
like "declare @dummy int"). I assume this has got to be another glitch in
the 4D ODBC commands, having to do with result sets, rowcounts, etc., but
I haven't done much else to narrow it down.

Basically, the built-in ODBC commands appear to have been designed with
the assumption that all we'd be doing is simple SELECT statements, one per
SQL command batch. As soon as you deviate in any way (perhaps by issuing a
CREATE TABLE command), all bets are off and funny things might happen to
you. Try preceding your CREATE TABLE command with "declare @dummy int",
which I'm pretty sure MS SQL Server syntax allows. That might be enough to
trick 4D.

- John.


**********************************************************************
Develop Faster!  With 4D Macro Pack. Free for 4D 2004.
http://www.4d.com/2004/macropack.html

4th Dimension Internet Users Group (4D iNUG)
FAQ:  <http://www.4d.com/support/faqnug.html>
Archive:  <http://dir.gmane.org/gmane.comp.lang.inug-4d.tech>
Wiki: <http://4dwiki.org/index.php/Main_Page>
Unsub:  mailto:  [hidden email]
**********************************************************************