Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]


Groups > comp.lang.clipper.visual-objects > #4897

Re: DBF to SQL code generator

From "James" <james@examples.net>
Newsgroups comp.lang.clipper.visual-objects
Subject Re: DBF to SQL code generator
Date 2013-03-11 14:00 -0400
Organization Aioe.org NNTP Server
Message-ID <khl63u$9b5$1@speranza.aioe.org> (permalink)
References <khdv0c$a0c$1@dont-email.me> <486f9b40-8319-4073-a8a1-a130eb6bccd2@googlegroups.com>

Show all headers | View raw


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 

Back to comp.lang.clipper.visual-objects | Previous | Next — Previous in thread | Find similar | Unroll thread


Thread

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

csiph-web