Hi phil,
Thanks for your tip but I have some open questions, can you please help me a bit more.
I would like to know the syntax of the parameters for value1 and value2. new Arrray(value1, value2, ...)
Imagine we have this sql statement, what is the syntax within the @JdbcUpdate or @JdbcDbColumn., Do I have to give the column name, the value and the comparison operator likes AND and OR and so on….
UPDATE ressystem.users
SET id=?, firstname=?, lastname=?, city=?, state=?
WHERE (id = 10 and firstName = 'John')
OR (city = 'Zurich' and state = 'zh')
;
Get user input
var id = getComponent("recordId").getValue();
var givenFirstName = getComponent("firstname").getValue();
populating the array value for the sql Set.
var v = {
id: id,
firstName: givenFirstName,
lastName: "Blob",
city: "Zurich",
state: "zh"
}
My ambiguity on the syntax definition is as follow:
How you do exactly write the above where clause as a parameter to the following line of code. I have done it in many different way but none of them is correct. Here some of my trial and error version.
var myCond = new Array ("id:",id, "firstame:", givenFirstName);
var myCond2 = new Array("id = ", id , "firstname = " , givenFirstName);
var myCond3 = new Array();
myArray2[0] = "id = " + id;
myArray2[1] = " and firstname = " + "'" + givenFirstName + "'";
var c = @JdbcUpdate("postgres","ressystem.users", v , "id=?" , id , myCon );
var c = @JdbcUpdate("postgres","ressystem.users", v, myCon );
I also would like to know if we can do something like this.
SELECT u.id, u.firstname, u.lastname, u.city, u.state, u.state_id, s.state_id, s.state, s.label
FROM ressystem.users u , states s
where u.state_id = s.state_id
and u.id = 10
or s.state like '%zh%'
@JdbcDbColumn("postgres","ressystem.users","u.firstname")
@JdbcDbColumn("postgres","ressystem.users???"," u.firstname???, s.state??? ")
2. Sorry I don’t follow your answer can you give me an example please, I am new in Lotus notes world and I am learning as I go further, I am very grateful for your time and help.
<xp:this.data>
<xe:jdbcQuery connectionName="postgres"
var="jdbcData1" defaultOrderBy="id"sqlTable="ressystem.users"
clearOnRendering="true"maxBlockCount="1">
xe:jdbcQuery>
xp:this.data>
3. I have managed to see how you can pass parameter to a sql file but I have another questions.
Is it possible to do something like this example in the sql file or outside of the sql file. I would like to do the (and) part if the second parameter is not null or empty screen.
SELECT id, firstname, lastname, city, state
FROM ressystem.users
where id = ?
if (? != null || ? != “”)
and firstname = ?
My example of Paramter to a file, it does work.
<xp:this.data>
<xe:jdbcQuery connectionName="postgres"
var="jdbcData1" defaultOrderBy="ordnumber" sqlFile="RoomResource.sql"
calculateCount="true">
<xe:this.sqlParameters>
<xe:sqlParameter>
<xe:this.value>#{javascript: var locID = "";
if (viewScope.LocName){
if (viewScope.LocName.indexOf(".") != -1){
locId = (viewScope.LocName.substr(0,viewScope.LocName.indexOf(".")));
}else{
locId = (viewScope.LocName);
}
}else{
locId = 1;
}
parseFloat(locId) || ""}]]>xe:this.value>
xe:sqlParameter>
xe:this.sqlParameters>
xe:jdbcQuery>
xp:this.data>
Thanks again fro your help.
Cyrus