ISTech Support Forum

ISTech Support Forum
http://www.istechforum.com/YaBB.pl
Crystal Reports, ODBC & Access >> ODBC General Issues >> Writing in PSQL
http://www.istechforum.com/YaBB.pl?num=1174397603

Message started by GasGiant on 03/20/07 at 05:33:22

Title: Writing in PSQL
Post by GasGiant on 03/20/07 at 05:33:22

Yeah, so I'm merrily programming my little heart out and I've come to the problem of INSERTing things into PSQL tables. Getting the right number of fields in the INSERT was the first challenge, but that didn't take too long. The ODBC map doesn't match with the table view in Maintain Database (TAS-D), but I knew that. Then I chased SQL Error 22005, data type mismatch. I squashed those, but now I'm stumped. It won't take my dates, no matter how I format them.

I'm looking for a guide to writing INSERT statements for PSQL. Anyone know of such a thing? The SQL Data Manager "help" file is not any.

Title: Re: Writing in PSQL
Post by kevind on 03/20/07 at 07:14:26

INSERT into {tablename} values ('yyyy-mm-dd')

The 'mm' and 'dd' must always be two digits and single quotes must enclose the date string.

Hope this helps.

Title: Re: Writing in PSQL
Post by GasGiant on 03/20/07 at 07:25:37

Thanks for the reply. Turned out that my problem was a misordering of fields, not the format problem. However, in looking for an answer I found out that I can give PSQL the keyword CURDATE and it will do the right thing. Saves me a step and eliminates a variable in my program. Cool.

Title: Re: Writing in PSQL
Post by Kelloggs on 03/21/07 at 09:41:47

Here is a good example. It is a MS Acces function, but the insert script is there

'INSERT QUANTITIES ON SHIPPING PSQL DATABASE
sqlString = "INSERT INTO PS_SOL ( so_type, so_wopre, so_wosuf, so_line, dba_line1, dba_line2, partid, pdesc, shipper, qty, bo, shp, shp_date, shp_time, shp_status) VALUES ( 'WO', " & WOPRE & "," & WOSUF & ", " & SOLINE & ", '" & LN1 & "', '" & LN2 & "' ,'" & PCODE & "', '" & PDESC & "', '" & dhGetUserName() & "'," & QTY & "," & NBO & "," & TSHP & ", CURDATE ( ), CURTIME ( ), '" & SSta & "')"
INSERT_SHP (sqlString)
'Do SO Status
'Find if SO is there already
sqlString = "SELECT count(*) as Exs FROM PS_SO where so_wopre = " & WOPRE & " AND so_wosuf = " & WOSUF
CurrentDb.QueryDefs("Server_PS_SO").SQL = sqlString
If DLookup("[Exs]", "Server_PS_SO") = 0 Then
If DLookup("[FSHP]", "Sys_SO_Status") = 0 Then
SoStat = "Fully Shipped"
Else
SoStat = "Partially Shipped"
End If
sqlString = "INSERT INTO PS_SO ( so_wopre, so_wosuf, so_type, so_status, so_date, custid, customer, custorder, jobnum, so_desc ) VALUES ( " & WOPRE & "," & WOSUF & ", 'WO','" & SoStat & "', CURDATE(),'" & CUSCOD & "','" & CUSNME & "','" & CUSORD & "','" & JOBNUM & "','" & DESC & "')"
INSERT_SHP (sqlString)
Else
If DLookup("[FSHP]", "Sys_SO_Status") = 0 Then
SoStat = "Fully Shipped"
Else
SoStat = "Partially Shipped"
End If
sqlString = "Update PS_SO Set so_status = '" & SoStat & "', so_date = CURDATE() where so_wopre = " & WOPRE & " AND so_wosuf = " & WOSUF
INSERT_SHP (sqlString)
End If