SQLite near error

Moderator: Rathinagiri

Post Reply
User avatar
dhaine_adp
Posts: 457
Joined: Wed Aug 06, 2008 12:22 pm
Location: Manila, Philippines
Been thanked: 1 time

SQLite near error

Post by dhaine_adp » Tue Oct 15, 2013 2:36 am

Hello,

SQLite near error due to single quote "'" that contains in text streams (strings). Example:
Danny's Files
Côte d'Ivoire
French words, etc.

I modified the file sql1.prg from <c>:\hmg\samples\sql\sqlite that contains the function c2sql() as follows:

Code: Select all

function C2SQL(Value)

   local cValue := ""
   local ii := 0  //--> added local variable
   local cTmp := ""   //--> added local variable
    local cFormatoDaData := set(4)
   do case
      case Valtype(Value) == "N"
         cValue := AllTrim(Str(Value))

      case Valtype(Value) == "D"
         if !Empty(Value)
            cdate := dtos(value)
            cValue := "'"+substr(cDate,1,4)+"-"+substr(cDate,5,2)+"-"+substr(cDate,7,2)+"'"
         else
            cValue := "''"
         endif

      case Valtype(Value) $ "CM"
         IF Empty( Value)
            cValue="''"
         ELSE
            //-- fixed escaping single quote. Like Pascal Programming add an immediate "'"
            //-- to the single quote that precedes it. I have no choice but to use
            //-- brute force on this rather using rat() and at()
            for ii := 1 to len( value )
               if substr( value, ii, 1 ) == "'"
                  cTmp += substr( value, ii, 1 ) + "'"
                else
                  cTmp += substr( value, ii, 1 )
                endif
            next
            value := cTmp
            cValue := "'" + value+ "'"
            
         ENDIF

      case Valtype(Value) == "L"
         cValue := AllTrim(Str(iif(Value == .F., 0, 1)))

      otherwise
         cValue := "''"       // NOTE: Here we lose values we cannot convert

   endcase
The side effect is that it slows down the whole process most especially in batch mode. I have commented the lines that I've added (//--). If the variable "value" happens to be a memo, definitely it will be WWW (world wide wait) process.

Well it can be done from the calling function but by convention it must be processed from within the c2sql().

FYI.

Regards,

Danny
Regards,

Danny
Manila, Philippines

User avatar
IMATECH
Posts: 167
Joined: Sun May 27, 2012 9:33 pm
Location: Brazil: Goiânia-GO.

Post by IMATECH » Tue Oct 15, 2013 3:18 am

Hi Dani !

You can replace all lines added by a single function ( same performance as ever ):

Code: Select all

   value := "'" + StrTran( value, "'", "''" ) + "'"
I recomend you try hbdc class to work with sqLite ( Harbour Native, jdbc like for sqLite )

and for good practices ( SQL standards )
Database object names ( Column, Table and DB ) have to be enclosed with Brackets ( eg: [cFieldName] )
and char contents with single quotes ( eg: 'cData' )


regards
M., Ronaldo

By: IMATECH

Imation Tecnologia

User avatar
esgici
Posts: 4363
Joined: Wed Jul 30, 2008 9:17 pm
DBs Used: DBF
Location: iskenderun / Turkiye
Has thanked: 246 times
Been thanked: 91 times
Contact:

Post by esgici » Tue Oct 15, 2013 5:33 am

Hi Danny and Ronaldo,

Thanks to hints :)

Viva HMG :D
Viva INTERNATIONAL HMG :D

User avatar
dhaine_adp
Posts: 457
Joined: Wed Aug 06, 2008 12:22 pm
Location: Manila, Philippines
Been thanked: 1 time

Post by dhaine_adp » Tue Oct 15, 2013 11:00 am

Hi Ronaldo,

Thanks for this:
value := "'" + StrTran( value, "'", "''" ) + "'"
Also thank you for suggesting "hbdc class". I think I'm gonna try this.

Regards,

Danny
Regards,

Danny
Manila, Philippines

User avatar
dhaine_adp
Posts: 457
Joined: Wed Aug 06, 2008 12:22 pm
Location: Manila, Philippines
Been thanked: 1 time

