PureBasic Forums - English

http://www.purebasic.com
https://www.purebasic.fr/english/

how to insert many columns in sqlite.

https://www.purebasic.fr/english/viewtopic.php?t=64158

Page 1 of 1

how to insert many columns in sqlite.

Posted: Sun Nov 29, 2015 11:14 am
by hessu

Code: Select all

#Separator = ","
Enumeration
#ruoka_Livsmedelsnamn
#ruoka_Livsmedelsnummer
#ruoka_Energi_kcal 
#ruoka_Energi_kJ
#ruoka_Kolhydrater_g
#ruoka_Fett_g
#ruoka_Protein_g
#ruoka_Fibrer_g
#ruoka_Vatten_g 
#ruoka_Alkohol_g 
#ruoka_Aska_g 
#ruoka_Monosackarider_g 
#ruoka_Disackarider_g 
#ruoka_Sackaros_g 
#ruoka_Fullkorn_totalt_g 
#ruoka_Sockerarter_g
#ruoka_Summa_mattade_fettsyror_g
#ruoka_Fettsyra_4_0_10_0_g 
#ruoka_Fettsyra_12_0_g 
#ruoka_Fettsyra_14_0_g
#ruoka_Fettsyra_16_0_g 
#ruoka_Fettsyra_18_0_g 
#ruoka_Fettsyra_20_0_g
#ruoka_Summa_enkelomattade_fettsyror_g 
#ruoka_Fettsyra_16_1_g 
#ruoka_Fettsyra_18_1_g
#ruoka_Summa_fleromattade_fettsyror_g 
#ruoka_Fettsyra_18_2_g 
#ruoka_Fettsyra_18_3_g
#ruoka_Fettsyra_20_4_g 
#ruoka_EPA_Fettsyra_20_5_g
#ruoka_DPA_Fettsyra_22_5_g
#ruoka_DHA_Fettsyra_22_6_g
#ruoka_Kolesterol_mg
#ruoka_Retinol_ug
#ruoka_Vitamin_A_ug
#ruoka_hmm_Karoten_ug
#ruoka_Vitamin_D_ug
#ruoka_Vitamin_E_mg
#ruoka_Vitamin_K_ug
#ruoka_Tiamin_mg
#ruoka_Riboflavin_mg 
#ruoka_Vitamin_C_mg
#ruoka_Niacin_mg 
#ruoka_Niacinekvivalenter_mg
#ruoka_Vitamin_B6_mg 
#ruoka_Vitamin_B12_ug 
#ruoka_Folat_ug 
#ruoka_Fosfor_mg
#ruoka_Jod_ug 
#ruoka_Jarn_mg 
#ruoka_Kalcium_mg
#ruoka_Kalium_mg 
#ruoka_Magnesium_mg
#ruoka_Natrium_mg
#ruoka_Salt_g
#ruoka_Selen_ug
#ruoka_Zink_mg
#ruoka_Avfall_skal_etc_prosent 


EndEnumeration

UseSQLiteDatabase() 

