date syntax error db postgres insert

[faustf]

hi guy

i try  to insert a date inside  the  postgres db  , but  answerme  error i formatted in  this mode ,

Local $dataslash = StringSplit(_DateTimeFormat(_NowCalc(), 2), '/')
            Local $orario = ($dataslash[3] & '-' & $dataslash[2] & '-' & $dataslash[1] & ' ' & _DateTimeFormat(_NowCalc(), 3) & '.000')

because  i just  another date  in db  and  it  formatted  in this mode 2016-02-23 20:31:30.546

but  id ont know  why  the insert answer me   with  error 

this is a code

odoo_presta_connector.au3 (418) : ==> COM Error intercepted !

    err.number is:         0x80020009
    err.windescription:    Exception occurred.

    err.description is:     ERRORE: input invalid syntax for the date type "";
Error while executing the query

    err.source is:         Microsoft OLE DB Provider for ODBC Drivers
    err.helpfile is:     
    err.helpcontext is:     0
    err.lastdllerror is:     0
    err.scriptline is:     418
    err.retcode is:     0x80004005

 

this is  my query 

Local $sQUERY = "INSERT INTO res_partner (name,company_id,comment,ean13,create_date,color,image_small,image,date,street," & _
                    "city,display_name,zip,title,function,country_id,parent_id,supplier,ref,email,is_company,website,customer,fax,street2,employee," & _
                    "credit_limit,write_date,active,tz,write_uid,lang,create_uid,image_medium,phone,mobile,type,use_parent_address,user_id,birthdate," & _
                    "vat,state_id,commercial_partner_id,notify_email,message_last_post,opt_out,section_id,signup_type,signup_expiration,signup_token," & _
                    "calendar_last_notif_ack,last_reconciliation_date,debit_limit,vat_subjected,speaker,website_meta_keywords,website_meta_description," & _
                    "website_meta_title,website_published,website_short_description,website_description,picking_warn,sale_warn,purchase_warn,picking_warn_msg," & _
                    "invoice_warn_msg,sale_warn_msg,invoice_warn,purchase_warn_msg) VALUES " & _
            "('" & $array_res_partner_exchange[$ab][1] & "','" & $array_res_partner_exchange[$ab][2] & "','" & $array_res_partner_exchange[$ab][3] & "','" & $array_res_partner_exchange[$ab][4] & "'," & _
            "'" & $array_res_partner_exchange[$ab][5] & "','" & $array_res_partner_exchange[$ab][6] & "','" & $array_res_partner_exchange[$ab][7] & "','" & $array_res_partner_exchange[$ab][8] & "'," & _
            "'" & $array_res_partner_exchange[$ab][9] & "','" & $array_res_partner_exchange[$ab][10] & "','" & $array_res_partner_exchange[$ab][11] & "','" & $array_res_partner_exchange[$ab][12] & "'," & _
            "'" & $array_res_partner_exchange[$ab][13] & "','" & $array_res_partner_exchange[$ab][14] & "','" & $array_res_partner_exchange[$ab][15] & "','" & $array_res_partner_exchange[$ab][16] & "'," & _
            "'" & $array_res_partner_exchange[$ab][17] & "','" & $array_res_partner_exchange[$ab][18] & "','" & $array_res_partner_exchange[$ab][19] & "','" & $array_res_partner_exchange[$ab][20] & "'," & _
            "'" & $array_res_partner_exchange[$ab][21] & "','" & $array_res_partner_exchange[$ab][22] & "','" & $array_res_partner_exchange[$ab][23] & "','" & $array_res_partner_exchange[$ab][24] & "'," & _
            "'" & $array_res_partner_exchange[$ab][25] & "','" & $array_res_partner_exchange[$ab][26] & "','" & $array_res_partner_exchange[$ab][27] & "','" & $array_res_partner_exchange[$ab][28] & "'," & _
            "'" & $array_res_partner_exchange[$ab][29] & "','" & $array_res_partner_exchange[$ab][30] & "','" & $array_res_partner_exchange[$ab][31] & "','" & $array_res_partner_exchange[$ab][32] & "'," & _
            "'" & $array_res_partner_exchange[$ab][33] & "','" & $array_res_partner_exchange[$ab][34] & "','" & $array_res_partner_exchange[$ab][35] & "','" & $array_res_partner_exchange[$ab][36] & "'," & _
            "'" & $array_res_partner_exchange[$ab][37] & "','" & $array_res_partner_exchange[$ab][38] & "','" & $array_res_partner_exchange[$ab][39] & "','" & $array_res_partner_exchange[$ab][40] & "'," & _
            "'" & $array_res_partner_exchange[$ab][41] & "','" & $array_res_partner_exchange[$ab][42] & "','" & $array_res_partner_exchange[$ab][43] & "','" & $array_res_partner_exchange[$ab][44] & "'," & _
            "'" & $array_res_partner_exchange[$ab][45] & "','" & $array_res_partner_exchange[$ab][46] & "','" & $array_res_partner_exchange[$ab][47] & "','" & $array_res_partner_exchange[$ab][48] & "'," & _
            "'" & $array_res_partner_exchange[$ab][49] & "','" & $array_res_partner_exchange[$ab][50] & "','" & $array_res_partner_exchange[$ab][51] & "','" & $array_res_partner_exchange[$ab][52] & "'," & _
            "'" & $array_res_partner_exchange[$ab][53] & "','" & $array_res_partner_exchange[$ab][54] & "','" & $array_res_partner_exchange[$ab][55] & "','" & $array_res_partner_exchange[$ab][56] & "'," & _
            "'" & $array_res_partner_exchange[$ab][57] & "','" & $array_res_partner_exchange[$ab][58] & "','" & $array_res_partner_exchange[$ab][59] & "','" & $array_res_partner_exchange[$ab][60] & "'," & _
            "'" & $array_res_partner_exchange[$ab][61] & "','" & $array_res_partner_exchange[$ab][62] & "','" & $array_res_partner_exchange[$ab][63] & "','" & $array_res_partner_exchange[$ab][64] & "'," & _
            "'" & $array_res_partner_exchange[$ab][65] & "','" & $array_res_partner_exchange[$ab][66] & "','" & $array_res_partner_exchange[$ab][67] & "','" & $array_res_partner_exchange[$ab][68] & "'," & _
            "'" & $array_res_partner_exchange[$ab][69] & "')"

