1. RAFAEL RODRIGUEZ
  2. PowerBuilder
  3. Tuesday, 26 November 2019 01:38 AM UTC

I have this message when I try to save a register with a primary key formed by two columns (moneda char(1) , fecha datetime ).

 

The message is SQLSTATE = 42000

sintaxis incorrecta cerca de '00'

no changes made to database

 

INSERT INTO CA ( MONEDA, FECHA, CAMBIO1, CAMBIO2, CAMBIO3 ) VALUES ( 'D', 2019/11/24 00:00:00:000, 6.9600, 6.9600, 6.9600 )

 

 

Can you help me, thanks

 

 

Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Monday, 6 January 2020 15:05 PM UTC
  2. PowerBuilder
  3. # 1

Hi Rafael;

   What happens when you run this SQL statement through the DB Painter's ISQL Pane?

Regards ... Chris

Comment
  1. RAFAEL RODRIGUEZ
  2. Tuesday, 7 January 2020 21:30 PM UTC
I run this on DB painter and gave me this message

SQLSTATE = 42000

Microsoft SQL Server Native Client 11.0

sintaxis incorrecta cerca de '00'



But I change the SQL with ' ' on the datetime column, and works

like that:

INSERT INTO CA ( MONEDA, FECHA, CAMBIO1, CAMBIO2, CAMBIO3 ) VALUES ( 'D', '2019/11/25 00:00:00.000' , 6.9600, 6.9600, 6.9600 );



now How Can I do it from the Datawindow



  1. Helpful
There are no comments made yet.
Michael Kramer Accepted Answer Pending Moderation
  1. Tuesday, 26 November 2019 06:18 AM UTC
  2. PowerBuilder
  3. # 2

This looks like DisableBind=1 with unquoted datetime which makes the DB think it is one date followed by one time separated only by space - which is illegal syntax.

Try DisableBind=0.

Otherwise find out why your datetime value is missing surrounding single quotes.

HTH /Michael 

PS: DisableBind=1 is a sure way to enable SQL injection. You should only use this option with extreme care and in general avoid it whenever possible. 

Comment
  1. RAFAEL RODRIGUEZ
  2. Tuesday, 26 November 2019 16:45 PM UTC
Thanks , i did this change and now it works
  1. Helpful
  1. RAFAEL RODRIGUEZ
  2. Friday, 3 January 2020 22:55 PM UTC
I tried with disablebind = 1, on client´s PC works when i add a register, but when i try to delete it doesn´t work.
  1. Helpful
  1. Chris Pollach @Appeon
  2. Friday, 3 January 2020 23:24 PM UTC
Hi Rafael ... FYI: the DisableBind setting does not change the syntax of the SQL statement generated by any DWO. What it does do is change the way the SQL syntax is constructed. The end result should be the same but the validation process changes to get to the same end result.
  1. Helpful
There are no comments made yet.
John Fauss Accepted Answer Pending Moderation
  1. Tuesday, 26 November 2019 05:05 AM UTC
  2. PowerBuilder
  3. # 3

Rafael -

I'd like to see the table definition (list of columns, data type of each column, primary key columns) as defined in the database and then also the exported DataWindow object (attach the exported .srd file).

Comment
  1. RAFAEL RODRIGUEZ
  2. Friday, 3 January 2020 22:59 PM UTC
This is the definition on SQL:



CREATE TABLE dbo.CA ( MONEDA char(1) NOT NULL , FECHA datetime NOT NULL , CAMBIO1 numeric(16,4) NOT NULL , CAMBIO2 numeric(16,4) NULL , CAMBIO3 numeric(16,4) NULL , CONSTRAINT PK_CA PRIMARY KEY CLUSTERED (MONEDA, FECHA)) ;

insert into dbo.pbcattbl (pbt_tnam, pbt_tid, pbt_ownr, pbd_fhgt, pbd_fwgt, pbd_fitl, pbd_funl, pbd_fchr, pbd_fptc, pbd_ffce, pbh_fhgt, pbh_fwgt, pbh_fitl, pbh_funl, pbh_fchr, pbh_fptc, pbh_ffce, pbl_fhgt, pbl_fwgt, pbl_fitl, pbl_funl, pbl_fchr, pbl_fptc, pbl_ffce, pbt_cmnt) values ('CA', OBJECT_ID('dbo.CA'), 'dbo', 0, 0, 'N', 'N', 0, 0, 'Tahoma', 0, 0, 'N', 'N', 0, 0, 'Tahoma', 0, 0 , 'N', 'N', 0, 0, 'Tahoma', NULL ) ;

