SQL SELECT brain dead

colfrank · Aug 21, 2018

Been away fro VFP for years and of course now I cannot figure out a code segment for a very simple query.

SELECT pilot, flt_time from flights ORDER BY launch TO FILE temp
OK fine, creates a TXT file as wanted.

Want to add:
SELECT pilot, flt_time+" minutes" from flights ORDER BY launch TO FILE temp

Found some samples where this should work.... but it has a syntax error

Another "want" is for the flt_time column to have a fixed position
Running the simple first example there are maybe 40 spaces between columns

The purpose of this venture is to make a two column page that can be inserted into the Body of a email.

Tom Borgmann · Aug 21, 2018

Hi colfrank,
you can't concatenate numeric data with a string that way.
You have to make 'minutes' a regular column or transform flt_time into text:

[pre]SELECT pilot, flt_time, "minutes" as fixtxt
FROM flights
ORDER BY launch
TO FILE temp[/pre]

VFP will use the fields length definitions for column width in your target file. So if pilot is defined as C(50) your file will look like this:

[pre]Pilotname 1234 minutes
another pilot 750 minutes
a.s.o.[/pre]

In case it isn't a problem to cut off some characters from the pilot column, you could reduce the width like this:

[pre]SELECT CAST(pilot as C(20)) as pilot, flt_time, "minutes" as fixtxt
FROM flights
ORDER BY launch
TO FILE temp[/pre]

that way your list will look like this:

[pre]Pilotname 1234 minutes
another pilot 750 minutes
a.s.o.[/pre]

Last but not the least, you column flt_time might need some correction too, as time usually is saved as an integer and ints don't display zeros in front. So, in case you want to show zeros to display the time as a 4 digit value, you could do that like this:

[pre]SELECT CAST(pilot as C(20)) as pilot, PADL(flt_time,4,[0]), "minutes" as fixtxt
FROM flights
ORDER BY launch
TO FILE temp[/pre]

which will result into this:

[pre]Pilotname 1234 minutes
another pilot 0750 minutes
a.s.o.[/pre]

HTH

-Tom

https://www.blogger.com/profile/08903165976787522012

vgulielmus · Aug 21, 2018

I've made a small test.
You can see that for 1 to 5 characters, VFP force the length to 21 characters
Then for each additional characters, the length is increased sometimes with 4, other times with 5 characters.
Don't know how to reduce the size, nor to control it.
I tried several commends, like SET MEMOWITH, but none helped.

Code:

CLEAR
SET SAFETY OFF
FOR lni = 1 TO 20
	CLOSE DATABASES all

	SET MEMOWIDTH TO 10
	CREATE CURSOR flights  (pilot C(100), flt_time I, launch T)
	INSERT INTO flights VALUES ('gelu manelu', 10, DATETIME()-100)
	INSERT INTO flights VALUES ('onu', 20, DATETIME()-300)

	SELECT CAST(pilot as c(m.lni)) as pilot FROM flights TO FILE temp
	cAll = FILETOSTR("temp.txt")
	ALINES(aAll,m.cAll,4)

	?m.lni, LEN(m.aAll[2])
NEXT

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

http://praisachion.blogspot.com/

Olaf Doschke · Aug 21, 2018

I would rather right align the number of minutes than pad with leading zeroes, but let me also add that it's not the job of SQL to format text output.

Eevn though it's more code, I'd
1. Query data
2. TextMerge it into something you use in emails

This could also go into an HTML table for more modern mail look and in general is easier to maintain and extend.

Code:

*Create Cursor flights (pilot Char(30), flt_time integer, launch DateTime)
*Insert Into flights Values ("Col Frank",60, DateTime()-3600)
*Insert Into flights Values ("Tom Borgman",90, DateTime()-1800)
*Insert Into flights Values ("Olaf Doschke",120, DateTime()-900)

Select pilot, flt_time From flights Order By launch Into Cursor crsFlightList
Local lcBody, lcHTML, lcFilename
lcBody = '<h1>Flights Table</h1><p><table class="tof"><re><th>Pilot</th><th>Duration</th></tr>'
* (substr(ttoc(dtot(date())+crsGrid.nSeconds,2),4,2))
Scan
Text To lcBody Additive TextMerge Noshow
<tr><td><<Alltrim(pilot)>></td><td><<Substr(Ttoc(Dtot(Date())+flt_time,2),4)>></td></tr>
EndText
EndScan
lcBody=lcBody+'</table>'

Text To lcHTML TextMerge Noshow
<!DOCTYPE html>
<html>
<head>
<style>
table, th, td {
   border: 1px solid black;
}
th, td {
   padding: 4px;
}
.tof {border-collapse: collapse;
}
.tof tr td:nth-child(2) { text-align:right; 
}
</style>
</head>
<body>
<<lcBody>>
</body>
</html>
ENDTEXT