Post by dhaine_adp » Tue Oct 15, 2013 12:15 pm

Hi Ronaldo,

There are some still _TODO_ stuffs on the hbdc class. The code itself is neatly written but have you tried this in deployment/production code? Well I'm going to use SQLite database because I don't want some users to mess around with the tables by using MS-Excel to open its contents.

Also is there any tools or library to encrypt the contents of the SQLite database?

Regards,

Danny
Regards,

Danny
Manila, Philippines

User avatar
esgici
Posts: 4363
Joined: Wed Jul 30, 2008 9:17 pm
DBs Used: DBF
Location: iskenderun / Turkiye
Has thanked: 246 times
Been thanked: 91 times
Contact:

Post by esgici » Tue Oct 15, 2013 12:33 pm

Hi Danny
dhaine_adp wrote: Also is there any tools or library to encrypt the contents of the SQLite database?
Please look at :

viewtopic.php?f=35&t=2468&p=21656#p21656" onclick="window.open(this.href);return false;" onclick="window.open(this.href);return false;

and

viewtopic.php?f=9&t=2265&p=19555#p19555" onclick="window.open(this.href);return false;" onclick="window.open(this.href);return false;

May be found more; please use "Search" or "Advanced Search" of forum.

Happy HMG'ing :D
Viva INTERNATIONAL HMG :D

User avatar
IMATECH
Posts: 167
Joined: Sun May 27, 2012 9:33 pm
Location: Brazil: Goiânia-GO.

Post by IMATECH » Tue Oct 15, 2013 4:11 pm

Hi Dani !



1 - hdbc _TODO_ stuffs are reserved for future implement...

But: since like most Relational DB, sqLite provides a unidirectional recordset
using hbbc, all we need to use is:

Connection
executeQuery
updateQuery

and to navigate on the recordset we use :next to retrieve data ( :getString, :getInteger... ) to any object...


2 - Yes, we use it in production :)


3 - SQLCipher is the best option to use with sqLite


4 - Test it and make your choice :)


regards
M., Ronaldo

By: IMATECH

Imation Tecnologia

User avatar
dhaine_adp
Posts: 457
Joined: Wed Aug 06, 2008 12:22 pm
Location: Manila, Philippines
Been thanked: 1 time

Post by dhaine_adp » Tue Oct 15, 2013 5:38 pm

Hi Ronaldo,

Thanks for the tips. It works fine now. It took me 18 hours to migrate and test using hbdc including reading manual from the internet. I borrowed the code from the hmg sample as well. Here take a look at the code. It's my first code using SQL statement.

Esgici:

:lol: Thanks also for the links.

Code: Select all

#include "minigui.ch"
#include "inkey.ch"
#include "common.ch"
#include "dale-aid.ch"

#require "hbsqlit3"