insert into dbo.pbcatcol(pbc_tnam, pbc_tid, pbc_ownr, pbc_cnam, pbc_cid, pbc_lpos, pbc_hpos, pbc_jtfy, pbc_case, pbc_hght, pbc_wdth, pbc_bmap ) values ('CA' , OBJECT_ID('dbo.CA'), 'dbo' , 'MONEDA', 1 , 0 , 0, 0 , 0 , 0, 0, 'N') ;

insert into dbo.pbcatcol(pbc_tnam, pbc_tid, pbc_ownr, pbc_cnam, pbc_cid, pbc_lpos, pbc_hpos, pbc_jtfy, pbc_case, pbc_hght, pbc_wdth, pbc_bmap ) values ('CA' , OBJECT_ID('dbo.CA'), 'dbo' , 'FECHA', 2 , 0 , 0, 0 , 0 , 0, 0, 'N') ;

insert into dbo.pbcatcol(pbc_tnam, pbc_tid, pbc_ownr, pbc_cnam, pbc_cid, pbc_lpos, pbc_hpos, pbc_jtfy, pbc_case, pbc_hght, pbc_wdth, pbc_bmap ) values ('CA' , OBJECT_ID('dbo.CA'), 'dbo' , 'CAMBIO1', 3 , 0 , 0, 0 , 0 , 0, 0, 'N') ;

insert into dbo.pbcatcol(pbc_tnam, pbc_tid, pbc_ownr, pbc_cnam, pbc_cid, pbc_lpos, pbc_hpos, pbc_jtfy, pbc_case, pbc_hght, pbc_wdth, pbc_bmap ) values ('CA' , OBJECT_ID('dbo.CA'), 'dbo' , 'CAMBIO2', 4 , 0 , 0, 0 , 0 , 0, 0, 'N') ;

insert into dbo.pbcatcol(pbc_tnam, pbc_tid, pbc_ownr, pbc_cnam, pbc_cid, pbc_lpos, pbc_hpos, pbc_jtfy, pbc_case, pbc_hght, pbc_wdth, pbc_bmap ) values ('CA' , OBJECT_ID('dbo.CA'), 'dbo' , 'CAMBIO3', 5 , 0 , 0, 0 , 0 , 0, 0, 'N') ;



The .SRD file is this:



release 19;

datawindow(units=0 timer_interval=0 color=16777215 brushmode=0 transparency=0 gradient.angle=0 gradient.color=8421504 gradient.focus=0 gradient.repetition.count=0 gradient.repetition.length=100 gradient.repetition.mode=0 gradient.scale=100 gradient.spread=100 gradient.transparency=0 picture.blur=0 picture.clip.bottom=0 picture.clip.left=0 picture.clip.right=0 picture.clip.top=0 picture.mode=0 picture.scale.x=100 picture.scale.y=100 picture.transparency=0 processing=0 HTMLDW=no print.printername="" print.documentname="" print.orientation = 0 print.margin.left = 110 print.margin.right = 110 print.margin.top = 96 print.margin.bottom = 96 print.paper.source = 0 print.paper.size = 0 print.canusedefaultprinter=yes print.prompt=no print.buttons=no print.preview.buttons=no print.cliptext=no print.overrideprintjob=no print.collate=yes print.background=no print.preview.background=no print.preview.outline=yes hidegrayline=no showbackcoloronxp=no picture.file="" )

header(height=0 color="536870912" transparency="0" gradient.color="8421504" gradient.transparency="0" gradient.angle="0" brushmode="0" gradient.repetition.mode="0" gradient.repetition.count="0" gradient.repetition.length="100" gradient.focus="0" gradient.scale="100" gradient.spread="100" )

summary(height=0 color="536870912" transparency="0" gradient.color="8421504" gradient.transparency="0" gradient.angle="0" brushmode="0" gradient.repetition.mode="0" gradient.repetition.count="0" gradient.repetition.length="100" gradient.focus="0" gradient.scale="100" gradient.spread="100" )

footer(height=0 color="536870912" transparency="0" gradient.color="8421504" gradient.transparency="0" gradient.angle="0" brushmode="0" gradient.repetition.mode="0" gradient.repetition.count="0" gradient.repetition.length="100" gradient.focus="0" gradient.scale="100" gradient.spread="100" )