i insert date inside  the array  before my query , and  is  array  number 9  and  28 

some one  have  idea  why ???

thankz  at all 

[mLipok]

& "','" & $array_res_partner_exchange[$ab][4] & "',"

How you think: What here is missing ?

btw. not only in this single place.

 

[Bowmore]

You will need to convert the string representations of your dates using the function To_Date or To_Timestamp, depending on the data type you have created the column in your database as. This is so that the database can correctly interoperate the date string.

For example

"'," & To_Date($array_res_partner_exchange[$ab][9],'YYYY-MM-DD HH:MM:SS.MS') & ",'"

 

[mLipok]

Next time try like this:

Local $sQUERY_Values = "('" & $array_res_partner_exchange[$ab][1] & "'"
For $i=2 To 68
    $sQUERY_Values &= ", '" & $array_res_partner_exchange[$ab][i] & "'"
Next
$sQUERY_Values &= ", '" & $array_res_partner_exchange[$ab][69] & "')"


Local $sQUERY = "INSERT INTO res_partner (name, company_id, comment, ean13, create_date, color, image_small, image, date, street, " & _
                    "city, display_name, zip, title, function, country_id, parent_id, supplier, ref, email, is_company, website, customer, fax, street2, employee, " & _
                    "credit_limit, write_date, active, tz, write_uid, lang, create_uid, image_medium, phone, mobile, type, use_parent_address, user_id, birthdate, " & _
                    "vat, state_id, commercial_partner_id, notify_email, message_last_post, opt_out, section_id, signup_type, signup_expiration, signup_token, " & _
                    "calendar_last_notif_ack, last_reconciliation_date, debit_limit, vat_subjected, speaker, website_meta_keywords, website_meta_description, " & _
                    "website_meta_title, website_published, website_short_description, website_description, picking_warn, sale_warn, purchase_warn, picking_warn_msg, " & _
                    "invoice_warn_msg, sale_warn_msg, invoice_warn, purchase_warn_msg) VALUES " & $sQUERY_Values

 

