Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
[OT] SQL Question
94 views
Skip to first unread message
E. Fridman
Aug 17, 2012, 11:51:36 AM8/17/12
to
This question is about SQL programming itself and NOT about accessing SQL databases from Clipper applications. Clipper is used here to merely describe the task.
In Clipper we can write certain routines that will run on any table without any a priory knowledge about its structure. For example, to convert Clipper table to tab-delimited ASCII file we will use FCOUNT(), FIELDGET(), and VALTYPE() to get all the necessary information.
Can the same be done with SQL? Will the solution be different for each implementation, i.e. MS-SQL, Oracle, etc?
Do you know of any source where this stuff is discussed and explained?
TIA, Eugene
In Clipper we can write certain routines that will run on any table without any a priory knowledge about its structure. For example, to convert Clipper table to tab-delimited ASCII file we will use FCOUNT(), FIELDGET(), and VALTYPE() to get all the necessary information.
Can the same be done with SQL? Will the solution be different for each implementation, i.e. MS-SQL, Oracle, etc?
Do you know of any source where this stuff is discussed and explained?
TIA, Eugene
dlzc
Aug 17, 2012, 12:41:23 PM8/17/12
to
Newsgroups: comp.lang.clipper, comp.lang.xharbour
... don't know if that bit of trickery will work, to post to two newsgroups using the new Google-Groups.
Dear E. Fridman:
On Friday, August 17, 2012 8:51:36 AM UTC-7, E. Fridman wrote:
> This question is about SQL programming itself and
> NOT about accessing SQL databases from Clipper
> applications. Clipper is used here to merely
> describe the task.
>
> In Clipper we can write certain routines that
> will run on any table without any a priory
> knowledge about its structure. For example, to
> convert Clipper table to tab-delimited ASCII file
> we will use FCOUNT(), FIELDGET(), and VALTYPE()
> to get all the necessary information.
>
> Can the same be done with SQL?
In Clipper, the RDD provides this same functionality. But the purpose of a high level interface is to make the logic legible for maintenance programmers.
> Will the solution be different for each
> implementation, i.e. MS-SQL, Oracle, etc?
I would think so.
> Do you know of any source where this stuff is
> discussed and explained?
David A. Smith
... don't know if that bit of trickery will work, to post to two newsgroups using the new Google-Groups.
Dear E. Fridman:
On Friday, August 17, 2012 8:51:36 AM UTC-7, E. Fridman wrote:
> This question is about SQL programming itself and
> NOT about accessing SQL databases from Clipper
> applications. Clipper is used here to merely
> describe the task.
>
> In Clipper we can write certain routines that
> will run on any table without any a priory
> knowledge about its structure. For example, to
> convert Clipper table to tab-delimited ASCII file
> we will use FCOUNT(), FIELDGET(), and VALTYPE()
> to get all the necessary information.
>
> Can the same be done with SQL?
> Will the solution be different for each
> implementation, i.e. MS-SQL, Oracle, etc?
> Do you know of any source where this stuff is
> discussed and explained?
Ella
Aug 17, 2012, 7:09:06 PM8/17/12
to
Hello,
Each SQL engine provides very powerful commands for exporting or importing data.
Practically you compose a single command ( specifying the desired input/output format, encoding etc. ) and you are done.
In case there is need of some very specific data workup, you can write a small stored procedure, which does the custom "data massage", by applying custom default values for columns, or preventively moving duplicate keys in a separate table etc.
In short: you don't need the mentioned xBase functions, which are specific to the navigational logic.
Note: each database ( collection of tables / relations ) has its meta-data stored by the SQL engine, and you can query the data structure details ( if the database administrator gives the programmer's profile read-access to that meta-data...)
Ella
Each SQL engine provides very powerful commands for exporting or importing data.
Practically you compose a single command ( specifying the desired input/output format, encoding etc. ) and you are done.
In case there is need of some very specific data workup, you can write a small stored procedure, which does the custom "data massage", by applying custom default values for columns, or preventively moving duplicate keys in a separate table etc.
In short: you don't need the mentioned xBase functions, which are specific to the navigational logic.
Note: each database ( collection of tables / relations ) has its meta-data stored by the SQL engine, and you can query the data structure details ( if the database administrator gives the programmer's profile read-access to that meta-data...)
Ella
dlzc
Aug 18, 2012, 5:52:50 PM8/18/12
to
Dear Ella:
On Friday, August 17, 2012 4:09:06 PM UTC-7, Ella wrote:
>
> Each SQL engine provides very powerful commands for
> exporting or importing data.
>
> Practically you compose a single command ( specifying
> the desired input/output format, encoding etc. ) and
> you are done.
It seemed to me Eugene was looking for a way to export the data from any SQL table, without knowing anything about the structure beforehand. The problem appears to be the "specification".
Without you personally writing code, are there programmatic ways to do what he described in pure SQL? Just trying to tap your knowledgebase...
David A. Smith
On Friday, August 17, 2012 4:09:06 PM UTC-7, Ella wrote:
>
> Each SQL engine provides very powerful commands for
> exporting or importing data.
>
> Practically you compose a single command ( specifying
> the desired input/output format, encoding etc. ) and
> you are done.
Without you personally writing code, are there programmatic ways to do what he described in pure SQL? Just trying to tap your knowledgebase...
David A. Smith
ella....@xharbour.com
Aug 19, 2012, 6:47:33 AM8/19/12
to
Hello,
:-)))
>
>
>
> David A. Smith
Here are two clashing matters:
1. The management aspect
-------------------------
SQL engines used on sercer-side, in real production represent considerable material investment for the owners - due to the licence fees and / or the administrator / developer personnel salaries payed for maintaining the server, the database, freesource ( if the licence id free ).
Becouse of obvious security reasons only the db admin has access to the database metadata, and those admins in my country do sign up a nondisclosure agreement as part of their job contracts.
SQL engines used on client-side as desktop engines, mostly packaged in the same application installer, which will use them ( the Microsoft online installers do check the presence of the needed version of their SQL Server [Express] engine...for example when you downoad Visual Studio Express ).
In case of these desktop engines, it might be possible, that the software designer accepts interoperability with third-party software, and so guest users might have read permission to the metadata.
2. The technical aspect
--------------------------
Here is a similar thread for theoretical aspects:
http://arstechnica.com/civis/viewtopic.php?f=20&t=681067
As a practical application the xDB sample project present in the xBuilder demo here:
\xHB\Samples\Visual-xHarbour\xDB
has implemented a light-veight SQL interface by using SQLRDD.
xDB is my public domain code contribution, and the code referring table structures can be written in plain Clipper - the screen output is made in a VXH grid, but I think the code is simple enough to be read...
Ella
// displays metadata about schema tables
local cQuery
if cEngine == "MYSQL"
cQuery:="select left(table_name,32), "+;
"left(table_type,32), "+;
"convert(table_rows, char(32)), "+;
"convert(avg_row_length*table_rows, char(32)), "+;
"convert(create_time, char(32)), "+;
"convert(update_time, char(32)), "+;
"left(table_collation,32) "+;
"from information_schema.tables "+;
"where table_schema='"+cSchema+"' and table_name not like 'sr_mgmnt%' "+;
"order by table_name"
elseif cEngine == "FB"
cQuery:="select RDB$RELATION_NAME, "+;
"cast(RDB$FIELD_ID as CHAR(32)), "+;
"RDB$OWNER_NAME, "+;
"RDB$SECURITY_CLASS "+;
"from RDB$RELATIONS "+;
"where RDB$VIEW_BLR IS NULL and RDB$SYSTEM_FLAG=0 and RDB$RELATION_NAME not like 'SR_MGMNT%' "+;
"order by RDB$RELATION_NAME"
elseif cEngine == "MS"
cQuery:="select left(name, 32), "+;
"crdate, "+;
"type_txt = case type when 'U' then 'Table' else 'View' end, "+;
"ltrim(str(schema_ver,12)) "+;
"from sysobjects "+;
"where (type='U' or type='V') and name not like 'SR_MGMNT%' "+;
"order by name"
endif
::Cursor:=nCursorCalc
::Disable()
::Grid:Visible:=.f.
::Caption:="Schema Informations"
#ifdef XDB_SQLRDD
ASize( aCursor, 0)
oSQL:Exec( cQuery, .f., .t., @aCursor, , ,nMaxCursor )
#endif
if len(aCursor) < 1
::MessageBox("There are no tables in the current schema","",MB_OK)
::Enable()
::Cursor:=nCursorNormal
return Self
endif
if cEngine == "MYSQL" // update headers
::GridColumn1:Caption:="Table name"
::GridColumn2:Caption:="Table type"
::GridColumn3:Caption:="Rows"
::GridColumn4:Caption:="Table Length"
::GridColumn5:Caption:="Create time"
::GridColumn6:Caption:="Update time"
::GridColumn7:Caption:="Table collation"
elseif cEngine == "FB"
::GridColumn1:Caption:="Table name"
::GridColumn2:Caption:="Field ID"
::GridColumn3:Caption:="Owner name"
::GridColumn4:Caption:="Security class"
elseif cEngine == "MS"
::GridColumn1:Caption:="Table name"
::GridColumn2:Caption:="Table creation date"
::GridColumn3:Caption:="Table type"
::GridColumn4:Caption:="Schema version"
endif
::Caption:="Schema Informations - Tables"
::myInitGrid(Self) // refresh grid content
::Enable()
::Cursor:=nCursorNormal
>
>
>
> David A. Smith
Here are two clashing matters:
1. The management aspect
-------------------------
SQL engines used on sercer-side, in real production represent considerable material investment for the owners - due to the licence fees and / or the administrator / developer personnel salaries payed for maintaining the server, the database, freesource ( if the licence id free ).
Becouse of obvious security reasons only the db admin has access to the database metadata, and those admins in my country do sign up a nondisclosure agreement as part of their job contracts.
SQL engines used on client-side as desktop engines, mostly packaged in the same application installer, which will use them ( the Microsoft online installers do check the presence of the needed version of their SQL Server [Express] engine...for example when you downoad Visual Studio Express ).
In case of these desktop engines, it might be possible, that the software designer accepts interoperability with third-party software, and so guest users might have read permission to the metadata.
2. The technical aspect
--------------------------
Here is a similar thread for theoretical aspects:
http://arstechnica.com/civis/viewtopic.php?f=20&t=681067
As a practical application the xDB sample project present in the xBuilder demo here:
\xHB\Samples\Visual-xHarbour\xDB
has implemented a light-veight SQL interface by using SQLRDD.
xDB is my public domain code contribution, and the code referring table structures can be written in plain Clipper - the screen output is made in a VXH grid, but I think the code is simple enough to be read...
Ella
// displays metadata about schema tables
local cQuery
if cEngine == "MYSQL"
cQuery:="select left(table_name,32), "+;
"left(table_type,32), "+;
"convert(table_rows, char(32)), "+;
"convert(avg_row_length*table_rows, char(32)), "+;
"convert(create_time, char(32)), "+;
"convert(update_time, char(32)), "+;
"left(table_collation,32) "+;
"from information_schema.tables "+;
"where table_schema='"+cSchema+"' and table_name not like 'sr_mgmnt%' "+;
"order by table_name"
elseif cEngine == "FB"
cQuery:="select RDB$RELATION_NAME, "+;
"cast(RDB$FIELD_ID as CHAR(32)), "+;
"RDB$OWNER_NAME, "+;
"RDB$SECURITY_CLASS "+;
"from RDB$RELATIONS "+;
"where RDB$VIEW_BLR IS NULL and RDB$SYSTEM_FLAG=0 and RDB$RELATION_NAME not like 'SR_MGMNT%' "+;
"order by RDB$RELATION_NAME"
elseif cEngine == "MS"
cQuery:="select left(name, 32), "+;
"crdate, "+;
"type_txt = case type when 'U' then 'Table' else 'View' end, "+;
"ltrim(str(schema_ver,12)) "+;
"from sysobjects "+;
"where (type='U' or type='V') and name not like 'SR_MGMNT%' "+;
"order by name"
endif
::Cursor:=nCursorCalc
::Disable()
::Grid:Visible:=.f.
::Caption:="Schema Informations"
#ifdef XDB_SQLRDD
ASize( aCursor, 0)
oSQL:Exec( cQuery, .f., .t., @aCursor, , ,nMaxCursor )
#endif
if len(aCursor) < 1
::MessageBox("There are no tables in the current schema","",MB_OK)
::Enable()
::Cursor:=nCursorNormal
return Self
endif
if cEngine == "MYSQL" // update headers
::GridColumn1:Caption:="Table name"
::GridColumn2:Caption:="Table type"
::GridColumn3:Caption:="Rows"
::GridColumn4:Caption:="Table Length"
::GridColumn5:Caption:="Create time"
::GridColumn6:Caption:="Update time"
::GridColumn7:Caption:="Table collation"
elseif cEngine == "FB"
::GridColumn1:Caption:="Table name"
::GridColumn2:Caption:="Field ID"
::GridColumn3:Caption:="Owner name"
::GridColumn4:Caption:="Security class"
elseif cEngine == "MS"
::GridColumn1:Caption:="Table name"
::GridColumn2:Caption:="Table creation date"
::GridColumn3:Caption:="Table type"
::GridColumn4:Caption:="Schema version"
endif
::Caption:="Schema Informations - Tables"
::myInitGrid(Self) // refresh grid content
::Enable()
::Cursor:=nCursorNormal
ella....@xharbour.com
Aug 19, 2012, 7:06:53 AM8/19/12
to
Additionally...
> Without you personally writing code, are there programmatic ways to do what he described in pure SQL? Just trying to tap your knowledgebase...
>
>
>
> David A. Smith
SQL has been designed exactly to separate the data from the metadata.
By doing a QUERY the user is pulling the exact AGGREGATE DATA VIEW he/she needs, and at client-side does the formatting he/she desires, by the means of which software desires ( Excel, Crystal report, Linux command line style tools, Clipper, whatever.
Ella
> Without you personally writing code, are there programmatic ways to do what he described in pure SQL? Just trying to tap your knowledgebase...
>
>
>
> David A. Smith
By doing a QUERY the user is pulling the exact AGGREGATE DATA VIEW he/she needs, and at client-side does the formatting he/she desires, by the means of which software desires ( Excel, Crystal report, Linux command line style tools, Clipper, whatever.
Ella
0 new messages