detail(height=472 color="536870912" transparency="0" gradient.color="8421504" gradient.transparency="0" gradient.angle="0" brushmode="0" gradient.repetition.mode="0" gradient.repetition.count="0" gradient.repetition.length="100" gradient.focus="0" gradient.scale="100" gradient.spread="100" )

table(column=(type=char(1) update=yes updatewhereclause=yes key=yes name=moneda dbname="CA.MONEDA" dbalias=".MONEDA" initial="D" )

column=(type=datetime update=yes updatewhereclause=yes key=yes name=fecha dbname="CA.FECHA" dbalias=".FECHA" validationmsg="~"La fecha no esta correcta~"" )

column=(type=decimal(4) update=yes updatewhereclause=yes name=cambio1 dbname="CA.CAMBIO1" dbalias=".CAMBIO1" initial="1" validation="dec(gettext()) >= 1 " validationmsg="~"El tipo de cambio esta incorrecto~"" )

column=(type=decimal(4) update=yes updatewhereclause=yes name=cambio2 dbname="CA.CAMBIO2" dbalias=".CAMBIO2" initial="0" validation="dec(gettext()) >= 0" validationmsg="~"El tipo de cambio esta incorrecto~"" )

column=(type=decimal(4) update=yes updatewhereclause=yes name=cambio3 dbname="CA.CAMBIO3" dbalias=".CAMBIO3" initial="0" validation="dec(gettext()) >= 0" validationmsg="~"El tipo de cambio esta incorrecto~"" )

retrieve="PBSELECT( VERSION(400) TABLE(NAME=~"CA~" ) COLUMN(NAME=~"CA.MONEDA~") COLUMN(NAME=~"CA.FECHA~") COLUMN(NAME=~"CA.CAMBIO1~") COLUMN(NAME=~"CA.CAMBIO2~") COLUMN(NAME=~"CA.CAMBIO3~")) ORDER(NAME=~"CA.MONEDA~" ASC=yes ) ORDER(NAME=~"CA.FECHA~" ASC=yes ) " update="CA" updatewhere=0 updatekeyinplace=no retrieve.asneeded=yes sort="moneda A fecha A " )