About the data type I sse you are using date just like @Bowmore said.

But not only date. 
VAT is number ?
User_id is number ?
company_id is number ?
.....

number should be passed as number not as string, so you should use 

", " & $array_res_partner_exchange[$ab][i] & ",

instead
 

", '" & $array_res_partner_exchange[$ab][i] & "'

 

btw.

About the date.

Is it date ? datetime ? or what ?
And another things In MS SQL I'm using CAST(...) like this:

........
$sQUERY &= "" & @CRLF
$sQUERY &= "OUTPUT inserted.[IDSomeColumnName]" & @CRLF
$sQUERY &= "" & @CRLF
$sQUERY &= "VALUES(0," & $vSomeOtherID & ",CAST('" & @YEAR & '-' & @MON & '-' & @MDAY & "' as datetime),1,0,5.7,4063,'',0,0,0,0,289,0,NULL,0)" & @CRLF
.......

 

Edited March 2, 2016 by mLipok

[faustf]

I try to do some modify  but  , have  now  error syntax 

this is  a code  error

odoo_presta_connector.au3 (418) : ==> COM Error intercepted !

    err.number is:      0x80020009
    err.windescription: Exception occurred.

    err.description is:     ERRORE: errore di sintassi a o presso "YYYY";
Error while executing the query

    err.source is:      Microsoft OLE DB Provider for ODBC Drivers
    err.helpfile is:    
    err.helpcontext is:     0
    err.lastdllerror is:    0
    err.scriptline is:  418
    err.retcode is:     0x80004005

and  this is my modify of  script 

