DBase field raising error in Query Wizard

rbuser

Hi,

I'm getting an error when using the Query Wizard after connecting to a DBase
file via a ADO connection. I've tracked it down to one field called 'User'.
If I just use the add all fields option I get the following error dialog.

---------------------------
SQL
---------------------------
Invalid SQL statement.

[Microsoft][ODBC dBase Driver] Syntax error (missing operator) in query
expression 'QSALTRND.[USER] USER_2'
---------------------------
OK
---------------------------

If I remove that field from the selection all is well. Is USER a reserved
word and is it trying to alias the field to USER_2? USER_2 is not a field
within the QSALTRND?

How can I resolve this as very many of my tables contain this field

Thanks again for your continued assistance

Btw,

I've attached a very small copy of this table, you can attach to it simply
with a ADO connection an the following connection string
and putting the attachment in a folder C:\Temp\QSALTRND

Provider=MSDASQL.1;Extended
Properties="DBQ=C:\Temp\QSALTRND;DefaultDir=C:\Temp;Driver={Microsoft dBASE
Driver (*.dbf)};DriverId=277;MaxBufferSize=2048;PageTimeout=5;"


System: D2007, RB 10.9

nicocizik

Hi Chris,

For future reference, please send all attachments to
support@digital-metaphors.com rather than attach them to the newsgroup
message.

ReportBuilder uses the daSQLReservedWords.pas file to determine if a word
can be used inside a query. Since ReportBuilder does not have any specific
logic for dBase databases, it simply uses the default word list located in
the daBuildLocalSQLReservedWordList routine which includes "USER" as a
reserved word. Simply commenting out the "USER" entry will fix the issue.

Moving forward, the addition of dBase to our natively supported database
types will be added to our to-do list for a later release of ReportBuilder.

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com

rbuser

Hi Nico,

Sorry for the late reply. The Reserved word list seems to have done the
trick but I've now found another problem which I could use some assistance
with.

When using the Query Wizard you are given the option to add Calculations. If
I sum a field (In this case the NUM_SEATS field) and then click finish I get
the below error. It seems from the error it is not creating the field alias
correctly as it does not have the 'As' keyword between the sum and the new
field alias.

I'm using DBase, so it there some other unit that I must edit to get this to
work correctly?

Thanks for the assistance,

Chris.

---------------------------
SQL
---------------------------
Invalid SQL statement.

[Microsoft][ODBC dBase Driver] Syntax error (missing operator) in query
expression 'SUM(QSALTRND.NUM_SEATS) SUM_QSALTRND_NUM_SEATS'
---------------------------
OK
---------------------------

nicocizik

Hi Chris,

When using dtOther, the default SQL syntax is used when creating queries.
One way to work around this issue is to use a database type that has similar
syntax to the database you are using (dtMySql, dtMSAccess, for example will
add an "AS" for field aliases). Otherwise you can add "dtOther" into the
conditional statement located in the TdaField.SelectSQLString routine inside
the daSQL.pas file to force it to be added.

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com

rbuser

Thanks Nico,

Once again, you save my bacon

rbuser

Nico,

That tip worked a treat for new reports but I see it won't help for
templates that have SQL already saved in the template with the wrong syntax.

I've already been using the RB demo for reading the templates as text for
removing the BDE specific classes. I could enhance that a bit and search the
text for the queries and modify them, saving them back to the database.

Before I start I'd like your opinion if there is there an easier way of
getting SQL from the termplates than searching the template text directly?
The demo uses a pipeline to read and write the template. Can I access the
query SQL using the pipeline somehow?

Thanks

Chris.

nicocizik

Hi Chris,

The best way to access and manipulate the SQL object once the Template has
been loaded is to use the TdaSQLBuilder class. This gives you direct access
to the SQL object as well as numerous useful utility functions for adding
and changing your queries.

See the TdaSQLBuilder topic in the RBuilder help as well as the following
RBWiki section.

http://www.digital-metaphors.com/rbWiki/DADE/SQLBuilder

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com