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


Groups > comp.lang.clipper.visual-objects > #4891 > unrolled thread

DBF to SQL code generator

Started by"Jamal" <vodotnet_nospam@yahoo.com>
First post2013-03-08 19:17 -0500
Last post2013-03-11 14:00 -0400
Articles 5 — 3 participants

Back to article view | Back to comp.lang.clipper.visual-objects


Contents

  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

#4891 — DBF to SQL code generator

From"Jamal" <vodotnet_nospam@yahoo.com>
Date2013-03-08 19:17 -0500
SubjectDBF 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]


#4892

From"Jamal" <vodotnet_nospam@yahoo.com>
Date2013-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]


#4896

From"Jamal" <vodotnet_nospam@yahoo.com>
Date2013-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]


#4893

FromSherlock <sherlock@sherlock.com.au>
Date2013-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]


#4897

From"James" <james@examples.net>
Date2013-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