Home > Archive > Clipper > March 2006 > SQL conversion
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
| Robert J. Stuart 2006-03-16, 6:55 pm |
| Let me start by stating I know nothing about SQL files and structure. But my
project now is to poll an SQL server and get the records from it and convert
them into something I can use, like a DBF file. What I eventually need to
do is produce a database that I can link into an HTML page to display some
user information. This data would be static and not changeable by the user.
What I want to accomplish is to poll the SQL server every night, convert the
records, and update my web server with the data.
Writing this app in clipper is not a problem for me once I know how to
convert the SQL files.
Any help?
Bob S.
| |
|
| SQL is a generic term and doesn't necessarily define what database type
you'd be talking to. There is a MS product called SQL Server which is there
offering the large scale Relational Database (RDB) world, is this what you
mean?
If I understand your task, you are going to extract some data from the RDB,
insert the data in Clipper DBFs, and build your HTML pages from the Clipper
DBF?
Do you have a tool to do the extract from the RDB? The actual data query
would be standard SQL language and similar to Clipper-ese. All these large
scale systems have export / save-as features that can be set up to fire
automatically (in most environments) or have a client side tool to do so.
Likely you'd have to export the found data to a flat file, delimited file or
common format (XLS, XML) to be able to suck it into a Clipper table.
Forgive me, it's been years since I actually coded in Clipper. Somebody
must have a 3rd party lib these days to allow an ODBC-like layer to get at
files in this format?
FWIW, if you need help constructing the actual SQL, let me know.
Cheers
Gary
Aside from the fact that Clipper might not be the best tool for this, I
"Robert J. Stuart" <rjstuart@earthlink.net> wrote in message
news:hIfSf.4764$sL2.3707@newsread2.news.atl.earthlink.net...
> Let me start by stating I know nothing about SQL files and structure. But
my
> project now is to poll an SQL server and get the records from it and
convert
> them into something I can use, like a DBF file. What I eventually need to
> do is produce a database that I can link into an HTML page to display some
> user information. This data would be static and not changeable by the
user.
> What I want to accomplish is to poll the SQL server every night, convert
the
> records, and update my web server with the data.
>
> Writing this app in clipper is not a problem for me once I know how to
> convert the SQL files.
>
> Any help?
>
> Bob S.
>
>
| |
| Joe Wright 2006-03-16, 6:55 pm |
| GWood wrote:
> SQL is a generic term and doesn't necessarily define what database type
> you'd be talking to. There is a MS product called SQL Server which is there
> offering the large scale Relational Database (RDB) world, is this what you
> mean?
>
> If I understand your task, you are going to extract some data from the RDB,
> insert the data in Clipper DBFs, and build your HTML pages from the Clipper
> DBF?
>
> Do you have a tool to do the extract from the RDB? The actual data query
> would be standard SQL language and similar to Clipper-ese. All these large
> scale systems have export / save-as features that can be set up to fire
> automatically (in most environments) or have a client side tool to do so.
>
> Likely you'd have to export the found data to a flat file, delimited file or
> common format (XLS, XML) to be able to suck it into a Clipper table.
>
> Forgive me, it's been years since I actually coded in Clipper. Somebody
> must have a 3rd party lib these days to allow an ODBC-like layer to get at
> files in this format?
>
> FWIW, if you need help constructing the actual SQL, let me know.
>
> Cheers
> Gary
>
> Aside from the fact that Clipper might not be the best tool for this, I
> "Robert J. Stuart" <rjstuart@earthlink.net> wrote in message
> news:hIfSf.4764$sL2.3707@newsread2.news.atl.earthlink.net...
> my
> convert
> user.
> the
>
>
As Gary said, SQL is Structured Query Language is that, a language. I
have the task of unloading an Informix table on a Unix server and
putting the data in a .dbf structure on a Windows server for use by
Clipper and FoxPro. I do it this way..
I have SAMBA installed on the Unix server so that I can map certain Unix
directories as Windows shares on our common network.
Informix has a utility called dbaccess which we use to access the
database and its tables. Informix tables are arranged in rows of columns
just like Clipper tables are. The columns have names. How familiar. We
can use dbaccess to 'unload' a table to a pipe ('|') delimited text file
like this. Write a shell script to run on Unix..
dbaccess database << END
unload to table.pip
select
mbr_id,
first,
last
from
members
where
mbr_id > 10 AND mbr_id < 14
END
This produces the text file, table.pip which looks something like..
11|John|Doe|
12|Jane|Doe|
13|Tom|Jones|
On the Windows side I have already table.dbf who's structure is..
MBR_ID C 6
FIRST C 15
LAST C 15
In my case I wrote a program in C that runs on the Windows side and
appends table.pip from the Unix server directly to table.dbf on Windows.
Lacking my C program, you might write Clipper to read table.pip and
write table.txt while replacing the '|' characters with ',' commas
(except for the last or course which is ignored.) The first line of
table.txt becomes..
11,John,Doe
Now having table.txt use Clipper..
use table exclusive
zap
append from table deli
And you're home.
Rinse, repeat as desired.
--
Joe Wright
"Everything should be made as simple as possible, but not simpler."
--- Albert Einstein ---
| |
| E. Fridman 2006-03-16, 6:55 pm |
| Bob,
Most likely you should be able to "link" your SQL database directly to
your web-site bypassing the need to create DBF in-between.
| |
|
| Yes, I should have expanded on this idea as well. Almost every version of
RDB these days will talk XML, and most browsers have a plug-in available
that will also talk XML. The Clipper layer might be familiar to you, but
really just adds a level of complexity to a task that is pretty standard.
"E. Fridman" <pm771.am@gmail.com> wrote in message
news:1142544993.780633.203420@e56g2000cwe.googlegroups.com...
> Bob,
>
> Most likely you should be able to "link" your SQL database directly to
> your web-site bypassing the need to create DBF in-between.
>
| |
| Robert J. Stuart 2006-03-16, 6:55 pm |
| I guess I was a little vague in my first post. Yes the SQL file I am
referring to is an MS SQL server. It is a Windows server 2000 box running
the MS SQL system. I am pretty sure that the main file that is in use is a
..MDF file in a folder on the server called MSSQL.
I do not want to link directly to this SQL file on the SQL server itself.
Now I can open a hole in the firewall and download this file every night
from the SQL machine to the Web server machine. Then link the file to the
HTML page. I was not sure that could be done. I have taken an Access data
file and linked it into a page before. So if I get what you are telling me,
I just need the correct plug-in to do this with this SQL file or should I
say .MDF file. If that is so, this should be easy. I have noticed that I
have to shut down the SQL services to copy or backup this file. So I
suppose I could create a batch file that shuts down the service, then opens
the firewall, copies the file from the machine we call SQL-1 to the machine
we call WEB-1, close the restart the service and close the firewall. Is it
really that simple?
Bob S.
"GWood" <sorry@nothere.com> wrote in message
news:WelSf.178$tT.111@news01.roc.ny...
> Yes, I should have expanded on this idea as well. Almost every version of
> RDB these days will talk XML, and most browsers have a plug-in available
> that will also talk XML. The Clipper layer might be familiar to you, but
> really just adds a level of complexity to a task that is pretty standard.
>
>
> "E. Fridman" <pm771.am@gmail.com> wrote in message
> news:1142544993.780633.203420@e56g2000cwe.googlegroups.com...
>
>
| |
|
|
| Robert J. Stuart 2006-03-17, 6:55 pm |
| OK so let me get some input from you guys. I will be working with 3
separate machines. SQL-1 which is the MS SQL server, Main-1, which is the
main file server used for print server, docs and other file storage, you
know the rest, and WEB-1 which is the web host and FTP machine.
Now currently I have WEB-1 coming in on a separate IP subnet address on the
T1 than the SQL-1 and Main-1 machines. I did this to have a complete
firewall system for the web server from the rest of the network. If I
wanted to do a direct link from the web server to the SQL-1 machine, I would
have to either punch a hole in the firewall or put the WEB-1 machine on the
same IP subnet as the rest of the network.
This database on the SQL is just basically a membership database, and the
data is not really that sensitive. Doing a direct link to the active SQL
file would be pretty hip since the web access would be in real time as to
the changes being made to the database by the girls in the office.
Bob S.
"GWood" <sorry@nothere.com> wrote in message
news:WelSf.178$tT.111@news01.roc.ny...
> Yes, I should have expanded on this idea as well. Almost every version of
> RDB these days will talk XML, and most browsers have a plug-in available
> that will also talk XML. The Clipper layer might be familiar to you, but
> really just adds a level of complexity to a task that is pretty standard.
>
>
> "E. Fridman" <pm771.am@gmail.com> wrote in message
> news:1142544993.780633.203420@e56g2000cwe.googlegroups.com...
>
>
| |
| Dave P 2006-03-20, 6:55 pm |
| lets say you have a domain server
and a machine where you sqlserver is
on or not on the domain machine
you can open a port on the firewal and on your router
say let 192.168.2.2 be visible through your Public Ip/gateway open....then
you can
link to that machine and get the file
or just set up a local webserver in that machine
and ftp the file
dave
"Robert J. Stuart" <rjstuart@earthlink.net> wrote in message
news:UmmSf.4932$sL2.4708@newsread2.news.atl.earthlink.net...
> I guess I was a little vague in my first post. Yes the SQL file I am
> referring to is an MS SQL server. It is a Windows server 2000 box running
> the MS SQL system. I am pretty sure that the main file that is in use is
a
> .MDF file in a folder on the server called MSSQL.
>
> I do not want to link directly to this SQL file on the SQL server itself.
> Now I can open a hole in the firewall and download this file every night
> from the SQL machine to the Web server machine. Then link the file to the
> HTML page. I was not sure that could be done. I have taken an Access
data
> file and linked it into a page before. So if I get what you are telling
me,
> I just need the correct plug-in to do this with this SQL file or should I
> say .MDF file. If that is so, this should be easy. I have noticed that I
> have to shut down the SQL services to copy or backup this file. So I
> suppose I could create a batch file that shuts down the service, then
opens
> the firewall, copies the file from the machine we call SQL-1 to the
machine
> we call WEB-1, close the restart the service and close the firewall. Is
it
> really that simple?
>
> Bob S.
>
>
>
> "GWood" <sorry@nothere.com> wrote in message
> news:WelSf.178$tT.111@news01.roc.ny...
of[color=darkred]
but[color=darkred]
standard.[color=darkred]
>
>
| |
|
| My earlier response was centered on obtaining the details from the SQL
database. Any reason you want to transfer the entire DB file, rather than
submitting a query and having the results output to a format accessible to
your web page?
The environment I work in uses this method extensively, but I haven't tried
to simulate it at a micro level. Our "WEB-1" has DB connectivity and talks
to "SQL-1", asking for and recieving data. We have third party RAD software
that allows us to massage the retrieved data for presentation on the web
page. Is this SQL data access possible in your setup, without having to do
end runs for file access? If so, SQL Server can likely provide XML
formatted data (http://www.perfectxml.com/articles/...ortSQLXML.asp#4)
or another format usable to your page.
"Robert J. Stuart" <rjstuart@earthlink.net> wrote in message
news:kDASf.5840$k75.2605@newsread3.news.atl.earthlink.net...
> OK so let me get some input from you guys. I will be working with 3
> separate machines. SQL-1 which is the MS SQL server, Main-1, which is the
> main file server used for print server, docs and other file storage, you
> know the rest, and WEB-1 which is the web host and FTP machine.
>
> Now currently I have WEB-1 coming in on a separate IP subnet address on
the
> T1 than the SQL-1 and Main-1 machines. I did this to have a complete
> firewall system for the web server from the rest of the network. If I
> wanted to do a direct link from the web server to the SQL-1 machine, I
would
> have to either punch a hole in the firewall or put the WEB-1 machine on
the
> same IP subnet as the rest of the network.
>
> This database on the SQL is just basically a membership database, and the
> data is not really that sensitive. Doing a direct link to the active SQL
> file would be pretty hip since the web access would be in real time as to
> the changes being made to the database by the girls in the office.
>
> Bob S.
>
>
> "GWood" <sorry@nothere.com> wrote in message
> news:WelSf.178$tT.111@news01.roc.ny...
of[color=darkred]
but[color=darkred]
standard.[color=darkred]
>
>
| |
| Robert J. Stuart 2006-03-22, 6:55 pm |
| GWood,
This sounds like the method that might work best for me. Unfortunately, I
am into and area that I am not familiar with. I got to stop taking on these
jobs that are over my head.... I have found a guy here that is more
familiar with this than I am and may just sub the job out to him.
Bob S.
"GWood" <sorry@nothere.com> wrote in message
news:fsfUf.3555$kg.1047@news02.roc.ny...
> My earlier response was centered on obtaining the details from the SQL
> database. Any reason you want to transfer the entire DB file, rather than
> submitting a query and having the results output to a format accessible to
> your web page?
>
> The environment I work in uses this method extensively, but I haven't
tried
> to simulate it at a micro level. Our "WEB-1" has DB connectivity and
talks
> to "SQL-1", asking for and recieving data. We have third party RAD
software
> that allows us to massage the retrieved data for presentation on the web
> page. Is this SQL data access possible in your setup, without having to do
> end runs for file access? If so, SQL Server can likely provide XML
> formatted data (http://www.perfectxml.com/articles/...ortSQLXML.asp#4)
> or another format usable to your page.
>
> "Robert J. Stuart" <rjstuart@earthlink.net> wrote in message
> news:kDASf.5840$k75.2605@newsread3.news.atl.earthlink.net...
the[color=darkred]
> the
> would
> the
the[color=darkred]
SQL[color=darkred]
to[color=darkred]
version[color=darkred]
> of
available[color=darkred]
> but
> standard.
to[color=darkred]
>
>
|
|
|
|
|