Local $sQUERY = "INSERT INTO res_partner (name,company_id,comment,ean13,create_date,color,image_small,image,date,street," & _
                    "city,display_name,zip,title,function,country_id,parent_id,supplier,ref,email,is_company,website,customer,fax,street2,employee," & _
                    "credit_limit,write_date,active,tz,write_uid,lang,create_uid,image_medium,phone,mobile,type,use_parent_address,user_id,birthdate," & _
                    "vat,state_id,commercial_partner_id,notify_email,message_last_post,opt_out,section_id,signup_type,signup_expiration,signup_token," & _
                    "calendar_last_notif_ack,last_reconciliation_date,debit_limit,vat_subjected,speaker,website_meta_keywords,website_meta_description," & _
                    "website_meta_title,website_published,website_short_description,website_description,picking_warn,sale_warn,purchase_warn,picking_warn_msg," & _
                    "invoice_warn_msg,sale_warn_msg,invoice_warn,purchase_warn_msg) VALUES " & _
            "(" & $array_res_partner_exchange[$ab][1] & ",'" & $array_res_partner_exchange[$ab][2] & "','" & $array_res_partner_exchange[$ab][3] & "','" & $array_res_partner_exchange[$ab][4] & "'," & _
            "'" & $array_res_partner_exchange[$ab][5] & "'," & $array_res_partner_exchange[$ab][6] & ",'" & $array_res_partner_exchange[$ab][7] & "','" & $array_res_partner_exchange[$ab][8] & "'," & _
            "' TO DATE " & $array_res_partner_exchange[$ab][9] &",'YYYY-MM-DD HH:MM:SS.MS'"& "','" & $array_res_partner_exchange[$ab][10] & "','" & $array_res_partner_exchange[$ab][11] & "','" & $array_res_partner_exchange[$ab][12] & "'," & _
            "'" & $array_res_partner_exchange[$ab][13] & "'," & $array_res_partner_exchange[$ab][14] & ",'" & $array_res_partner_exchange[$ab][15] & "'," & $array_res_partner_exchange[$ab][16] & "," & _
            "" & $array_res_partner_exchange[$ab][17] & ",'" & $array_res_partner_exchange[$ab][18] & "','" & $array_res_partner_exchange[$ab][19] & "','" & $array_res_partner_exchange[$ab][20] & "'," & _
            "'" & $array_res_partner_exchange[$ab][21] & "','" & $array_res_partner_exchange[$ab][22] & "','" & $array_res_partner_exchange[$ab][23] & "','" & $array_res_partner_exchange[$ab][24] & "'," & _
            "'" & $array_res_partner_exchange[$ab][25] & "','" & $array_res_partner_exchange[$ab][26] & "'," & $array_res_partner_exchange[$ab][27] & ",'TO DATE " & $array_res_partner_exchange[$ab][28] &",'YYYY-MM-DD HH:MM:SS.MS'"& "'," & _
            "'" & $array_res_partner_exchange[$ab][29] & "','" & $array_res_partner_exchange[$ab][30] & "'," & $array_res_partner_exchange[$ab][31] & ",'" & $array_res_partner_exchange[$ab][32] & "'," & _
            "" & $array_res_partner_exchange[$ab][33] & ",'" & $array_res_partner_exchange[$ab][34] & "','" & $array_res_partner_exchange[$ab][35] & "','" & $array_res_partner_exchange[$ab][36] & "'," & _
            "'" & $array_res_partner_exchange[$ab][37] & "','" & $array_res_partner_exchange[$ab][38] & "'," & $array_res_partner_exchange[$ab][39] & ",'" & $array_res_partner_exchange[$ab][40] & "'," & _
            "'" & $array_res_partner_exchange[$ab][41] & "'," & $array_res_partner_exchange[$ab][42] & "," & $array_res_partner_exchange[$ab][43] & ",'" & $array_res_partner_exchange[$ab][44] & "'," & _
            "'" & $array_res_partner_exchange[$ab][45] & "','" & $array_res_partner_exchange[$ab][46] & "'," & $array_res_partner_exchange[$ab][47] & ",'" & $array_res_partner_exchange[$ab][48] & "'," & _
            "'" & $array_res_partner_exchange[$ab][49] & "','" & $array_res_partner_exchange[$ab][50] & "','" & $array_res_partner_exchange[$ab][51] & "','" & $array_res_partner_exchange[$ab][52] & "'," & _
            "" & $array_res_partner_exchange[$ab][53] & "," & $array_res_partner_exchange[$ab][54] & ",'" & $array_res_partner_exchange[$ab][55] & "','" & $array_res_partner_exchange[$ab][56] & "'," & _
            "'" & $array_res_partner_exchange[$ab][57] & "','" & $array_res_partner_exchange[$ab][58] & "','" & $array_res_partner_exchange[$ab][59] & "','" & $array_res_partner_exchange[$ab][60] & "'," & _
            "'" & $array_res_partner_exchange[$ab][61] & "','" & $array_res_partner_exchange[$ab][62] & "','" & $array_res_partner_exchange[$ab][63] & "','" & $array_res_partner_exchange[$ab][64] & "'," & _
            "'" & $array_res_partner_exchange[$ab][65] & "','" & $array_res_partner_exchange[$ab][66] & "','" & $array_res_partner_exchange[$ab][67] & "','" & $array_res_partner_exchange[$ab][68] & "'," & _
            "'" & $array_res_partner_exchange[$ab][69] & "')"

 

[mLipok]

2 hours ago, Bowmore said:

For example

"'," & To_Date($array_res_partner_exchange[$ab][9],'YYYY-MM-DD HH:MM:SS.MS') & ",'"

 

@faustf you post diffrent example:

"' TO DATE " & $array_res_partner_exchange[$ab][9] &",'YYYY-MM-DD HH:MM:SS.MS'"& "','"

you should try to use:

"', TO_DATE(" & $array_res_partner_exchange[$ab][9] &",'YYYY-MM-DD HH:MM:SS.MS')"& "','"

 

 

