Re: v13+ SQL access other system

[Douglas Cryer via 4D_Tech]

Chip,

What you are trying to do is all possible and I have done it with many 
different SQL databases over the years with various SQL backends such as DEC 
RDB, Sharebase, Terradata, Oracle, MS SQL, Firebird, MySQL, SQLite and 
PostgreSQL.  

Some of these do not exist anymore the main ones I would expect you would hit 
are: Oracle, MS SQL, MySQL, PostgreSQL

For each there are either general tools or specialist tools which can be used 
from 4D.  My favourite tools are Pluggers options for MySQL, SQLite and 
PostgreSQL

For other databases the base option is to use 4D ODBC Pro apart from Oracle 
where you could use 4D for OCI

I have always liked SQL although each system has it's own variations from the 
standard.

You said you were in the 'can this be done?' phase.  I would say it can be done 
the extent to which it is reasonable depends on your budget and time to learn.  
Sometimes simpler solutions are better.  For instance there are a number of 
tools that will allow you to run a select script which could be written to a 
file and then imported.  That would allow you to separate the SQL scripting 
from the report storage and data processing.

Regards,  Dougie


telekinetix Limited- J. Douglas Cryer
Phone : 01234 761759  Mobile : 07973 675 218
2nd Floor Broadway House, 4-6 The Broadway, Bedford MK40 2TE
Email : jdcr...@telekinetix.com  Web : http://www.telekinetix.com 


 
 



**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: v13+ SQL access other system

[Robert McKeever via 4D_Tech]

Back in 4Dv11 days, I was asked to connect to a SQL database with 4D. With 4D, 
I was able to connect to a test copy of the SQL system. I found and extracted 
the list of the SQL tables. And, for each table, I extracted the field map. I 
then wrote 4D code to do sample data extracts for each table - actually wrote 
4D code to generate 4D code for the extracts. Took 3-4 days.

Figured out what the client actually need, etc. The system is still running.

The code is probably around here somewhere - I’m like a packrat - I never throw 
code away.

> On Oct 8, 2019, at 10:04 PM, Chip Scheide via 4D_Tech <4d_tech@lists.4d.com> 
> wrote:
> 
> My use of SQL is very limited...
> I have been asked to try to gather data from 4 separate commercial databases 
> (assumed to be running some SQL compliant system of unknown origin - i.e. 
> mysql, ms sql server etc) and try to coordinate the data and produce a report 
> summarizing the combined data.
> 
> so.. I have a few questions...
> 
> Given:
> - any version of 4D from v13 -> v17
> - an unknown SQL compliant database
> - a valid username/password
> 
> is it possible/reasonable to be able to:
> - get the table/field 'structure' (schema?) of the SQL system
> - use the table/field info to automagically create a matching 4D structure?
> - extract data (to text, or into a 4D system) from the SQL system
> 
> 
> Thanks
> Chip
> 
> Hell is other people 
> Jean-Paul Sartre
> **
> 4D Internet Users Group (4D iNUG)
> Archive:  http://lists.4d.com/archives.html
> Options: https://lists.4d.com/mailman/options/4d_tech
> Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
> **

_
Bob McKeever  http://www.mswl.com 
McKeever's Software Wizardry
Port Coquitlam, B.C.
bobmckee...@mac.com




**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: v13+ SQL access other system

[Chip Scheide via 4D_Tech]

Thanks Tom!

the end goal is a single report (this would be done in 4D), summarizing 
information across all 4 commercial applications.
What I know about the internal workings of any of the 4 sql programs = 0
From the description - there is no official outline of the report yet 
- each program contains information regarding 'projects'. 
- each program was written/maintained by separate software developers
- each program has part of the data needed for the desired final report 
product

I am currently in the 'can this be done?' phase, so I am just trying to 
get a feel for what is reasonable to be able to do.
However, honestly, even if I can get schema and raw data access - I am 
not sure that coordination of all the data will be practical, let alone 
possible - especially with little or no input from the 4 vendors...

Chip
On Wed, 9 Oct 2019 04:51:41 -0700, Tom Benedict wrote:
> Hi Chip,
> 
> The answer is yes to your three questions, but access to the 
> table/fields schema will need to be granted to the user. Also, “
> automagically’ creating a matching 4D structure will require you 
> write code, so it’s not very ‘magic’. ;)
> 
> Extracting data should be straightforward. It’s just a matter of 
> writing queries (SELECT) and putting the returned arrays into records 
> or write to text files.
> 
> It’s possible to SYNCHRONIZE 4D tables with SQL tables, but I have 
> no experience with that feature.
> 
> Is the goal to generate the reports in 4D? Or in SQL?
> 
> HTH,
> 
> Tom Benedict
> 
>> On Oct 8, 2019, at 22:04, Chip Scheide via 4D_Tech 
>> <4d_tech@lists.4d.com> wrote:
>> 
>> My use of SQL is very limited...
>> I have been asked to try to gather data from 4 separate commercial 
>> databases (assumed to be running some SQL compliant system of 
>> unknown origin - i.e. mysql, ms sql server etc) and try to 
>> coordinate the data and produce a report summarizing the combined 
>> data.
>> 
>> so.. I have a few questions...
>> 
>> Given:
>> - any version of 4D from v13 -> v17
>> - an unknown SQL compliant database
>> - a valid username/password
>> 
>> is it possible/reasonable to be able to:
>> - get the table/field 'structure' (schema?) of the SQL system
>> - use the table/field info to automagically create a matching 4D structure?
>> - extract data (to text, or into a 4D system) from the SQL system
>> 
>> 
>> Thanks
>> Chip
> 
> 
---
Gas is for washing parts
Alcohol is for drinkin'
Nitromethane is for racing 
**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: v13+ SQL access other system

[Tom Benedict via 4D_Tech]

Hi Chip,

The answer is yes to your three questions, but access to the table/fields 
schema will need to be granted to the user. Also, “automagically’ creating a 
matching 4D structure will require you write code, so it’s not very ‘magic’. ;)

Extracting data should be straightforward. It’s just a matter of writing 
queries (SELECT) and putting the returned arrays into records or write to text 
files.

It’s possible to SYNCHRONIZE 4D tables with SQL tables, but I have no 
experience with that feature.

Is the goal to generate the reports in 4D? Or in SQL?

HTH,

Tom Benedict

> On Oct 8, 2019, at 22:04, Chip Scheide via 4D_Tech <4d_tech@lists.4d.com> 
> wrote:
> 
> My use of SQL is very limited...
> I have been asked to try to gather data from 4 separate commercial databases 
> (assumed to be running some SQL compliant system of unknown origin - i.e. 
> mysql, ms sql server etc) and try to coordinate the data and produce a report 
> summarizing the combined data.
> 
> so.. I have a few questions...
> 
> Given:
> - any version of 4D from v13 -> v17
> - an unknown SQL compliant database
> - a valid username/password
> 
> is it possible/reasonable to be able to:
> - get the table/field 'structure' (schema?) of the SQL system
> - use the table/field info to automagically create a matching 4D structure?
> - extract data (to text, or into a 4D system) from the SQL system
> 
> 
> Thanks
> Chip

**
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**