**********************
function sq3Makefile()

   LOCAL cMsg := ""

   LOCAL lRetVal := .f.

   LOCAL cCreateSQL := ""
   LOCAL cIndexSQL  := ""
   LOCAL cQstr      := ""

   LOCAL cID := ""
   LOCAL cNm := ""
   LOCAL cAR := ""
   
   LOCAL cLC, cCC, cST, cNT

   LOCAL oConn, oMeta, oStmt, cSql, oRS
   LOCAL aItems_ := {}

   BEGIN SEQUENCE
   IF .NOT. importOpen()
      BREAK
   ENDIF

   oConn := hdbcSQLTConnection():New( sqDBLang, .T. )
   oMeta := oConn:getMetaData()
   
   cQstr := "SELECT * FROM sqlite_master WHERE type = 'table' ORDER BY name"
   **cQStr := "SELECT name, tbl_name FROM sqlite_master"
   oStmt := oConn:CreateStatement()

   **-- check sql database if tables are needed to be created
   oRS  := oStmt:executeQuery( cQstr )
   oRS:setTableName( "sqlite_master" )
   cNT := ""
   DO WHILE oRs:next()
      cNT := oRs:getString( "tbl_name" )
      IF .NOT. EMPTY( cNT )
         AADD( aItems_, cNT )
      ENDIF
   ENDDO
   IF LEN( aItems_ ) > 0
      lRetVal := .t.
      oRs:Close()
      oStmt:Close()
      oConn:Close()
      BREAK
   ENDIF
   oRs:Close()
   oStmt:Close()
   cNT := ""

   cCreateSQL := "CREATE TABLE country ("
   cCreateSQL += "  CCID char( 2 ) NOT NULL,"         // Country ID - Two-letter code from ISO-3166 
   cCreateSQL += "  CCNM varchar( 75 ) NOT NULL,"     // Name - Country Name                        
   cCreateSQL += "  AREA varchar( 10 ) NOT NULL);"    // Area - World Area                          

   cCreateSQL += "CREATE TABLE LangCodes ("
   cCreateSQL += "  LANGID  char(3) NOT NULL,"       // -- Three-letter code iso-639-3
   cCreateSQL += "  COUNTRY char(2) NOT NULL,"       // -- Main country where used     
   cCreateSQL += "  STATUS  char(1) NOT NULL,"       // -- L(iving), (e)X(tinct)       
   cCreateSQL += "  NAME    varchar(75) NOT NULL);"  // -- Primary name in that country
   
   cCreateSQL += "CREATE TABLE LangIndex ("
   cCreateSQL += "  LANGID   char(3) NOT NULL,"       // -- Three-letter code for language iso-639-3
   cCreateSQL += "  COUNTRY  char(2) NOT NULL,"       // -- Country where this name is used
   cCreateSQL += "  NAMETYPE char(2) NOT NULL,"       // -- L(anguage), LA(lternate), D(ialect), DA(lternate), LP,DP (a pejorative alternate)
   cCreateSQL += "  NAME     varchar(75) NOT NULL)"   // -- The name                       

   cIndexSQL := "CREATE UNIQUE INDEX IdxCountry ON country (CCID,CCNM,AREA);" 
   cIndexSQL += "CREATE INDEX LangC ON LangCodes (LANGID,COUNTRY,NAME);"
   cIndexSQL += "CREATE INDEX LangI ON LangIndex (LANGID,COUNTRY,NAME)"

   oStmt := oConn:createStatement()
   oStmt:executeUpdate( cCreateSQL )
   oStmt := oConn:CreateStatement()
   oStmt:executeUpdate( cIndexSQL )
   oStmt:Close()

   oConn:StartTransaction()
   DBSELECTAREA( "COUNTRY" )
   DBGOTOP()
   WHILE .NOT. EOF()
      cID := ALLTRIM( COUNTRY->CCID )
      cNM := ALLTRIM( COUNTRY->CCNM )
      cAR := ALLTRIM( COUNTRY->AREA )

      cNM := STRTRAN( cNM, "'", "''" )  // stuff extra single quote

      cQstr := "INSERT INTO country (CCID, CCNM, AREA) VALUES ( "
      cQstr +=  c2SQL( cID ) + ","
      cQstr +=  c2SQL( cNM ) + ","
      cQStr +=  c2SQL( cAR ) + ")"
      oStmt := oConn:CreateStatement()
      oStmt:executeUpdate( cQstr )

      DBSKIP()
   END
   oStmt:close()

   DBSELECTAREA( "WORLDLANG" )
   DBGOTOP()
   WHILE .NOT. EOF()
      cLC := ALLTRIM( WORLDLANG->LANGID )
      cCC := ALLTRIM( WORLDLANG->COUNTRY )
      cST := ALLTRIM( WORLDLANG->STATUS )
      cNM := ALLTRIM( WORLDLANG->NAME )
      
      cNM := STRTRAN( cNM, "'", "''" )

      cQstr := "INSERT INTO LangCodes (LANGID,COUNTRY,STATUS,NAME) VALUES ( "
      cQstr +=  c2SQL( cLC ) + ","
      cQstr +=  c2SQL( cCC ) + ","
      cQStr +=  c2SQL( cST ) + ","
      cQStr +=  c2SQL( cNM ) + ")"
      oStmt := oConn:CreateStatement()
      oStmt:executeUpdate( cQstr )

      DBSKIP()
   END
   oStmt:close()

   DBSELECTAREA( "INDEXWORLD" )
   DBGOTOP()
   WHILE .NOT. EOF()
      cLC := ALLTRIM( INDEXWORLD->LANGID )
      cCC := ALLTRIM( INDEXWORLD->COUNTRY )
      cNT := ALLTRIM( INDEXWORLD->NAMETYPE )
      cNM := ALLTRIM( INDEXWORLD->NAME )
      cNM := STRTRAN( cNM, "'", "''" )
      
      cQstr := "INSERT INTO LangIndex (LANGID,COUNTRY,NAMETYPE,NAME) VALUES ( "
      cQstr +=  c2SQL( cLC ) + ","
      cQstr +=  c2SQL( cCC ) + ","
      cQStr +=  c2SQL( cNT ) + ","
      cQStr +=  c2SQL( cNM ) + ")"
      oStmt := oConn:CreateStatement()
      oStmt:executeUpdate( cQstr )
   
      DBSKIP()
   END
   oStmt:close()
   oConn:Commit()
   oConn:Close()

   lRetVal := .t.
   MSGINFO( "Import to SQL is done.", "Finished" )
   END SEQUENCE
   IF .NOT. lRetVal
      MSGSTOP( cMsg, "Error" )
   ENDIF
   DBCLOSEALL()
   RETURN lRetVal