http://www.postgresql.org/docs/8.1/static/functions-formatting.html

 

[mLipok]

http://www.postgresqltutorial.com/postgresql-cast/

 

[faustf]

so i just  do  some  other   test

i cloning  table  and  reduce  colum  at  2    id and  date  (timestamp 6)

this syntax , i  used  in this  test 

Local $oErrorHandler = ObjEvent("AutoIt.Error", "_ErrFunc")
    _Connetti_DB_Postgre()

    Local $dataslash = StringSplit(_DateTimeFormat(_NowCalc(), 2), '/')
    Local $orario = ($dataslash[3] & '-' & $dataslash[2] & '-' & $dataslash[1] & ' ' & _DateTimeFormat(_NowCalc(), 3) & '.111')

    MsgBox (0,'',$orario)

    Local $sQUERY = "INSERT INTO res_partner_copy (create_date) VALUES ('" & $orario & "')"
    Local $rsCustomers = $objConn.Execute($sQUERY)
    If @error Then MsgBox($MB_ICONERROR, '$rsCustomers', '@error = ' & @error & @CRLF & '@extended = ' & @extended)



    _Close_DB_Postgre()

in this mode  work perfect  

i tryed  to replicate  the same ,situation in  program,  this  is  a script 

Local $sQUERY = "INSERT INTO res_partner (name,company_id,comment,ean13,create_date,color,image_small,image,date,street," & _
                    "city,display_name,zip,title,function,country_id,parent_id,supplier,ref,email,is_company,website,customer,fax,street2,employee," & _
                    "credit_limit,write_date,active,tz,write_uid,lang,create_uid,image_medium,phone,mobile,type,use_parent_address,user_id,birthdate," & _
                    "vat,state_id,commercial_partner_id,notify_email,message_last_post,opt_out,section_id,signup_type,signup_expiration,signup_token," & _
                    "calendar_last_notif_ack,last_reconciliation_date,debit_limit,vat_subjected,speaker,website_meta_keywords,website_meta_description," & _
                    "website_meta_title,website_published,website_short_description,website_description,picking_warn,sale_warn,purchase_warn,picking_warn_msg," & _
                    "invoice_warn_msg,sale_warn_msg,invoice_warn,purchase_warn_msg) VALUES " & _
            "('" & $array_res_partner_exchange[$ab][1] & "'," & $array_res_partner_exchange[$ab][2] & ",'" & $array_res_partner_exchange[$ab][3] & "','" & $array_res_partner_exchange[$ab][4] & "'," & _
            "'" & $array_res_partner_exchange[$ab][5] & "," &  $array_res_partner_exchange[$ab][6] & ",'" & $array_res_partner_exchange[$ab][7] & "','" & $array_res_partner_exchange[$ab][8] & "'," & _
            "'" & $array_res_partner_exchange[$ab][9]& "," &$array_res_partner_exchange[$ab][10] & "','" & $array_res_partner_exchange[$ab][11] & "','" & $array_res_partner_exchange[$ab][12] & "'," & _
            "" & $array_res_partner_exchange[$ab][13] & "," & $array_res_partner_exchange[$ab][14] & ",'" & $array_res_partner_exchange[$ab][15] & "'," & $array_res_partner_exchange[$ab][16] & "," & _
            "" & $array_res_partner_exchange[$ab][17] & ",'" & $array_res_partner_exchange[$ab][18] & "','" & $array_res_partner_exchange[$ab][19] & "','" & $array_res_partner_exchange[$ab][20] & "'," & _
            "'" & $array_res_partner_exchange[$ab][21] & "','" & $array_res_partner_exchange[$ab][22] & "','" & $array_res_partner_exchange[$ab][23] & "','" & $array_res_partner_exchange[$ab][24] & "'," & _
            "'" & $array_res_partner_exchange[$ab][25] & "','" & $array_res_partner_exchange[$ab][26] & "'," & $array_res_partner_exchange[$ab][27] & ",'" & $array_res_partner_exchange[$ab][28]& "'," & _
            "'" & $array_res_partner_exchange[$ab][29] & "','" & $array_res_partner_exchange[$ab][30] & "'," & $array_res_partner_exchange[$ab][31] & ",'" & $array_res_partner_exchange[$ab][32] & "'," & _
            "" & $array_res_partner_exchange[$ab][33] & ",'" & $array_res_partner_exchange[$ab][34] & "'," & $array_res_partner_exchange[$ab][35] & "," & $array_res_partner_exchange[$ab][36] & "," & _
            "'" & $array_res_partner_exchange[$ab][37] & "','" & $array_res_partner_exchange[$ab][38] & "'," & $array_res_partner_exchange[$ab][39] & ",'" & $array_res_partner_exchange[$ab][40] & "'," & _
            "'" & $array_res_partner_exchange[$ab][41] & "'," & $array_res_partner_exchange[$ab][42] & "," & $array_res_partner_exchange[$ab][43] & ",'" & $array_res_partner_exchange[$ab][44] & "'," & _
            "'" & $array_res_partner_exchange[$ab][45] & "','" & $array_res_partner_exchange[$ab][46] & "'," & $array_res_partner_exchange[$ab][47] & ",'" & $array_res_partner_exchange[$ab][48] & "'," & _
            "'" & $array_res_partner_exchange[$ab][49] & "','" & $array_res_partner_exchange[$ab][50] & "','" & $array_res_partner_exchange[$ab][51] & "','" & $array_res_partner_exchange[$ab][52] & "'," & _
            "" & $array_res_partner_exchange[$ab][53] & "," & $array_res_partner_exchange[$ab][54] & ",'" & $array_res_partner_exchange[$ab][55] & "','" & $array_res_partner_exchange[$ab][56] & "'," & _
            "'" & $array_res_partner_exchange[$ab][57] & "','" & $array_res_partner_exchange[$ab][58] & "','" & $array_res_partner_exchange[$ab][59] & "','" & $array_res_partner_exchange[$ab][60] & "'," & _
            "'" & $array_res_partner_exchange[$ab][61] & "','" & $array_res_partner_exchange[$ab][62] & "','" & $array_res_partner_exchange[$ab][63] & "','" & $array_res_partner_exchange[$ab][64] & "'," & _
            "'" & $array_res_partner_exchange[$ab][65] & "','" & $array_res_partner_exchange[$ab][66] & "','" & $array_res_partner_exchange[$ab][67] & "','" & $array_res_partner_exchange[$ab][68] & "'," & _
            "'" & $array_res_partner_exchange[$ab][69] & "')"

