Ms Access frontend, PostgreSQL backend efficient INSERT INTO ... RETURNING ... statement
HI,
I'm using MS Access as a frontend to PostgreSQL database. And I have run into dead end:
How would I get return value of passthrough SQL INSERT statement?
I tried something like this:
strSQL = "INSERT INTO someTable (col1, col2) VALUES ('val1', 'val2') RETURNING col0"
'Create passtrhough query dynamically (ano开发者_运维百科ther function)
CreateSPT ("some_temp_query", strSQL)
idvalue = DLookup("col0", "some_temp_query")
Apparenty this way INSERT statement will be called 2 times :) Does anybody know what is the correct way? I have run out of ideas
Edit: I'm using latest psqlODBC drivers
Assuming you have a saved pass-though query, then you can modify the sql on the fly, and return values that way. The following code runs without you even having to declare any variables.
With CurrentDb.QueryDefs("qryPassReturn")
.SQL = "INSERT INTO someTable (col1, col2) VALUES ('val1', 'val2') RETURNING col0"
Debug.Print .OpenRecordset()(0)
End With
I suppose you could declare a reocrdset to receive the return value, and go:
Dim rstReturn As DAO.Recordset
With CurrentDb.QueryDefs("qryPassReturn")
.SQL = "INSERT INTO someTable (col1, col2) VALUES ('val1', 'val2') RETURNING col0"
Set rstReturn = .OpenRecordset
End With
Debug.Print rstReturn(0)
So, you can just pass the sql, and the values returned will appear as a standard reocrdset. So, the first example should run fine and note how I not even declared one varialbe to make this work. You as mentioned simply need a saved pass-though query.
Seems that function below does the trick... Not sure about performance though :)
'Insert-SQL passthroug
'Pass here all the insert statements
Function ISPT(SQLString)
Dim strODBCString As String
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim result As Long
strODBCString = _
"DRIVER={PostgreSQL Unicode};DATABASE=" & settings_getDBName() & _
";SERVER=" & settings_getServerAddress & _
";PORT=" & settings_getServerPort & _
settings_getConnStrParams
cn.Open strODBCString
rs.Open SQLString, cn
'return value
result = rs.Fields(0).Value
'Cleanup
If rs.state <> adStateClosed Then rs.Close
Set rs = Nothing
If cn.state <> adStateClosed Then cn.Close
Set cn = Nothing
ISPT = result
End Function
精彩评论