text(band=detail alignment="0" text="Moneda:" border="0" color="0" x="27" y="40" height="64" width="219" html.valueishtml="0" name=moneda_t visible="1" font.face="Times New Roman" font.height="-10" font.weight="400" font.family="1" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" background.transparency="0" background.gradient.color="8421504" background.gradient.transparency="0" background.gradient.angle="0" background.brushmode="0" background.gradient.repetition.mode="0" background.gradient.repetition.count="0" background.gradient.repetition.length="100" background.gradient.focus="0" background.gradient.scale="100" background.gradient.spread="100" tooltip.backcolor="134217752" tooltip.delay.initial="0" tooltip.delay.visible="32000" tooltip.enabled="0" tooltip.hasclosebutton="0" tooltip.icon="0" tooltip.isbubble="0" tooltip.maxwidth="0" tooltip.textcolor="134217751" tooltip.transparency="0" transparency="0" )

column(band=detail id=1 alignment="0" tabsequence=32766 border="1" color="0" x="247" y="40" height="60" width="462" format="[general]" html.valueishtml="0" name=moneda tag="Digite la moneda para el tipo de cambio" visible="1" dddw.name=dw_h_moneda dddw.displaycolumn=vpa dddw.datacolumn=desc2 dddw.percentwidth=350 dddw.lines=0 dddw.limit=0 dddw.allowedit=no dddw.useasborder=yes dddw.case=any dddw.vscrollbar=yes font.face="Times New Roman" font.height="-10" font.weight="400" font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" background.transparency="0" background.gradient.color="8421504" background.gradient.transparency="0" background.gradient.angle="0" background.brushmode="0" background.gradient.repetition.mode="0" background.gradient.repetition.count="0" background.gradient.repetition.length="100" background.gradient.focus="0" background.gradient.scale="100" background.gradient.spread="100" tooltip.backcolor="134217752" tooltip.delay.initial="0" tooltip.delay.visible="32000" tooltip.enabled="0" tooltip.hasclosebutton="0" tooltip.icon="0" tooltip.isbubble="0" tooltip.maxwidth="0" tooltip.textcolor="134217751" tooltip.transparency="0" transparency="0" )

column(band=detail id=2 alignment="0" tabsequence=10 border="1" color="0" x="960" y="36" height="60" width="347" format="DD/MM/YYYY" html.valueishtml="0" protect="0~t1" name=fecha tag="Digite la fecha del tipo de Cambio" visible="1" editmask.required=yes editmask.ddcalendar=yes editmask.mask="DD/MM/YYYY " editmask.focusrectangle=no font.face="Times New Roman" font.height="-10" font.weight="400" font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" background.transparency="0" background.gradient.color="8421504" background.gradient.transparency="0" background.gradient.angle="0" background.brushmode="0" background.gradient.repetition.mode="0" background.gradient.repetition.count="0" background.gradient.repetition.length="100" background.gradient.focus="0" background.gradient.scale="100" background.gradient.spread="100" tooltip.backcolor="134217752" tooltip.delay.initial="0" tooltip.delay.visible="32000" tooltip.enabled="0" tooltip.hasclosebutton="0" tooltip.icon="0" tooltip.isbubble="0" tooltip.maxwidth="0" tooltip.textcolor="134217751" tooltip.transparency="0" transparency="0" )

column(band=detail id=3 alignment="1" tabsequence=20 border="1" color="0" x="960" y="136" height="64" width="347" format="##,###.000000" html.valueishtml="0" name=cambio1 tag="Digite el monto de la cotización oficial" visible="1" editmask.required=yes editmask.mask="###,###.000000" editmask.focusrectangle=no font.face="Times New Roman" font.height="-10" font.weight="400" font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" background.transparency="0" background.gradient.color="8421504" background.gradient.transparency="0" background.gradient.angle="0" background.brushmode="0" background.gradient.repetition.mode="0" background.gradient.repetition.count="0" background.gradient.repetition.length="100" background.gradient.focus="0" background.gradient.scale="100" background.gradient.spread="100" tooltip.backcolor="134217752" tooltip.delay.initial="0" tooltip.delay.visible="32000" tooltip.enabled="0" tooltip.hasclosebutton="0" tooltip.icon="0" tooltip.isbubble="0" tooltip.maxwidth="0" tooltip.textcolor="134217751" tooltip.transparency="0" transparency="0" )

column(band=detail id=4 alignment="1" tabsequence=30 border="1" color="0" x="960" y="252" height="64" width="347" format="##,###.000000" html.valueishtml="0" name=cambio2 tag="Digite el monto de compra" visible="1" editmask.mask="###,###.000000" editmask.focusrectangle=no font.face="Times New Roman" font.height="-10" font.weight="400" font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" background.transparency="0" background.gradient.color="8421504" background.gradient.transparency="0" background.gradient.angle="0" background.brushmode="0" background.gradient.repetition.mode="0" background.gradient.repetition.count="0" background.gradient.repetition.length="100" background.gradient.focus="0" background.gradient.scale="100" background.gradient.spread="100" tooltip.backcolor="134217752" tooltip.delay.initial="0" tooltip.delay.visible="32000" tooltip.enabled="0" tooltip.hasclosebutton="0" tooltip.icon="0" tooltip.isbubble="0" tooltip.maxwidth="0" tooltip.textcolor="134217751" tooltip.transparency="0" transparency="0" )

column(band=detail id=5 alignment="1" tabsequence=40 border="1" color="0" x="960" y="364" height="64" width="347" format="##,###.000000" html.valueishtml="0" name=cambio3 tag="Digite el monto de venta" visible="1" editmask.mask="###,###.000000" editmask.focusrectangle=no font.face="Times New Roman" font.height="-10" font.weight="400" font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" background.transparency="0" background.gradient.color="8421504" background.gradient.transparency="0" background.gradient.angle="0" background.brushmode="0" background.gradient.repetition.mode="0" background.gradient.repetition.count="0" background.gradient.repetition.length="100" background.gradient.focus="0" background.gradient.scale="100" background.gradient.spread="100" tooltip.backcolor="134217752" tooltip.delay.initial="0" tooltip.delay.visible="32000" tooltip.enabled="0" tooltip.hasclosebutton="0" tooltip.icon="0" tooltip.isbubble="0" tooltip.maxwidth="0" tooltip.textcolor="134217751" tooltip.transparency="0" transparency="0" )

text(band=detail alignment="1" text="Fecha:" border="0" color="0" x="777" y="40" height="64" width="160" html.valueishtml="0" name=ano_t visible="1" font.face="Times New Roman" font.height="-10" font.weight="400" font.family="1" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" background.transparency="0" background.gradient.color="8421504" background.gradient.transparency="0" background.gradient.angle="0" background.brushmode="0" background.gradient.repetition.mode="0" background.gradient.repetition.count="0" background.gradient.repetition.length="100" background.gradient.focus="0" background.gradient.scale="100" background.gradient.spread="100" tooltip.backcolor="134217752" tooltip.delay.initial="0" tooltip.delay.visible="32000" tooltip.enabled="0" tooltip.hasclosebutton="0" tooltip.icon="0" tooltip.isbubble="0" tooltip.maxwidth="0" tooltip.textcolor="134217751" tooltip.transparency="0" transparency="0" )

text(band=detail alignment="0" text="Cotización oficial:" border="0" color="0" x="485" y="140" height="64" width="453" html.valueishtml="0" name=monto1_t visible="1" font.face="Times New Roman" font.height="-10" font.weight="400" font.family="1" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" background.transparency="0" background.gradient.color="8421504" background.gradient.transparency="0" background.gradient.angle="0" background.brushmode="0" background.gradient.repetition.mode="0" background.gradient.repetition.count="0" background.gradient.repetition.length="100" background.gradient.focus="0" background.gradient.scale="100" background.gradient.spread="100" tooltip.backcolor="134217752" tooltip.delay.initial="0" tooltip.delay.visible="32000" tooltip.enabled="0" tooltip.hasclosebutton="0" tooltip.icon="0" tooltip.isbubble="0" tooltip.maxwidth="0" tooltip.textcolor="134217751" tooltip.transparency="0" transparency="0" )

text(band=detail alignment="0" text="Monto de compra:" border="0" color="0" x="485" y="252" height="64" width="453" html.valueishtml="0" name=t_2 visible="1" font.face="Times New Roman" font.height="-10" font.weight="400" font.family="1" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" background.transparency="0" background.gradient.color="8421504" background.gradient.transparency="0" background.gradient.angle="0" background.brushmode="0" background.gradient.repetition.mode="0" background.gradient.repetition.count="0" background.gradient.repetition.length="100" background.gradient.focus="0" background.gradient.scale="100" background.gradient.spread="100" tooltip.backcolor="134217752" tooltip.delay.initial="0" tooltip.delay.visible="32000" tooltip.enabled="0" tooltip.hasclosebutton="0" tooltip.icon="0" tooltip.isbubble="0" tooltip.maxwidth="0" tooltip.textcolor="134217751" tooltip.transparency="0" transparency="0" )

text(band=detail alignment="0" text="Monto de venta:" border="0" color="0" x="485" y="364" height="64" width="453" html.valueishtml="0" name=t_1 visible="1" font.face="Times New Roman" font.height="-10" font.weight="400" font.family="1" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" background.transparency="0" background.gradient.color="8421504" background.gradient.transparency="0" background.gradient.angle="0" background.brushmode="0" background.gradient.repetition.mode="0" background.gradient.repetition.count="0" background.gradient.repetition.length="100" background.gradient.focus="0" background.gradient.scale="100" background.gradient.spread="100" tooltip.backcolor="134217752" tooltip.delay.initial="0" tooltip.delay.visible="32000" tooltip.enabled="0" tooltip.hasclosebutton="0" tooltip.icon="0" tooltip.isbubble="0" tooltip.maxwidth="0" tooltip.textcolor="134217751" tooltip.transparency="0" transparency="0" )

htmltable(border="1" )

htmlgen(clientevents="1" clientvalidation="1" clientcomputedfields="1" clientformatting="0" clientscriptable="0" generatejavascript="1" encodeselflinkargs="1" netscapelayers="0" pagingmethod=0 generatedddwframes="1" )

xhtmlgen() cssgen(sessionspecific="0" )

xmlgen(inline="0" )

xsltgen()

jsgen()

export.xml(headgroups="1" includewhitespace="0" metadatatype=0 savemetadata=0 )

import.xml()

export.pdf(method=0 distill.custompostscript="0" xslfop.print="0" nativepdf.customsize=0 nativepdf.customorientation=0 nativepdf.pdfstandard=0 nativepdf.useprintspec=no )

export.xhtml()



  1. Helpful
There are no comments made yet.
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Tuesday, 26 November 2019 01:46 AM UTC
  2. PowerBuilder
  3. # 4

Hi Rafael;

   Can you tell us ...

1) DBMS vendor & version

2) DB client that your using to connect;

Regards .... Chris

Comment
  1. RAFAEL RODRIGUEZ
  2. Friday, 3 January 2020 23:00 PM UTC
DBMS is SQL SERVER 2014



the DB client is SQLNCLI11



  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.