lcFilename=Addbs(GetEnv('TEMP'))+'flights.html'
StrTofile(lcHTML,lcFilename)
Run "&lcFilename"

You can of course store the final HTML where you want and don't need to "Run" it. If you don't know how to create HTML mail, please ask - start a new question for that, as it's related, but a topic of its own.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

Mike Lewis · Aug 21, 2018

I tried several commends, like SET MEMOWITH, but none helped.

SET MEMOWIDTH will have no effect here. It only affects the output displayed on the screen (or to a printer) by commands such as [tt]?[/tt] and LIST. And it only afects fields wider than 254 characters.

Keep in mind too that when you send the output of SELECT to a file, you get two extra spaces inserted at the start of each line. A more accurate way of testing this code would be to send the output to a cursor.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

Olaf Doschke · Aug 21, 2018

As I see the discussion about extra spaces in text output of queries, even in case you don't want to create HTML I'd look into EXPORT or COPY TO with TYPE SDF

Code:

Create Cursor flights (pilot Char(30), flt_time integer, launch DateTime)
Insert Into flights Values ("Col Frank",60, DateTime()-3600)
Insert Into flights Values ("Tom Borgman",90, DateTime()-1800)
Insert Into flights Values ("Olaf Doschke",120, DateTime()-900)

Select pilot, flt_time From flights Order By launch Into Cursor crsFlightList

Local lcFilename

lcFilename=Addbs(GetEnv('TEMP'))+'flights.txt'
Copy To (lcFilename) Type SDF
Modify File (lcFilename)

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

Olaf Doschke · Aug 21, 2018

SDF outputs integers right aligned into 11 char width, as it theoretically can get to the minus billion range. But you can cast to any width char and get that out, no comma, no tabs. Also no header captions, but that's easy to integrate into the fixed text of your mail body.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

vgulielmus · Aug 21, 2018

Obviously, the best advise is to output the result otherwise, e.g. using Copy To, but I am intrigued by the behavior of SELECT ... TO

I noticed that SET HEADINGS have a significant impact on the output.When SET HEADINGS is OFF, the width starts with 4, and increase with 4 or 5 / character

Code:

CLEAR
SET SAFETY OFF
FOR lni = 1 TO 70
	CLOSE DATABASES all
	CLEAR TYPEAHEAD 

	SET HEADINGS OFF
	CREATE CURSOR flights  (pilot C(100), flt_time I, launch T)
	INSERT INTO flights VALUES ('gelu manelu', 10, DATETIME()-100)
	INSERT INTO flights VALUES ('onu', 20, DATETIME()-300)

	SELECT CAST(pilot as c(m.lni)) as pilot FROM flights TO FILE temp
	cAll = FILETOSTR("temp.txt")
	ALINES(aAll,m.cAll,4)

	??m.lni, LEN(LTRIM(m.aAll[1]))
NEXT

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

http://praisachion.blogspot.com/

Mike Lewis · Aug 21, 2018

Vilhelm-Ion, I've been running your code. I'm not seeing any difference with SET HEADINGS ON | OFF. As far as I know, that setting only affects the output of the TYPE and DIPLAY commands. Similarly, I don't see the need for CLOSE DATABASE or CLEAR TYPEAHEAD.

But, regardless of that, even if you get the number of spaces right, once you have inserted the output into an email body, the columns will look ragged (unless you use a fixed-pitch font, which is ugly). You might be able to avoid the problem by adding a tab character (CHR(9)) to the pilot's name (after trimming). But in my tests, I find that doesn't avoid a slightly ragged look (that might be an issue with my email client).

Perhaps the best option is to create an HTML table, as per Olaf's post.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

colfrank · Aug 21, 2018

thanks for all the help..I now have a properly formatted SDF file (final.txt)

Next obstacle, using CDO2000 as my email package want to display the contents of final.txt in the body of the message.

.cTextBody = ??

I know that I could always just attach final.txt, but to have it part of the body is much more attractive.

Tore Bleken · Aug 21, 2018

Remember one of the golden rules at technical forums: New question = new thread!

Olaf Doschke · Aug 21, 2018

Tore is right, but the short answer is FILETOSTR(lcFilename)

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

vgulielmus · Aug 21, 2018

Mike Lewis, I will try the same prg on another PC.
I noticed some strange behavior.
Version of VFP is 9.0.7423
The two images:

and

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

http://praisachion.blogspot.com/

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

SQL SELECT brain dead

colfrank

Programmer

Tom Borgmann

Programmer

vgulielmus

Programmer

Olaf Doschke

Programmer

Mike Lewis

Programmer

Olaf Doschke

Programmer

Olaf Doschke

Programmer

vgulielmus

Programmer

Mike Lewis

Programmer

colfrank

Programmer

Tore Bleken

Programmer

Olaf Doschke

Programmer

vgulielmus

Programmer

Similar threads

Part and Inventory Search

Sponsor