Help with code

[demopro]

I have some SQL code that I need to put into a dBase IV code and dont know how.

Here is the SQl code:

SELECT jono, SUBSTRING(MRRN, 3,6) as MRRN, MRRN, COST
FROM Comled
WHERE(JONO NOT LIKE SUBSTRING(MRRN, 3,6) and jono <> 'YY1122' and Jono <> 'PPq211'

 

[dbMark]

First, there would be an error becuase you are trying to create duplicate fields, "SUBSTRING(MRRN, 3,6) as MRRN" as well as "MRRN" itself.

Here is the SQL format for dBase of DOS 5.0, I'm not sure how much it changed from the prior version: (Note that only SELECT and FROM are required clauses.)

SELECT ...
 INTO ...
 FROM ...
 WHERE ...
 GROUP BY ...
 HAVING ...
 UNION <one or more SELECT commands>
 ORDER BY ... / FOR UPDAE OF ...
 SAVE TO TEMP ...

SELECT jono, SUBSTR(MRRN, 3,6) as jono_MRRN, MRRN, COST
FROM Comled
WHERE SUBSTR(MRRN, 3,6) <> jono .AND. jono <> 'YY1122' .AND. jono <> 'PPq211'

Or simpler:

SELECT jono, SUBSTR(MRRN, 3,6) as jono_MRRN, MRRN, COST
FROM Comled
WHERE jono_MRRN <> jono .AND. jono <> 'YY1122' .AND. jono <> 'PPq211'

We often use double quotes, but single quotes are okay too. They are equivalent, but since both are allowed, as well as square brackets, they can be used for nested quotes.

 

[dbMark]

Sorry, please ignore that last box/block of code. It won't work. The prior one should work.

 

[1oldfoxman]

Be aware that in dBaseIV you had to issue the command:

SET SQL ON

to enable the SQL mode for executing a query like that. Then SET SQL OFF to turn of that mode. (At least that's what I'm remembering - it HAS been a long, long time.)

 

[demopro]

I read somewhere that I could put in sql commands into a file of its own under the file extension of .prs

Is there any performance difference between these options?

 

[1oldfoxman]

I'm not finding reference to .prs files in my old dBaseIV books, but did see a brief reference on the newer dBase product site, so I'm not sure whether that's the right file extension for dBIV. I'm remembering procedure files as being .prg files the same as any other dbase program, but could be called essentially as a subroutine library with the dBase command SET PROCEDURE TO. Perhaps dbMark who's clearly more up to speed than myself can be enticed to respond as well.

My experience from years ago would tell me that code executed from a procedure file would not execute any faster than that in a main program (.prg) file, it was simply another way of handling frequently re-used code, user defined functions, error handling routines, etc. It didn't necessarily speed things up or slow things down. Testing would be the ultimate means obviously of determing whether there would be advantage to either approach.

 

[dbMark]

I can't remember whether I actually used dBIV. I used dBIII+ in the 1980s and dB5 more recently.

I don't know anything about .prs files. I agree with 1oldfoxman that the SQL itself would run the same speed wherever it is. Speed depends on compilation and file access.

Compilation - I seem to recall, not sure, that dbIII+ did not pre-compile any code. Perhaps dbIV does? dB5 always creates .DBO pre-compilation files that can be optionally compiled into an EXE.

File access - By putting multiple procedures into a PROCEDURE file and then using SET PROCEDURE TO ... the code is pulled into memory and then the programs run faster because you don't wait while the various files are read from the disk drive. Building an EXEcutable file has similar speed benefits.