in this  mode  i  have  error 

odoo_presta_connector.au3 (445) : ==> COM Error intercepted !

    err.number is:         0x80020009
    err.windescription:    Exception occurred.

    err.description is:     ERRORE:  error syntax near  "2016";
Error while executing the query

    err.source is:         Microsoft OLE DB Provider for ODBC Drivers
    err.helpfile is:     
    err.helpcontext is:     0
    err.lastdllerror is:     0
    err.scriptline is:     445
    err.retcode is:     0x80004005

someone  have  idea???

 

[mLipok]

Add this line:

Clipput ( $sQUERY )

past it to notepad or in new tab in SciTE

as you get:

Quote

  err.description is:     ERRORE:  error syntax near  "2016";
Error while executing the query

 

find 2006
 

post here SQL Snippet  content  "2016" (some text before and after this year).
There is no need to post entire $sQUERY content just a fragment with 2016
If you are using few times "2016" you should post few snippets.

 

You are doing there something wrong.

 

Edited March 3, 2016 by mLipok

[faustf]

so in this  time  i  have  another  big  idea  .... insert   with  date in clear , like  this  

 

Local $sQUERY = "INSERT INTO res_partner (name,company_id,comment,ean13,create_date,color,image_small,image,date,street," & _
                    "city,display_name,zip,title,function,country_id,parent_id,supplier,ref,email,is_company,website,customer,fax,street2,employee," & _
                    "credit_limit,write_date,active,tz,write_uid,lang,create_uid,image_medium,phone,mobile,type,use_parent_address,user_id,birthdate," & _
                    "vat,state_id,commercial_partner_id,notify_email,message_last_post,opt_out,section_id,signup_type,signup_expiration,signup_token," & _
                    "calendar_last_notif_ack,last_reconciliation_date,debit_limit,vat_subjected,speaker,website_meta_keywords,website_meta_description," & _
                    "website_meta_title,website_published,website_short_description,website_description,picking_warn,sale_warn,purchase_warn,picking_warn_msg," & _
                    "invoice_warn_msg,sale_warn_msg,invoice_warn,purchase_warn_msg) VALUES " & _
            "('lopresti',1,'funkoff','ean13'," & _
            "'2016-02-22 19:47:24.727',2,'image small','image'," & _
            "'2016-02-22 19:47:24.727','street','city','lopresti name'," & _
            "350054,4,'funzione',5," & _
            "60000,'0','ref','email'," & _
            "'f','website','f','fax'," & _
            "'street 2','f',7000,'2016-02-22 19:47:24.727'," & _
            "'t','tz',80000,'lang'," & _
            "90000,'image medium',3336268186,3356963030," & _
            "'type','f',100000,'13/12/1977'," & _
            "'22%',1100000,1,'f'," & _
            "'2016-02-22 19:47:24.727','f',120000,'signup type'," & _
            "'2016-02-22 19:47:24.727','signup token','2016-02-22 19:47:24.727','2016-02-22 19:47:24.727'," & _
            "12,'f','f','website meta'," & _
            "'website meta description','website metatitle','f','web site short descript'," & _
            "'web site description','picking warn','sale warn ','purchase warn'," & _
            "'picking warn msg','invocie warn mesg','sale warn msg','invoice warn'," & _
            "'purchase_ warn msg')"

