ASE, SQL, OLE, ADE

[#1]

My problem:
In AutoCAD 2000 a simple SQL-statement like "select * from mytab" returns
only one sublist with the specifications of the columns but no data rows.
I'm sure, that the table has many rows and with AutoCAD R14 all works fine.

I´m using the dbl-functions from Jon Fleming, version 2.01 (dbl.lsp
23.04.99).

Thanks

[#2]

Wow, that's a weird one. Can you post a small version of your database in
the custmoer-files newsgroup? Exactly how are you connected to the database
(ODBC or what)?

jrf
Member of the Autodesk Discussion Forum Moderator Program

In article <852ao8$9o711@adesknews2.autodesk.com>, Frank Wiesner wrote:
> My problem:
> In AutoCAD 2000 a simple SQL-statement like "select * from mytab" returns
> only one sublist with the specifications of the columns but no data rows.
> I'm sure, that the table has many rows and with AutoCAD R14 all works fine.
>
> I´m using the dbl-functions from Jon Fleming, version 2.01 (dbl.lsp
> 23.04.99).

[#3]

Dear Jon,
I posted the database (a2ktest.mdb, only one table) to customer-files.

I tried connecting with MS JET 3.51 and also with ODBC driver, it doesn´t
matter.

The problem seems to be the asi_fetch function inside of DBLDoSQL. The
returnvalue is always nil, the argument CursorDescriptor seems to be ok.

I tried using asi_fetch with different values for fetch_orientation, but the
returnvalue is always nil.

Thanks
Frank

Jon Fleming schrieb in im Newsbeitrag:
VA.00000b8a.0517ffab@fleming-group.com...
> Wow, that's a weird one. Can you post a small version of your database in
> the custmoer-files newsgroup? Exactly how are you connected to the
database
> (ODBC or what)?
>

[#4]

OK, I can reproduce your results in AutoCAD 2000. I haven't tried it in
AutoCAD 14.

The problem is the memo fields. Apparently AutoCAD 2000 will not retrieve a
row containing a memo field unless the value of that memo field is null (if
there are multiple memo fields, they all must be null). Note that in the
column descriptions you _did_ get back from DBLDoSQL, the field type (group
3) for the last two columns is 0 (meaning "unknown"). If you remove both
memo fields from the table, DBL retrieves the data just fine.

Since SQL doesn't have a memo field or equivalent, some driver in the chain
can't figure out what to do with that data.

I am a little surprised that the Jet driver does the same thing, but I can
see that it does.

I can think of several possible workarounds.

Workaround 1. Rethink your database structure. I don't know what you have
in those memo fields, but they sort of look like data items that are
associated with each row, and you stuffed into a memo field because you
didn't know how many items there might be. Also, I see that some of the
things that look like data items appear in more than one row (for example,
87.3). If that's the case, you should handle them differently. The method of
stuffing them into a memo field can cause lots of problems other than not
being able to retrieve them from AutoCAD.

This looks like a classic "many to many" relationship; each thing in the main
table can be associated with many of those data items, and each of those data
items can be associated with many things in the main table. The way to
handle that situation is:

A. Make sure that the main table contains a unique key for
each row. The key can be a combination of more than one
column, but it's usually best to have a column that's
only for the key.

B. Create a new table that contains the data items and
contains another unique key in each row.

C. Create yet another new table that contains keys from
the main table paired with corresponding keys from the
data item table that are associated with that main table
item. Note that keys from either table may appear multiple
times in this third table.

Then you will be able to retrieve your data and you will have a much more
robust database.

I also see that many columns that are not memo fields contain duplicate data.
This is often not a good thing. Without knowing what you are doing with that
database, I can't be sure; but there's a good chance that those duplicate
entries belong in another table. I suggest that you read "AutoCAD Database
Connectivity", Scott McFarlane, ISBN 0-7668-1640-0 (which includes a good
discussion of database design, although the connecting lines in the figures
are almost too faint to make out). Another good discussion of database
design is in "SQL Clearly Explained", Jan L. Harrington, ISBN 0-12-326426-X
(and I think her explanations of SQL are worth reading too).

Workaround 2. Move the memo fields to another table, with a unique key for
each one, and replace the memo fields in the main table with those key
values. This is kludgy, and will work only if your ATUOCAD application
doesn't need to know the contents of those memo fields. I don't recommend
this workaround.

Workaround 3. Rewrite your application to use ActiveX Data Objects (ADO)
instead of DBL. I can verify that those memo fields can be retrieved using
ADO from LISP. See ADOLISP.ZIP from http://www.cadalog.com. Also, "AutoCAD
Database Connectivity" (see above for the ISBN) covers ADO and using it from
VBA or LISP. Note that the ASI interface used by DBL is likely to disappear
in the next major release of AutoCAD. You may have to port your code to ADO
sometime in the next couple of years anyway; maybe you should do it now.

I STRONGLY suggest you look at your database design; although workarounds
2 or 3 might get you going faster, a well-designed database is going to be
much more useful and robust in the long term.

jrf
Member of the Autodesk Discussion Forum Moderator Program

In article <852ao8$9o711@adesknews2.autodesk.com>, Frank Wiesner wrote:
> My problem:
> In AutoCAD 2000 a simple SQL-statement like "select * from mytab" returns
> only one sublist with the specifications of the columns but no data rows.
> I'm sure, that the table has many rows and with AutoCAD R14 all works fine.
>
> I´m using the dbl-functions from Jon Fleming, version 2.01 (dbl.lsp
> 23.04.99).

[#5]

Dear Jon,
thank you very much !

Frank

[#6]

After reading your answer, i think that I should give you some more
explanation.

The table i use (and you got) is the result of a query to a oracle database.
The oracle database is organized as you suggested. Because there are much
more tables as you could suspect, the SQL-command would get too complicated
to use it in DBL. So I did it with MS-Access and saved it as a table.

The oracle database is given by another CAD-System (Auto-trol Vectorpipe) ,
all geometric data is stored in the database. The memofields are containing
the dimensions.
I use the table only to convert the graphics from the other CAD to AutoCAD
(building 3D-solids with AutoLISP).

I will take a look at ADOLISP.

Frank

[#7]

He is using the latest version of DBL, which works with AutoCAD 2000.
DBL202.ZIP from http://www.cadalog.com.

jrf
Member of the Autodesk Discussion Forum Moderator Program

In article <85kgib$ifo3@adesknews2.autodesk.com>, Leandro Sperandio Rodrigues
wrote:
> I´m starting to use A2K, i´m trying to conect my AutoCAD2000 with Oracle8,
> but my old functions that i´m using in AutoCAD R14 doesn´t work with A2K.
> This functions you have on dbl.lsp can connect on Oracle8 with A2K ???
> How i can get this functions ???

[#8]

Dear Frank Wiesner,

I´m starting to use A2K, i´m trying to conect my AutoCAD2000 with Oracle8,
but my old functions that i´m using in AutoCAD R14 doesn´t work with A2K.
This functions you have on dbl.lsp can connect on Oracle8 with A2K ???
How i can get this functions ???

Thanks,

Leandro S. Rodrigues - leandro@marcopolo.com.br

Frank Wiesner escreveu nas notícias de
mensagem:852ao8$9o711@adesknews2.autodesk.com...
> My problem:
> In AutoCAD 2000 a simple SQL-statement like "select * from mytab" returns
> only one sublist with the specifications of the columns but no data rows.
> I'm sure, that the table has many rows and with AutoCAD R14 all works
fine.
>
> I´m using the dbl-functions from Jon Fleming, version 2.01 (dbl.lsp
> 23.04.99).
>
> Thanks
>
>
>