Accessing Paradox using multiple SQL statements

[newtechy]

I am accessing a Paradox (Version 7) table remotely from a client application. For connectivity to the Paradox database, I've created a system DSN using the Intersolv 3.11 32-bit Paradox File driver. I have no problems accessing the table using a single SQL statement. But when trying to process the Paradox table using consecutive update/select/delete SQL statements containing 'where' clauses, the following error is displayed:

'keyword found more than once: Where'

Is there a way to resolve this?? Do I need a different version of the Intersolv driver that handles multiple SQL statements with 'where' clauses simultaneously??..Please advise

 

[footpad]

newtechy,

WIthout any idea of the tool used to create the client or the queries you're constructing, it's really hard to hazard a guess.

However, it appears as if Intersolv has an update to their ODBC drivers that resolves some issues with WHERE clauses against Paradox tables. (See

http://www.datadirect-technologies.com/download/docs/odbc/readme/fixes.htm

for the details).

You may want to contact them for more information.

Hope this helps...

-- Lance

P.S. Generally, this forum supports Paradox for Windows, the database software provided with Professional editions Corel WordPerfect Office 2002. While a few of us are also skilled in other areas, it's always best to try to post in forums related to the actual tools your using. I'm not sure if tek-tips has an InterSolv area, but you might see if there's one for your specific tool. You might run into someone who's run into the exact same issue.

 

[newtechy]

Footpad:

Thanks for replying back...I've checked the reference you provided, but it's a different scenario....
The client is a program that uses an SQL panel that allows you to (1) enter SQL code, very much like MS Query or (2) enter a call to a stored procedure within a RDBMS database, such as Paradox. The SQL statements I'm using updates a flag on a table, then selects rows on that table by that same flag, and then deletes the rows on that table by yet the same flag. Below is the code I'm using:

Update <table> set flg = 'Y' where flg = 'N'
Select * from <table> where flg = 'Y'
Delete from <table> where flg = 'Y'

If possible, I wouldn't mind calling a procedure from the Paradox database that can perform this same SQL, as long as a result set is returned from the Paradox database. As I said before, when I perform a single &quot;Select * from <table> where flg = 'N'&quot;, rows are being returned to the client program with success. It's only when I add the &quot;Update..where&quot; and &quot;Delete...where&quot; that I'm having a problem. Can you provide a sample Paradox procedure that can perform the Update/Select/Delete for me?? Please advise..

 

[footpad]

newtechy,

Regrettably, the Paradox file format is a local format, much like the xBASE .DBF format. There is no remote server, per se, with triggers, stored procedures, or other tools you may be used to when working with things like Oracle, SQL Server, InterBase, or even MySQL.

I would love to be able to give you a stored proc, but there's no support or mechanism for doing so outside of the client program used to access the data.

You could use something like Paradox for Windows, dBASE for Windows, or even a custom utility written in Delphi, C++ Builder, or even VB for that matter. But, I'm afraid the administration tools provided with the BDE don't include the type of thing you're looking for. They're just not supported.

I'm not sure, but you might check your client tool to see if they provide any properties or the properties of your ODBC 32 Data Source to see if there are some concurrency settings or something like that.

Beyond that, all I can suggest is seeing if there are related issues and or information available from Intersolv's website.

Sorry...

-- Lance

 

[newtechy]

Okay, thanks for the info. But tell me, since my client application can call a stored procedure in SQL Server, is it possible for a Paradox record to be inserted into a SQL Server table via OLE?? If so, can the insert to an SQL Server table happen instantaneuosly when an add/update to a Paradox record occurs by a Paradox front-end application? In other words, if a user of a Paradox front-end application updates/adds a record in a Paradox table, can that same record be inserted into a SQL Server table by way of OLE?? Please advise...

 

[footpad]

newtechy,

It can, but only if you're using a client that supports such messages. You'll have a better idea about the possibility of that than I do, I'm afraid. I do not believe Intersolv's driver supports DDE, OLE, or any form of IPC. (Though you certainly get points for creativity.)

The problem is, of course, that we have no idea which client you're using.

I don't believe you should be having problems running multiple SQL queries in series. I know I've had no troubles doing similar operations from Paradox for Windows or any Delphi clients I've written. Granted, most of my work uses the BDE, however, I can't imagine that Intersolv would release a driver that didn't allow for that possibility.

(Of course, stranger things *have* happened. Are you sure the updated driver wouldn't help? It might be worth sending one of their support folks a quick email.)

At this point, I can only think of checking the configuration of your DSN in the ODBC Adminstrator. Perhaps there's a handle sharing setting of some form? (I don't have the driver installed on my PC, so I don't even have a way of suggesting things to try.)

Alternatively, you might check out InterSolv's documentation. They have a rather large number of PDF's on their web site and one of them may contain the tidbit you're looking for.

Finally, the only other avenue I can think of would be to go over to

http://groups.google.com

and search for InterSolv SQL and the version of your driver. Perhaps someone's previously posted a message to Usenet and gotten the appropriate answer.

I wish I had a more specific suggestion, but I'm afraid I don't.

Sorry...

-- Lance

 

[newtechy]

Lance:

I spent all day looking through the Intersolv SQL forum and found nothing. Unfortunately, I've been unable to go to the Intersolv.com website because of technical difficulties. But thanks for the reference points...

You mentioned that you were able to run multiple SQL statements in series on Windows or some Delphi clients against a Paradox table. Would you have any examples?? It would be interesting to know if the SQL I'm executing is syntactically correct..Please advise..