****************************
static function importOpen()

   LOCAL lRetVal := .f.
   
   BEGIN SEQUENCE
   IF .NOT. NETUSE( ( dbCountry ), "COUNTRY", Shared, ReadOnly )
      cBreakMsg := "Cannot open file " + dbLangCC + "."
      BREAK
   ENDIF
   ORDLISTADD( ( ixCountry ) )
   ORDSETFOCUS( "CountryID", ( ixCountry ) )

   IF .NOT. NETUSE( ( dbTLWorldLang ), "WORLDLANG", Shared, ReadOnly )
      cBreakMsg := "Cannot open file " + dbLangCd + "."
      BREAK
   ENDIF
   ORDLISTADD( ( ixTLWorldLang ) )
   ORDSETFOCUS( "CountryID", ( ixTLWorldLang ) )

   IF .NOT. NETUSE( ( dbIndexWorld ), "INDEXWORLD", Shared, ReadOnly )
      cBreakMsg := "Cannot open file " + dbIndexWorld + "." 
      BREAK
   ENDIF
   ORDLISTADD( ( ixIndexWorld ) )
   ORDSETFOCUS( "LangArea", ( ixIndexWorld ) )

   lRetVal := .t.
   END ENSEQUENCE
   RETURN lRetVal



function C2SQL(Value)

   local cValue := ""
    local cFormatoDaData := set(4)
   do case
      case Valtype(Value) == "N"
         cValue := AllTrim(Str(Value))

      case Valtype(Value) == "D"
         if !Empty(Value)
            cdate := dtos(value)
            cValue := "'"+substr(cDate,1,4)+"-"+substr(cDate,5,2)+"-"+substr(cDate,7,2)+"'"
         else
            cValue := "''"
         endif

      case Valtype(Value) $ "CM"
         IF Empty( Value)
            cValue="''"
         ELSE
            cValue := "'" + value+ "'"
         ENDIF

      case Valtype(Value) == "L"
         cValue := AllTrim(Str(iif(Value == .F., 0, 1)))

      otherwise
         cValue := "''"       // NOTE: Here we lose values we cannot convert

   endcase

return cValue
Regards,

Danny
Regards,

Danny
Manila, Philippines

User avatar
IMATECH
Posts: 167
Joined: Sun May 27, 2012 9:33 pm
Location: Brazil: Goiânia-GO.

Post by IMATECH » Tue Oct 15, 2013 6:39 pm

Hi Dani !


Nice job :)

and for good practices ( SQL standards )
Database object names ( Column, Table and DB ) have to be enclosed with Brackets ( eg: [cFieldName] )
and char contents with single quotes ( eg: 'cData' )

cQstr := "SELECT * FROM [sqlite_master] WHERE type = 'table' ORDER BY [sqlite_master].[name]"
cCreateSQL := "CREATE TABLE [country] ...
cQstr := "INSERT INTO [country] (CCID, CCNM, AREA) VALUES ( ... )



Regards
M., Ronaldo

By: IMATECH

Imation Tecnologia

Post Reply