• Welcome, Guest. Please login.
 
May 11, 2021, 08:47:50 PM

News:

Welcome to the SQLitening support forums!


ZIP CODES

Started by D. Wilson, January 17, 2011, 02:23:57 AM

Previous topic - Next topic

D. Wilson

I am trying to import a zip code database into a SQlite database. I have declared the zipcode field as text. However when I import the records in the database it drops the leading zeros on some zip codes. I was using the slsBuildInsertOrUpdate function to build my sql statement. When I look at the database the field is declared as text.

D. Wilson

Opps I figured it out. I forgot about the forum search feature. I did a search and found the answer.

Bern Ertl

Quote from: D. Wilson on January 17, 2011, 02:23:57 AM... When I look at the database the field is declared as text.

Declarations in the CREATE TABLE statements are not enforced.  Whether you enclose the data in single quotes when inserting/updating is what matters.

Marty Francom

I ran into this problem also.  I assumed that declaring a field as character that SQLite would treat  that field as a character field.  But I found that if the field contents looked like a number then SQLIte would treat the field as a number and drop the leading zeros.   I guess this is a case of SQLite trying to be too helpful. 

I also found that if character fields contained only a   + . ;   character  then the  InsertorUpdate would fail with a  NEAR error.   

I guess when you Insert or Update the database all character fields should have the single quote arround them.  To prevent SQLite from having a problem.

What happens if the user places single quotes inside his character fields.  Will that cause SQLite problems?

Fred Meier

QuoteWhat happens if the user places single quotes inside his character fields.  Will that cause SQLite problems?
Yes, embedded single quotes must be doubled.

Understanding SQLite storage class and column affinity is very helpful to resolve problems.
See: http://www.sqlitening.com/support/index.php?topic=3165.0

Jean-Pierre LEROY

Marty,

I use this function with the INSERT or UPDATE SQL Statements for TEXT fields:


Function SQTo2SQ(ByVal pString As String) As String
    Replace "'" With "''" In pString
    Function = pString
End Function


Hope that helps.
Jean-Pierre