now  i think work correct  , but   have  this  error 

 

odoo_presta_connector.au3 (445) : ==> COM Error intercepted !

    err.number is:         0x80020009
    err.windescription:    Exception occurred.

    err.description is:     ERRORE: la INSERT o l'UPDATE over table "res_partner" violates foreign key constraeint "res_partner_user_id_fkey";
Error while executing the query

    err.source is:         Microsoft OLE DB Provider for ODBC Drivers
    err.helpfile is:     
    err.helpcontext is:     0
    err.lastdllerror is:     0
    err.scriptline is:     445
    err.retcode is:     0x80004005

Edited March 3, 2016 by faustf

[mLipok]

I'm not so DBMS expert 
You should read:

http://www.postgresql.org/docs/8.1/static/tutorial-fk.html

http://dba.stackexchange.com/questions/29147/postgresql-insert-update-violates-foreign-key-constraints

http://stackoverflow.com/questions/2444899/insert-or-update-on-table-violates-foreign-key-constraint

 

[mLipok]

I think that you have some rules (constraint) for "user_id" which you violates when you trying to instert such a set of data.

 

[faustf]

 i think so  , never  one  happy 

[mLipok]

Based on this tutorial

http://www.postgresql.org/docs/8.1/static/tutorial-fk.html

Without seeing your DataBase I can gues that you are using "user_id" which not exist.

 

[Bowmore]

I think you still have an issue with the format of the dates.

In your first test script in post #8 that you say worked 
 $sQUERY = INSERT INTO res_partner_copy (create_date) VALUES ('03-03-2016 23:11:41.111')  

I assume that is the default date format on your so the database can make sense of the date and time string.

The value of $sQUERY in post #10 contains dates in a different format

  "'2016-02-22 19:47:24.727','signup token','2016-02-22 19:47:24.727','2016-02-22 19:47:24.727'," & _

which the database can not make sense of 
Therefore you need to either convert all the dates to the default date format of your PC or use TO_DATE specify the format used in the data

 

[faustf]

if  you want my database  you can install ,   a  bitnami  odoo   https://bitnami.com/redirect/to/83111/bitnami-odoo-8.0-14-windows-installer.exe , exist  also a virtual machin  , but  is in  linux  os 

 