Filename$ = OpenFileRequester("Choose a Text File", "", "*.txt|*.txt", 0)
OpenDatabase(1, GetCurrentDirectory() + "ruoka.sqlite", "", "", #PB_Database_SQLite) 

;If Filename$
  File = ReadFile(#PB_Any, Filename$)
  ;If File
  While Not Eof(File)
    
    Line$ = ReadString(File)
  
Livsmedelsnamn$=StringField(Line$, 1, #Separator)
Livsmedelsnummer$=StringField(Line$, 2, #Separator)
Energi_kcal$=StringField(Line$, 3, #Separator)
Energi_kJ$=StringField(Line$, 4, #Separator)
Kolhydrater_g$=StringField(Line$, 5, #Separator)
Fett_g$=StringField(Line$, 6, #Separator)
Protein_g$=StringField(Line$, 7, #Separator)
Fibrer_g$=StringField(Line$, 8, #Separator)
Vatten_g$=StringField(Line$, 9, #Separator)
Alkohol_g$=StringField(Line$, 10, #Separator)
Aska_g$=StringField(Line$, 11, #Separator)
Monosackarider_g$=StringField(Line$, 12, #Separator)
Disackarider_g$=StringField(Line$, 13, #Separator)
Sackaros_g$=StringField(Line$, 14, #Separator)
Fullkorn_totalt_g$=StringField(Line$, 15, #Separator)
Sockerarter_g$=StringField(Line$, 16, #Separator)
Summa_mattade_fettsyror_g$=StringField(Line$, 17, #Separator)
Fettsyra_4_0_10_0_g$=StringField(Line$, 18, #Separator)
Fettsyra_12_0_g$=StringField(Line$, 19, #Separator)
Fettsyra_14_0_g$=StringField(Line$, 20,#Separator)
Fettsyra_16_0_g$=StringField(Line$, 21, #Separator)
Fettsyra_18_0_g$=StringField(Line$, 22, #Separator) 
Fettsyra_20_0_g$=StringField(Line$, 23, #Separator)
Summa_enkelomattade_fettsyror_g$=StringField(Line$, 24, #Separator) 
Fettsyra_16_1_g$=StringField(Line$, 25, #Separator)
Fettsyra_18_1_g$=StringField(Line$, 26, #Separator)
Summa_fleromattade_fettsyror_g$=StringField(Line$, 27, #Separator)
Fettsyra_18_2_g$=StringField(Line$, 28, #Separator)
Fettsyra_18_3_g$=StringField(Line$, 29, #Separator)
Fettsyra_20_4_g$=StringField(Line$, 30, #Separator)
EPA_Fettsyra_20_5_g$=StringField(Line$, 31, #Separator)
DPA_Fettsyra_22_5_g$=StringField(Line$, 32, #Separator)
DHA_Fettsyra_22_6_g$=StringField(Line$, 33, #Separator)
Kolesterol_mg$=StringField(Line$, 34, #Separator)
Retinol_ug$=StringField(Line$, 35, #Separator)
Vitamin_A_ug$=StringField(Line$, 36, #Separator)
hmm_Karoten_ug$=StringField(Line$, 37, #Separator)
Vitamin_D_ug$=StringField(Line$, 38, #Separator)
Vitamin_E_mg$=StringField(Line$, 39, #Separator)
Vitamin_K_ug$=StringField(Line$, 40, #Separator)
Tiamin_mg$=StringField(Line$, 41, #Separator)
Riboflavin_mg$=StringField(Line$, 42, #Separator)
Vitamin_C_mg$=StringField(Line$, 43, #Separator)
Niacin_mg$=StringField(Line$, 44, #Separator)
Niacinekvivalenter_mg$=StringField(Line$, 45, #Separator)
Vitamin_B6_mg$=StringField(Line$, 46, #Separator)
Vitamin_B12_ug$=StringField(Line$, 47, #Separator)
Folat_ug$=StringField(Line$, 48, #Separator)
Fosfor_mg$=StringField(Line$, 49, #Separator)
Jod_ug$=StringField(Line$, 50, #Separator)
Jarn_mg$=StringField(Line$, 51, #Separator)
Kalcium_mg$=StringField(Line$, 52, #Separator)
Kalium_mg$=StringField(Line$, 53, #Separator)
Magnesium_mg$=StringField(Line$, 54, #Separator)
Natrium_mg$=StringField(Line$, 55, #Separator)
Salt_g$=StringField(Line$, 56, #Separator)
Selen_ug$=StringField(Line$, 57, #Separator)
Zink_mg$=StringField(Line$, 58, #Separator)
Avfall_skal_etc_prosent$=StringField(Line$, 59, #Separator)




      
DatabaseUpdate(1, "INSERT INTO ruoka (Livsmedelsnamn,Livsmedelsnummer,Energi_kcal,Energi_kJ,Kolhydrater_g,Fett_g,Protein_g) VALUES ('"+Livsmedelsnamn$+"','"+Livsmedelsnummer$+"','"+Energi_kcal$+"','"+Energi_kJ$+"','"+Kolhydrater_g$+"','"+Fett_g$+"','"+Protein_g$+"' );")
DatabaseUpdate(1, "INSERT INTO ruoka (Fibrer_g,Vatten_g,Alkohol_g,Aska_g,Monosackarider_g) VALUES ,'"+Fibrer_g$+"','"+Vatten_g$+"','"+Alkohol_g$+"','"+Aska_g$+"','"+Monosackarider_g$+"' ) ;")
DatabaseUpdate(1, "INSERT INTO ruoka (Disackarider_g,Sackaros_g,Fullkorn_totalt_g,Sockerarter_g,Summa_mattade_fettsyror_g,Fettsyra_4_0_10_0_g) VALUES ('"+Disackarider_g$+"','"+Sackaros_g$+"','"+Fullkorn_totalt_g$+"','"+Sockerarter_g$+'","'+Summa_mattade_fettsyror_g$+"','"+Fettsyra_4_0_10_0_g$+"' );")
DatabaseUpdate(1, "INSERT INTO ruoka (Fettsyra_12_0_g,Fettsyra_14_0_g,Fettsyra_16_0_g,Fettsyra_18_0_g,Fettsyra_20_0_g,Summa_enkelomattade_fettsyror_g) VALUES ('"+Fettsyra_12_0_g$+"','"+Fettsyra_14_0_g$+"','"+Fettsyra_16_0_g$+"','"+Fettsyra_18_0_g$+"', '"+Fettsyra_20_0_g$+"','"+Summa_enkelomattade_fettsyror_g$+"' );")
DatabaseUpdate(1, "INSERT INTO ruoka (Fettsyra_16_1_g,Fettsyra_18_1_g,Summa_fleromattade_fettsyror_g,Fettsyra_18_2_g,Fettsyra_18_3_g,Fettsyra_20_4_g) VALUES ('"+Fettsyra_16_1_g$+"','"+Fettsyra_18_1_g$+"','"+Summa_fleromattade_fettsyror_g$+"','"+Fettsyra_18_2_g$+"','"+Fettsyra_18_3_g$+"','"+Fettsyra_20_4_g$+"' );")
DatabaseUpdate(1, "INSERT INTO ruoka (EPA_Fettsyra_20_5_g,DPA_Fettsyra_22_5_g) VALUES ('"+EPA_Fettsyra_20_5_g$+"','"+DPA_Fettsyra_22_5_g$+"');")
DatabaseUpdate(1, "INSERT INTO ruoka (DHA_Fettsyra_22_6_g,Kolesterol_mg,Retinol_ug,Vitamin_A_ug,hmm_Karoten_ug,Vitamin_D_ug) VALUES ('"+DHA_Fettsyra_22_6_g$+"','"+Kolesterol_mg$+"','"+Retinol_ug$+"','"+Vitamin_A_ug$+"','"+hmm_Karoten_ug$+"','"+Vitamin_D_ug$+"');")
DatabaseUpdate(1, "INSERT INTO ruoka (Vitamin_E_mg,Vitamin_K_ug,Tiamin_mg,Riboflavin_mg,Vitamin_C_mg,Niacin_mg,Niacinekvivalenter_mg,Vitamin_B6_mg,Vitamin_B12_ug) VALUES ('"+Vitamin_E_mg$+"','"+Vitamin_K_ug$+"','"+Tiamin_mg$+"','"+Riboflavin_mg$+"','"+Vitamin_C_mg$+"','"+Niacin_mg$+"','"+Niacinekvivalenter_mg$+"','"+Vitamin_B6_mg$+"','"+Vitamin_B12_ug$+"');")
DatabaseUpdate(1, "INSERT INTO ruoka (Folat_ug,Fosfor_mg,Jod_ug,Jarn_mg,Kalcium_mg,Kalium_mg,Magnesium_mg,Natrium_mg,Salt_g,Selen_ug,Zink_mg,Avfall_skal_etc_prosent) VALUES ('"+Folat_ug$+"','"+Fosfor_mg$+"','"+Jod_ug$+"','"+Jarn_mg$+"','"+Kalcium_mg$+"','"+Kalium_mg$+"','"+Magnesium_mg$+"','"+Natrium_mg$+"','"+Salt_g$+"','"+Selen_ug$+"','"+Zink_mg$+"','"+Avfall_skal_etc_prosent$+"');")
Sorry, this is swedish language. (food database)
I have several fields. If I run this, next record Starts with (Fibrer_g,Vatten_g,Alkohol_g,Aska_g,Monosackarider_g)
and next (Disackarider_g,Sackaros_g,Fullkorn_totalt_g,Sockerarter_g,Summa_mattade_fettsyror_g,Fettsyra_4_0_10_0_g) and so on.
My questio is: how to link these DatabaseUpdate(1, "INSERT INTO rows so I get whole records. I have no idea. I have found no examples.
I've tried and tried but no success.
please help me.

hessu :cry: :cry: :cry:

Re: how to insert many columns in sqlite.

Posted: Sun Nov 29, 2015 11:28 am
by Keya
hello i cant help you with SQL sorry but it struck me that you were using so many individual string variables for the same data, especially when you already had an Enumeration in place to use them in a string array, but didnt seem to be using them! :)

for example rather than:

Code: Select all

Tiamin_mg$=StringField(Line$, 41, #Separator)
Riboflavin_mg$=StringField(Line$, 42, #Separator)
Vitamin_C_mg$=StringField(Line$, 43, #Separator)... {etc}
You already have #ruoka_Tiamin_mg, #ruoka_Riboflavin_mg, and #ruoka_Vitamin_C_mg in the Enum corresponding to 41, 42, 43 respectively,
so how about:

Code: Select all

Dim Product$(#ruoka_Avfall_skal_etc_prosent+1)  ;last item in the Enum
Product$(#ruoka_Tiamin_mg)=StringField(Line$, #ruoka_Tiamin_mg, #Separator)
Product$(#ruoka_Riboflavin_mg)=StringField(Line$, #ruoka_Riboflavin_mg, #Separator)
Product$(#ruoka_Vitamin_C_mg)=StringField(Line$, #ruoka_Vitamin_C_mg, #Separator)
and then because you can access them by array you no longer need to do them all individually either, so to fill the entire array you can simply:

Code: Select all

For i = #ruoka_Livsmedelsnamn To #ruoka_Avfall_skal_etc_prosent   ;first to last values in the Enum
 Product$(i) =  StringField(Line$, i, #Separator)
Next i

Re: how to insert many columns in sqlite.

Posted: Sun Nov 29, 2015 12:24 pm
by Bisonte
You have to collect all your SQL Statements to one string.

Like this... :

Code: Select all

SQL.s = "INSERT INTO ruoka (Livsmedelsnamn,Livsmedelsnummer,Energi_kcal,Energi_kJ,Kolhydrater_g,Fett_g,Protein_g,"
SQL   + "Fibrer_g,Vatten_g,Alkohol_g,Aska_g,Monosackarider_g,Disackarider_g,Sackaros_g,Fullkorn_totalt_g,"
SQL   + "Sockerarter_g,Summa_mattade_fettsyror_g,Fettsyra_4_0_10_0_g,Fettsyra_12_0_g,Fettsyra_14_0_g,Fettsyra_16_0_g," 
SQL   + "Fettsyra_18_0_g,Fettsyra_20_0_g,Summa_enkelomattade_fettsyror_g,Fettsyra_16_1_g,Fettsyra_18_1_g,"
SQL   + "Summa_fleromattade_fettsyror_g,Fettsyra_18_2_g,Fettsyra_18_3_g,Fettsyra_20_4_g,EPA_Fettsyra_20_5_g,"
SQL   + "DPA_Fettsyra_22_5_g,DHA_Fettsyra_22_6_g,Kolesterol_mg,Retinol_ug,Vitamin_A_ug,hmm_Karoten_ug,Vitamin_D_ug,"
SQL   + "Vitamin_E_mg,Vitamin_K_ug,Tiamin_mg,Riboflavin_mg,Vitamin_C_mg,Niacin_mg,Niacinekvivalenter_mg,Vitamin_B6_mg,"
SQL   + "Vitamin_B12_ug,Folat_ug,Fosfor_mg,Jod_ug,Jarn_mg,Kalcium_mg,Kalium_mg,Magnesium_mg,Natrium_mg,Salt_g,Selen_ug,"
SQL   + "Zink_mg,Avfall_skal_etc_prosent) VALUES ("
SQL + "'"+Livsmedelsnamn$+"','"+Livsmedelsnummer$+"','"+Energi_kcal$+"','"+Energi_kJ$+"','"+Kolhydrater_g$+"','"+Fett_g$+"','"+Protein_g$+"',"
SQL + "'"+Fibrer_g$+"','"+Vatten_g$+"','"+Alkohol_g$+"','"+Aska_g$+"','"+Monosackarider_g$+"',"
SQL + "'"+Disackarider_g$+"','"+Sackaros_g$+"','"+Fullkorn_totalt_g$+"','"+Sockerarter_g$+'","'+Summa_mattade_fettsyror_g$+"','"+Fettsyra_4_0_10_0_g$+"',"
SQL + "'"+Fettsyra_12_0_g$+"','"+Fettsyra_14_0_g$+"','"+Fettsyra_16_0_g$+"','"+Fettsyra_18_0_g$+"', '"+Fettsyra_20_0_g$+"','"+Summa_enkelomattade_fettsyror_g$+"',"
SQL + "'"+Fettsyra_16_1_g$+"','"+Fettsyra_18_1_g$+"','"+Summa_fleromattade_fettsyror_g$+"','"+Fettsyra_18_2_g$+"','"+Fettsyra_18_3_g$+"','"+Fettsyra_20_4_g$+"',"
SQL + "'"+EPA_Fettsyra_20_5_g$+"','"+DPA_Fettsyra_22_5_g$+"',"
SQL + "'"+DHA_Fettsyra_22_6_g$+"','"+Kolesterol_mg$+"','"+Retinol_ug$+"','"+Vitamin_A_ug$+"','"+hmm_Karoten_ug$+"','"+Vitamin_D_ug$+"',"
SQL + "'"+Vitamin_E_mg$+"','"+Vitamin_K_ug$+"','"+Tiamin_mg$+"','"+Riboflavin_mg$+"','"+Vitamin_C_mg$+"','"+Niacin_mg$+"','"+Niacinekvivalenter_mg$+"','"+Vitamin_B6_mg$+"','"+Vitamin_B12_ug$+"',"
SQL + "'"+Folat_ug$+"','"+Fosfor_mg$+"','"+Jod_ug$+"','"+Jarn_mg$+"','"+Kalcium_mg$+"','"+Kalium_mg$+"','"+Magnesium_mg$+"','"+Natrium_mg$+"','"+Salt_g$+"','"+Selen_ug$+"','"+Zink_mg$+"','"+Avfall_skal_etc_prosent$+"')"

DatabaseUpdate(1, SQL)
BTW, this is the wrong forum ... please a mod can move it to the right section ;)

Re: how to insert many columns in sqlite.

Posted: Sun Nov 29, 2015 8:35 pm
by hessu
Hi Bisonte.

Thanks for this, unfortunately it's insert nothing. But I got good advice anyway.

hessu

Now I'll try get this moved to right place.

Re: how to insert many columns in sqlite.

Posted: Sun Nov 29, 2015 10:32 pm
by infratec
Hi,

then there is an error in the SQL$.

Use this:

Code: Select all

If DatabaseUpdate(1, SQL$) = 0
  Debug SQL$
  Debug DatabaseError()
EndIf
Bernd

Re: how to insert many columns in sqlite.(Works now)

Posted: Sun Nov 29, 2015 11:18 pm
by hessu
Yeah. There were errors.

58 values for 59 columns

I have read every thing in code, but it's so difficult see everythig.

Thank you so much.
Maybe I'll get it work.

I found it. There was '",'" this error. While I counted these values I saw it has another color.
All times are UTC+01:00
Page 1 of 1

Powered by phpBB® Forum Software © phpBB Limited