Jump to content

sqlite create table autoincrement problem

faustf
 Share

Recommended Posts

faustf Universalist

faustf

Posted
Posted

hi guy i  have do  a simple   script  for  create a table  but  dont  go :(

tell me  error  autoincrement

_SQLite_Exec($Db, "Create table anagrafica (Ana_ID  INTEGER PRIMARY KEY NOT NULL AUTOINCREMENT," & _
    "Ana_Nome VARCHAR(300)," & _
    "Ana_Indirizzo VARCHAR(300)," & _
    "Ana_Citta VARCHAR(300)," & _
    "Ana_Provincia VARCHAR(300)," & _
    "Ana_Piva VARCHAR(300)," & _
    "Ana_CdF VARCHAR(300)," & _
    "Ana_telefono VARCHAR(300)," & _
    "Ana_mail VARCHAR(300)," & _
    "Ana_cell VARCHAR(300)," & _
    "Ana_fax VARCHAR(300)," & _
    "Ana_Nazione VARCHAR(300)," & _
    "Ana_Nome_Legale VARCHAR(300)," & _
    "Ana_Indirizzo_Legale VARCHAR(300)," & _
    "Ana_Citta_Legale VARCHAR(300)," & _
    "Ana_Provincia_Legale VARCHAR(300)," & _
    "Ana_Nazione_Legale VARCHAR(300)," & _
    "Ana_Ricordare VARCHAR(300)," & _
    "Ana_Mail_legale VARCHAR(300)," & _
    "Ana_Bank VARCHAR(300)," & _
    "Ana_Iban VARCHAR(300)," & _
    "Ana_Swift VARCHAR(300)," & _
    "Ana_Cell_Legale VARCHAR(300)," & _
    "Ana_Cap VARCHAR(300)," & _
    "Ana_Cap_Legale VARCHAR(300)," & _
    "Ana_Legale_e_Fisico VARCHAR(300)," & _
    "Ana_pagamento_assegnato VARCHAR(50),);")
Link to comment
Share on other sites
jchd Universalist

jchd

Posted
Posted

You have two errors:

Ana_Id should be "INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT"

(what is "not null" here is the type [integer], not the clause "primary key")

and you have an extra comma at the end of column definition.

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Link to comment
Share on other sites
faustf Universalist

faustf

Posted
  • Author
Posted

i correct  but happen  somthing  strange 

the  code  is  :

Func _prima_installazione()


    $Db=_SQLite_Open($percorso_Db); Open a permanent disk database
    If @error Then
        MsgBox(16+262144, "SQLite Error", "Can't create a memory Database!")
        Exit -1
    EndIf

_SQLite_Exec($Db, "Create table anagrafica (Ana_ID  INT PRIMARY KEY AUTOINCREMENT ," & _
    "Ana_Nome VARCHAR(300)," & _
    "Ana_Indirizzo VARCHAR(300)," & _
    "Ana_Citta VARCHAR(300)," & _
    "Ana_Provincia VARCHAR(300)," & _
    "Ana_Piva VARCHAR(300)," & _
    "Ana_CdF VARCHAR(300)," & _
    "Ana_telefono VARCHAR(300)," & _
    "Ana_mail VARCHAR(300)," & _
    "Ana_cell VARCHAR(300)," & _
    "Ana_fax VARCHAR(300)," & _
    "Ana_Nazione VARCHAR(300)," & _
    "Ana_Nome_Legale VARCHAR(300)," & _
    "Ana_Indirizzo_Legale VARCHAR(300)," & _
    "Ana_Citta_Legale VARCHAR(300)," & _
    "Ana_Provincia_Legale VARCHAR(300)," & _
    "Ana_Nazione_Legale VARCHAR(300)," & _
    "Ana_Ricordare VARCHAR(300)," & _
    "Ana_Mail_legale VARCHAR(300)," & _
    "Ana_Bank VARCHAR(300)," & _
    "Ana_Iban VARCHAR(300)," & _
    "Ana_Swift VARCHAR(300)," & _
    "Ana_Cell_Legale VARCHAR(300)," & _
    "Ana_Cap VARCHAR(300)," & _
    "Ana_Cap_Legale VARCHAR(300)," & _
    "Ana_Legale_e_Fisico VARCHAR(300)," & _
    "Ana_pagamento_assegnato VARCHAR(50));")

                _SQLite_Close($Db)
                _SQLite_Shutdown()


    EndFunc

if  i  runt  this  code  create a 0kb sqlite file , and if i try open with sqlite database browser , dont go and tell me is  not a real db

if modify the  script  in this mode

Func _prima_installazione()


    $Db=_SQLite_Open($percorso_Db); Open a permanent disk database
    If @error Then
        MsgBox(16+262144, "SQLite Error", "Can't create a memory Database!")
        Exit -1
ELSE

_SQLite_Exec($Db, "Create table anagrafica (Ana_ID  INT PRIMARY KEY AUTOINCREMENT ," & _
    "Ana_Nome VARCHAR(300)," & _
    "Ana_Indirizzo VARCHAR(300)," & _
    "Ana_Citta VARCHAR(300)," & _
    "Ana_Provincia VARCHAR(300)," & _
    "Ana_Piva VARCHAR(300)," & _
    "Ana_CdF VARCHAR(300)," & _
    "Ana_telefono VARCHAR(300)," & _
    "Ana_mail VARCHAR(300)," & _
    "Ana_cell VARCHAR(300)," & _
    "Ana_fax VARCHAR(300)," & _
    "Ana_Nazione VARCHAR(300)," & _
    "Ana_Nome_Legale VARCHAR(300)," & _
    "Ana_Indirizzo_Legale VARCHAR(300)," & _
    "Ana_Citta_Legale VARCHAR(300)," & _
    "Ana_Provincia_Legale VARCHAR(300)," & _
    "Ana_Nazione_Legale VARCHAR(300)," & _
    "Ana_Ricordare VARCHAR(300)," & _
    "Ana_Mail_legale VARCHAR(300)," & _
    "Ana_Bank VARCHAR(300)," & _
    "Ana_Iban VARCHAR(300)," & _
    "Ana_Swift VARCHAR(300)," & _
    "Ana_Cell_Legale VARCHAR(300)," & _
    "Ana_Cap VARCHAR(300)," & _
    "Ana_Cap_Legale VARCHAR(300)," & _
    "Ana_Legale_e_Fisico VARCHAR(300)," & _
    "Ana_pagamento_assegnato VARCHAR(50));")

                _SQLite_Close($Db)
                _SQLite_Shutdown()

ENDIF
    EndFunc

create a  db file  3kb  and  if  i open with sqlite database browser go ok  i  see a  ana_id .......

but  if i try  to write  with this  code

_SQLite_Exec($Db, "Insert into anagrafica (ana_nome,ana_indirizzo,ana_citta,ana_provincia,ana_piva,ana_cdf,ana_telefono,ana_mail,ana_cell,ana_fax,ana_nazione,ana_nome_legale,ana_indirizzo_legale,ana_citta_legale,ana_provincia_legale,ana_nazione_legale,ana_ricordare,ana_mail_legale,ana_bank,ana_iban,ana_swift,ana_cell_legale,ana_cap,ana_cap_legale,ana_legale_e_fisico,ana_pagamento_assegnato)" & _
"values (" & _SQLite_FastEscape($ditta_op)& "," & _
      _SQLite_FastEscape($via_op)& "," & _
      _SQLite_FastEscape($citta_op)& "," & _
      _SQLite_FastEscape($prov_op)& "," & _
      _SQLite_FastEscape($mail_op)& "," & _
      _SQLite_FastEscape($cell_op)& "," & _
      _SQLite_FastEscape($piva_op)& "," & _
      _SQLite_FastEscape($cdifi_op)& "," & _
      _SQLite_FastEscape($tell_op)& "," & _
      _SQLite_FastEscape($fax_op)& "," & _
      _SQLite_FastEscape($cap_op)& "," & _
      _SQLite_FastEscape($ditta_leg)& "," & _
      _SQLite_FastEscape($via_leg)& "," & _
      _SQLite_FastEscape($citta_leg)& "," & _
      _SQLite_FastEscape($prov_leg)& "," & _
      _SQLite_FastEscape($mail_leg)& "," & _
      _SQLite_FastEscape($cell_leg)& "," & _
      _SQLite_FastEscape($piva_leg)& "," & _
      _SQLite_FastEscape($cdifis_leg)& "," & _
      _SQLite_FastEscape($tell_leg)& "," & _
      _SQLite_FastEscape($fax_leg)& "," & _
      _SQLite_FastEscape($cap_leg)& "," & _
      _SQLite_FastEscape($iban_leg)& "," & _
      _SQLite_FastEscape($swift_leg)& "," & _
      _SQLite_FastEscape($banca_leg )&"," & _
      "'pagamento assegnato');")

_SQLite_Close()
_SQLite_Shutdown()

dont  give error  but  not  write  nothing inside the db 

Link to comment
Share on other sites
kylomas Universalist

kylomas

Posted
Posted (edited)

faustf,

First, it is not

INT PRIMARY KEY AUTOINCREMENT

but

INTEGER PRIMARY KEY AUTOINCREMENT

 

as JCHD pointed out above. 

When I run the code that you say worked,  I get an error in the SCiTE console explaining the problem.

Secondly, I don't think that you should be starting and stopping an instance of sqlite within each of these functions (the experts may have another opinion).  Start sqlite at the top of the script, use the DB then shut sqlite down after your are done. 

Finally, I changed your script as follows:

  

   corrected the primary key definition

   added enough to get the code fragments to run 

   changed the insert statement to hardcoded values (just to test the insert)

The code runs and I am able to view the DB with the inserted record.

#include <sqlite.au3>

_SQLite_Startup()

Local $percorso_Db = @ScriptDir & '\my.db3'
$Db = _SQLite_Open($percorso_Db); Open a permanent disk database

_prima_installazione()

_write_something()

_SQLite_Close($Db)
_SQLite_Shutdown()

Func _prima_installazione()


    If @error Then
        MsgBox(16 + 262144, "SQLite Error", "Can't create a memory Database!")
        Exit -1
    Else

        _sqlite_exec(-1,'drop table if exists anagrafica;')

        _SQLite_Exec($Db, "Create table anagrafica (Ana_ID  INTEGER PRIMARY KEY AUTOINCREMENT ," & _
                "Ana_Nome VARCHAR(300)," & _
                "Ana_Indirizzo VARCHAR(300)," & _
                "Ana_Citta VARCHAR(300)," & _
                "Ana_Provincia VARCHAR(300)," & _
                "Ana_Piva VARCHAR(300)," & _
                "Ana_CdF VARCHAR(300)," & _
                "Ana_telefono VARCHAR(300)," & _
                "Ana_mail VARCHAR(300)," & _
                "Ana_cell VARCHAR(300)," & _
                "Ana_fax VARCHAR(300)," & _
                "Ana_Nazione VARCHAR(300)," & _
                "Ana_Nome_Legale VARCHAR(300)," & _
                "Ana_Indirizzo_Legale VARCHAR(300)," & _
                "Ana_Citta_Legale VARCHAR(300)," & _
                "Ana_Provincia_Legale VARCHAR(300)," & _
                "Ana_Nazione_Legale VARCHAR(300)," & _
                "Ana_Ricordare VARCHAR(300)," & _
                "Ana_Mail_legale VARCHAR(300)," & _
                "Ana_Bank VARCHAR(300)," & _
                "Ana_Iban VARCHAR(300)," & _
                "Ana_Swift VARCHAR(300)," & _
                "Ana_Cell_Legale VARCHAR(300)," & _
                "Ana_Cap VARCHAR(300)," & _
                "Ana_Cap_Legale VARCHAR(300)," & _
                "Ana_Legale_e_Fisico VARCHAR(300)," & _
                "Ana_pagamento_assegnato VARCHAR(50));")


    EndIf
EndFunc   ;==>_prima_installazione

Func _write_something()

    _SQLite_Exec($Db, "Insert into anagrafica (ana_nome,ana_indirizzo,ana_citta,ana_provincia,ana_piva,ana_cdf,ana_telefono,ana_mail,ana_cell,ana_fax,ana_nazione,ana_nome_legale,ana_indirizzo_legale,ana_citta_legale,ana_provincia_legale,ana_nazione_legale,ana_ricordare,ana_mail_legale,ana_bank,ana_iban,ana_swift,ana_cell_legale,ana_cap,ana_cap_legale,ana_legale_e_fisico,ana_pagamento_assegnato)" & _
            "values (" & _SQLite_FastEscape(1) & "," & _
            _SQLite_FastEscape(2) & "," & _
            _SQLite_FastEscape(3) & "," & _
            _SQLite_FastEscape(4) & "," & _
            _SQLite_FastEscape(5) & "," & _
            _SQLite_FastEscape(6) & "," & _
            _SQLite_FastEscape(7) & "," & _
            _SQLite_FastEscape(8) & "," & _
            _SQLite_FastEscape(9) & "," & _
            _SQLite_FastEscape(10) & "," & _
            _SQLite_FastEscape(11) & "," & _
            _SQLite_FastEscape(12) & "," & _
            _SQLite_FastEscape(13) & "," & _
            _SQLite_FastEscape(14) & "," & _
            _SQLite_FastEscape(15) & "," & _
            _SQLite_FastEscape(16) & "," & _
            _SQLite_FastEscape(17) & "," & _
            _SQLite_FastEscape(18) & "," & _
            _SQLite_FastEscape(19) & "," & _
            _SQLite_FastEscape(20) & "," & _
            _SQLite_FastEscape(21) & "," & _
            _SQLite_FastEscape(22) & "," & _
            _SQLite_FastEscape(23) & "," & _
            _SQLite_FastEscape(24) & "," & _
            _SQLite_FastEscape(25) & "," & _
            "'pagamento assegnato');")

endfunc

kylomas

 

edit: spelling

 

Edited by kylomas

Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Link to comment
Share on other sites
jchd Universalist

jchd

Posted
Posted

I already told the OP several times to invoke *_Init, *_Open at the start of the program, and to only invoke *_Close then *_Shutdown at program termination. kylomas is right on every point.

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Link to comment
Share on other sites
  • Moderators
JLogan3o13 Universalist

JLogan3o13

Posted
  • Moderators
Posted

faustf, both klyomas and jchd are trying to help, but you have to at least try to think your way through this. Klyomas put in the @ScriptDir & "my.db3" because he doesn't know where your db file resides. Just change the path to the location of your db file.

"Profanity is the last vestige of the feeble mind. For the man who cannot express himself forcibly through intellect must do so through shock and awe" - Spencer W. Kimball

How to get your question answered on this forum!

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share
Go to topic listing

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...