[faustf]

hi  i  have  little questions  with this  code  the  odbc  give me erro r    ","; 

but  i dont understund  where is  

Local $sQUERY = "INSERT INTO res_partner (name,company_id,comment,create_date,color,street,city," & _
                    "display_name,zip,country_id,supplier,email,is_company,customer,street2,employee,credit_limit,write_date," & _
                    "active,tz,write_uid,lang,create_uid,phone,mobile,type,use_parent_address,user_id,state_id,notify_email," & _
                    "opt_out,debit_limit,vat_subjected,speaker,website_published,picking_warn,sale_warn,purchase_warn,invoice_warn)" & _
                    "VALUES " & _
                    "('"  & $array_res_partner_exchange[$ab][1]  & "',"  & $array_res_partner_exchange[$ab][2]  & ",'"  & $array_res_partner_exchange[$ab][3]  & "',"  & _
                    "'"   & $array_res_partner_exchange[$ab][5]  & "',"  & $array_res_partner_exchange[$ab][6]  & ",'"  & $array_res_partner_exchange[$ab][10] & "',"  & _
                    "'"   & $array_res_partner_exchange[$ab][11] & "','" & $array_res_partner_exchange[$ab][12] & "','" & $array_res_partner_exchange[$ab][13] & "',"   & _
                    ""    & $array_res_partner_exchange[$ab][16] & ",'"  & $array_res_partner_exchange[$ab][18] & "','" & $array_res_partner_exchange[$ab][20] & "',"  & _
                    "'"   & $array_res_partner_exchange[$ab][21] & "','" & $array_res_partner_exchange[$ab][23] & "','" & $array_res_partner_exchange[$ab][25] & "',"  & _
                    "'"   & $array_res_partner_exchange[$ab][26] & "',"  & $array_res_partner_exchange[$ab][27] & ",'"  & $array_res_partner_exchange[$ab][28] & "',"  & _
                    "'"   & $array_res_partner_exchange[$ab][29] & "','" & $array_res_partner_exchange[$ab][30] & "',"  & $array_res_partner_exchange[$ab][31] & ","   & _
                    "'"   & $array_res_partner_exchange[$ab][32] & "',"  & $array_res_partner_exchange[$ab][33] & ",'"  & $array_res_partner_exchange[$ab][35] & "',"  & _
                    "'"   & $array_res_partner_exchange[$ab][36] & "','" & $array_res_partner_exchange[$ab][37] & "','" & $array_res_partner_exchange[$ab][38] & "',"  & _
                    ""    & $array_res_partner_exchange[$ab][39] & ","   & $array_res_partner_exchange[$ab][42] & ",'"  & $array_res_partner_exchange[$ab][44] & "',"  & _
                    "'"   & $array_res_partner_exchange[$ab][46] & "',"  & $array_res_partner_exchange[$ab][53] & ",'"  & $array_res_partner_exchange[$ab][54] & "',"  & _
                    "'"   & $array_res_partner_exchange[$ab][55] & "','" & $array_res_partner_exchange[$ab][59] & "','" & $array_res_partner_exchange[$ab][62] & "',"  & _
                    "'"   & $array_res_partner_exchange[$ab][63] & "','" & $array_res_partner_exchange[$ab][64] & "','" & $array_res_partner_exchange[$ab][66] & "')"

odoo_presta_connector.au3 (440) : ==> COM Error intercepted !

    err.number is:      0x80020009
    err.windescription: Exception occurred.

    err.description is:     ERRORE: syntax error near  ",";
Error while executing the query

    err.source is:      Microsoft OLE DB Provider for ODBC Drivers
    err.helpfile is:    
    err.helpcontext is:     0
    err.lastdllerror is:    0
    err.scriptline is:  440
    err.retcode is:     0x80004005

 

[faustf]

i answer me  ,  i find  a solution  the  problem  happen  when ,  inside a  array  , not  have  nothing  , and   value  is  integer or float