Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]
Groups > comp.lang.clipper.visual-objects > #4891 > unrolled thread
| Started by | "Jamal" <vodotnet_nospam@yahoo.com> |
|---|---|
| First post | 2013-03-08 19:17 -0500 |
| Last post | 2013-03-11 14:00 -0400 |
| Articles | 5 — 3 participants |
Back to article view | Back to comp.lang.clipper.visual-objects
DBF to SQL code generator "Jamal" <vodotnet_nospam@yahoo.com> - 2013-03-08 19:17 -0500
Re: DBF to SQL code generator "Jamal" <vodotnet_nospam@yahoo.com> - 2013-03-08 22:40 -0500
Re: DBF to SQL code generator "Jamal" <vodotnet_nospam@yahoo.com> - 2013-03-11 10:54 -0400
Re: DBF to SQL code generator Sherlock <sherlock@sherlock.com.au> - 2013-03-09 02:28 -0800
Re: DBF to SQL code generator "James" <james@examples.net> - 2013-03-11 14:00 -0400
| From | "Jamal" <vodotnet_nospam@yahoo.com> |
|---|---|
| Date | 2013-03-08 19:17 -0500 |
| Subject | DBF to SQL code generator |
| Message-ID | <khdv0c$a0c$1@dont-email.me> |
Hi All, Does anyone have VO code (class) to create CREATE TABLE tablename statement and INSERT INTO tablename statement for DBF files? Jamal
[toc] | [next] | [standalone]
| From | "Jamal" <vodotnet_nospam@yahoo.com> |
|---|---|
| Date | 2013-03-08 22:40 -0500 |
| Message-ID | <kheath$nq0$1@dont-email.me> |
| In reply to | #4891 |
Never mind. I am working on it. "Jamal" wrote in message news:khdv0c$a0c$1@dont-email.me... Hi All, Does anyone have VO code (class) to create CREATE TABLE tablename statement and INSERT INTO tablename statement for DBF files? Jamal
[toc] | [prev] | [next] | [standalone]
| From | "Jamal" <vodotnet_nospam@yahoo.com> |
|---|---|
| Date | 2013-03-11 10:54 -0400 |
| Message-ID | <khkr3v$h8n$1@dont-email.me> |
| In reply to | #4892 |
Phil, I see your post in google groups but not under news.eternal-september.org. Anyway, for thanks for code; it helps. I have something similar except the backslash part and will take that into consideration. Jamal "Jamal" wrote in message news:kheath$nq0$1@dont-email.me... Never mind. I am working on it. "Jamal" wrote in message news:khdv0c$a0c$1@dont-email.me... Hi All, Does anyone have VO code (class) to create CREATE TABLE tablename statement and INSERT INTO tablename statement for DBF files? Jamal
[toc] | [prev] | [next] | [standalone]
| From | Sherlock <sherlock@sherlock.com.au> |
|---|---|
| Date | 2013-03-09 02:28 -0800 |
| Message-ID | <486f9b40-8319-4073-a8a1-a130eb6bccd2@googlegroups.com> |
| In reply to | #4891 |
snip[ Does anyone have VO code (class) to create CREATE TABLE tablename statement and INSERT INTO tablename statement for DBF files? ]
// ==================== IMPORT RECEIPTN.DBF to RECEIPTN table SQL ===================
oReceiptn := _RECEIPTN{, DBEXCLUSIVE} // Sherlock live data
cDataTable := "receiptn"
//
IF oReceiptn:Used
oReceiptn:SuspendNotification()
oReceiptn:SetOrder(0) // NATURAL ORDER..
oReceiptn:Gotop()
DO WHILE ! oReceiptn:EOF
cQuery := [INSERT INTO ] + cDataTable + [ ] + oReceiptn:BuildDBF2SQLDataString( oReceiptn:DbStruct, FALSE )
//
oResult := oADODB:Execute( cQuery )
//
oReceiptn:Skip()
ENDDO
oReceiptn:ResetNotification()
oReceiptn:Close()
oReceiptn := NULL_OBJECT
ELSE
ErrorBox{,"Close 'RECEIPTN.DBF' in cmVODBX32... so I can access the data !"}:Show()
ENDIF
METHOD BuildDBF2SQLDataString( aSQLStructure, lIgnoreMemos := FALSE ) CLASS DbServer
LOCAL cSQLString := [) VALUES (], cDataType AS STRING
LOCAL cFieldName , cDBFFields := [(] AS STRING
LOCAL xx AS DWORD
LOCAL aDbstruct := SELF:DbStruct AS ARRAY
LOCAL uValue AS USUAL
FOR xx := 1 UPTO ALen(aDbstruct)
cFieldName := Upper(aDbstruct[xx][DBS_NAME])
//
IF AScan( aSQLStructure, { |a| Upper(a[DBS_NAME]) = cFieldName }) > 0 // Does this field exist in SQL table ?
// ? cFieldName + " Found "
cDataType := aDbstruct[xx][DBS_TYPE] // DBF Datatype = "C", "M", "L", "D", "N"
// cDBFFields += [`] + cFieldName + [`,] // DBF field name [ ie, `AG_AGENT` ]. These are not " but ` characters
cDBFFields += Lower(cFieldName) + [,]
uValue := SELF:FIELDGET( cFieldName ) // DBF field data .. could be STRING, DATE, LOGIC, NUMERIC, ARRAY, MEMO
//
IF cDataType = 'M' // If DBF MEMO type, intercept
IF IsString( uValue ) // Is MEMO data a string
cDataType := 'C' // Change it to "C" .. will be dealt with below
IF lIgnoreMemos
uValue := _CHR(32) // A space is required... do not remove
ENDIF
ELSEIF IsNumeric( uValue ) // Is MEMO data NUMERIC [ what a waste of a memo ! ]
cDataType := "N" // Change it to "N" .. will be dealt with below
ELSEIF IsDate( uValue ) // Is MEMO data a DATE [ what a waste of a memo ! ]
cDataType := 'D' // Change it to "D" .. will be dealt with below
ELSEIF IsLogic( uValue ) // Is MEMO data a LOGIC [ what a waste of a memo ! ]
cDataType := "L" // Change it to "L" .. will be dealt with below
ELSEIF IsArray( uValue ) // Is MEMO data an ARRAY [ what a waste of a memo ! ]
cDataType := "C" // Change it to "C" .. will be dealt with below
uValue := ""
ENDIF
ENDIF
//
DO CASE
CASE cDataType = 'C' // if DBF "C" character, add to string with delimiters
// uValue := StrTran(AllTrim(uValue),['],[''])
//IF Occurs( '\' ,uValue) > 0 // if a BACKSLASH make it DOUBLE BACKSLASH and add "E" escape to new string
IF Occurs( '\' ,uValue) > 0 .OR. Occurs( ["] ,uValue) > 0 // if a BACKSLASH make it DOUBLE BACKSLASH and add "E" escape to new string
uValue := StrTran(AllTrim(uValue),[\],[\\])
//uValue := StrTran(AllTrim(uValue),["], [\"] )
cSQLString += "E"
// PostgreSQL also accepts "escape" string constants, which are an extension to the SQL standard. An escape STRING constant
// is specified by writing the letter E (upper or lower case) just before the opening single quote, e.g. E'foo'.
// (When continuing an escape STRING constant across lines, write E only before the first opening quote.)
// Within an escape STRING, a backslash character (\) begins a C-like backslash escape SEQUENCE, in which the combination OF backslash and
// following character(s) represents a special BYTE value. \b IS a backspace, \f IS a form feed, \n is a newline, \r is a carriage RETURN, \t is a tab.
// Also supported are \digits, where digits represents an octal BYTE value, and \xhexdigits, where hexdigits represents a hexadecimal BYTE value.
// (You are responsible that the BYTE sequences you create are valid characters in the server character set encoding.)
// ANY other character following a backslash IS taken literally. Thus, to include a backslash character,
// write two backslashes (\\). Also, a single quote can be included IN an escape STRING by writing \', in addition to the normal way of ''.
ENDIF
//
cSQLString += ['] + StrTran(AllTrim(uValue),['],['']) + [',]
//
CASE cDataType = 'N' // if DBF "N" numeric, create a string with delimiters
cSQLString += AsString(uValue) + [,]
//
CASE cDataType = 'D' // if DBF "D" date, create an ANSI string date with delimiters
IF uValue = NULL_DATE
cSQLString += [null,]
ELSE
cSQLString += ['] + SELF:dtosSQL(uValue) + [',]
ENDIF
//
CASE cDataType = 'L' // if DBF "L" logic, stringify result as "Y", "N" for SQL
cSQLString += ['] + IIF( uValue, 'Y', 'N' ) + [',]
//
ENDCASE
ENDIF
NEXT
//
cDBFFields += cSQLString + [)] // Cap the last ) to DBF field list ") VALUES ( xxx, yyy, )"
cDBFFields := StrTran( cDBFFields, [,)], [)] ) // Remove the ", )" and convert to ")"
cSQLString := StrTran( cDBFFields, ["], [\"] ) // Apply escape "\" to any " for SQL string for compliance.
// added 3/01/2009 PMG
IF Right( cSQLString,2) = [\']
cSQLString := SubStr(cSQLString, 1, SLen(cSQLString)-2) + [']
ENDIF
// cSQLString := StrTran( cDBFFields, [\'], ['] ) // If a single "\" as last character, then remove it.
//
RETURN cSQLString
METHOD dtosSQL( dDate ) CLASS DbServer
LOCAL cDate AS STRING
IF dDate != NULL_DATE
cDate := Stuff(Stuff(DToS(dDate),5,0,[-]),8,0,[-]) // 20071201 return "2007-12-01"
ELSE
cDate := Space(4) + '-' + Space(2) + '-' + Space(2) // if empty date return " - - "
ENDIF
//
RETURN cDate
[toc] | [prev] | [next] | [standalone]
| From | "James" <james@examples.net> |
|---|---|
| Date | 2013-03-11 14:00 -0400 |
| Message-ID | <khl63u$9b5$1@speranza.aioe.org> |
| In reply to | #4893 |
Phil,
The part dtosSQL() is probably too much.
I simply do the following;
uValue := oMyServer:FIELDGET(aStruct[i, DBS_NAME])
if aStruct[i, DBS_TYPE] = "D"
uValue := "'" + DToS(uValue) + "'"
if Empty(uValue)
uValue := null
endif
endif
Jamal
"Sherlock" wrote in message
news:486f9b40-8319-4073-a8a1-a130eb6bccd2@googlegroups.com...
snip[ Does anyone have VO code (class) to create CREATE TABLE tablename
statement and INSERT INTO tablename statement for DBF files? ]
// ==================== IMPORT RECEIPTN.DBF to RECEIPTN table SQL
===================
oReceiptn := _RECEIPTN{, DBEXCLUSIVE} // Sherlock live
data
cDataTable := "receiptn"
//
IF oReceiptn:Used
oReceiptn:SuspendNotification()
oReceiptn:SetOrder(0) // NATURAL ORDER..
oReceiptn:Gotop()
DO WHILE ! oReceiptn:EOF
cQuery := [INSERT INTO ] + cDataTable + [ ] +
oReceiptn:BuildDBF2SQLDataString( oReceiptn:DbStruct, FALSE )
//
oResult := oADODB:Execute( cQuery )
//
oReceiptn:Skip()
ENDDO
oReceiptn:ResetNotification()
oReceiptn:Close()
oReceiptn := NULL_OBJECT
ELSE
ErrorBox{,"Close 'RECEIPTN.DBF' in cmVODBX32... so I can access the data
!"}:Show()
ENDIF
METHOD BuildDBF2SQLDataString( aSQLStructure, lIgnoreMemos := FALSE ) CLASS
DbServer
LOCAL cSQLString := [) VALUES (], cDataType AS STRING
LOCAL cFieldName , cDBFFields := [(] AS STRING
LOCAL xx AS DWORD
LOCAL aDbstruct := SELF:DbStruct AS ARRAY
LOCAL uValue AS USUAL
FOR xx := 1 UPTO ALen(aDbstruct)
cFieldName := Upper(aDbstruct[xx][DBS_NAME])
//
IF AScan( aSQLStructure, { |a| Upper(a[DBS_NAME]) = cFieldName }) > 0 //
Does this field exist in SQL table ?
// ? cFieldName + " Found "
cDataType := aDbstruct[xx][DBS_TYPE] // DBF Datatype = "C", "M", "L",
"D", "N"
// cDBFFields += [`] + cFieldName + [`,] // DBF field name
[ ie, `AG_AGENT` ]. These are not " but ` characters
cDBFFields += Lower(cFieldName) + [,]
uValue := SELF:FIELDGET( cFieldName ) // DBF field data .. could be
STRING, DATE, LOGIC, NUMERIC, ARRAY, MEMO
//
IF cDataType = 'M' // If DBF MEMO type, intercept
IF IsString( uValue ) // Is MEMO data a string
cDataType := 'C' // Change it to "C" .. will
be dealt with below
IF lIgnoreMemos
uValue := _CHR(32) // A space is required... do not remove
ENDIF
ELSEIF IsNumeric( uValue ) // Is MEMO data NUMERIC [ what
a waste of a memo ! ]
cDataType := "N" // Change it to "N" .. will
be dealt with below
ELSEIF IsDate( uValue ) // Is MEMO data a DATE [
what a waste of a memo ! ]
cDataType := 'D' // Change it to "D" .. will
be dealt with below
ELSEIF IsLogic( uValue ) // Is MEMO data a LOGIC [
what a waste of a memo ! ]
cDataType := "L" // Change it to "L" .. will
be dealt with below
ELSEIF IsArray( uValue ) // Is MEMO data an ARRAY [
what a waste of a memo ! ]
cDataType := "C" // Change it to "C" .. will
be dealt with below
uValue := ""
ENDIF
ENDIF
//
DO CASE
CASE cDataType = 'C' // if DBF "C" character,
add to string with delimiters
// uValue := StrTran(AllTrim(uValue),['],[''])
//IF Occurs( '\' ,uValue) > 0 // if a BACKSLASH make it DOUBLE
BACKSLASH and add "E" escape to new string
IF Occurs( '\' ,uValue) > 0 .OR. Occurs( ["] ,uValue) > 0 // if a
BACKSLASH make it DOUBLE BACKSLASH and add "E" escape to new string
uValue := StrTran(AllTrim(uValue),[\],[\\])
//uValue := StrTran(AllTrim(uValue),["], [\"] )
cSQLString += "E"
// PostgreSQL also accepts "escape" string constants, which are an extension
to the SQL standard. An escape STRING constant
// is specified by writing the letter E (upper or lower case) just before
the opening single quote, e.g. E'foo'.
// (When continuing an escape STRING constant across lines, write E only
before the first opening quote.)
// Within an escape STRING, a backslash character (\) begins a C-like
backslash escape SEQUENCE, in which the combination OF backslash and
// following character(s) represents a special BYTE value. \b IS a
backspace, \f IS a form feed, \n is a newline, \r is a carriage RETURN, \t
is a tab.
// Also supported are \digits, where digits represents an octal BYTE value,
and \xhexdigits, where hexdigits represents a hexadecimal BYTE value.
// (You are responsible that the BYTE sequences you create are valid
characters in the server character set encoding.)
// ANY other character following a backslash IS taken literally. Thus, to
include a backslash character,
// write two backslashes (\\). Also, a single quote can be included IN an
escape STRING by writing \', in addition to the normal way of ''.
ENDIF
//
cSQLString += ['] + StrTran(AllTrim(uValue),['],['']) + [',]
//
CASE cDataType = 'N' // if DBF "N" numeric,
create a string with delimiters
cSQLString += AsString(uValue) + [,]
//
CASE cDataType = 'D' // if DBF "D" date, create
an ANSI string date with delimiters
IF uValue = NULL_DATE
cSQLString += [null,]
ELSE
cSQLString += ['] + SELF:dtosSQL(uValue) + [',]
ENDIF
//
CASE cDataType = 'L' // if DBF "L" logic,
stringify result as "Y", "N" for SQL
cSQLString += ['] + IIF( uValue, 'Y', 'N' ) + [',]
//
ENDCASE
ENDIF
NEXT
//
cDBFFields += cSQLString + [)] // Cap the last ) to
DBF field list ") VALUES ( xxx, yyy, )"
cDBFFields := StrTran( cDBFFields, [,)], [)] ) // Remove the ", )" and
convert to ")"
cSQLString := StrTran( cDBFFields, ["], [\"] ) // Apply escape "\" to
any " for SQL string for compliance.
// added 3/01/2009 PMG
IF Right( cSQLString,2) = [\']
cSQLString := SubStr(cSQLString, 1, SLen(cSQLString)-2) + [']
ENDIF
// cSQLString := StrTran( cDBFFields, [\'], ['] ) // If a single "\" as
last character, then remove it.
//
RETURN cSQLString
METHOD dtosSQL( dDate ) CLASS DbServer
LOCAL cDate AS STRING
IF dDate != NULL_DATE
cDate := Stuff(Stuff(DToS(dDate),5,0,[-]),8,0,[-]) // 20071201
return "2007-12-01"
ELSE
cDate := Space(4) + '-' + Space(2) + '-' + Space(2) // if empty
date return " - - "
ENDIF
//
RETURN cDate
[toc] | [prev] | [standalone]
Back to top | Article view | comp.lang.clipper.visual-objects
csiph-web