q1:
I am looking for a more direct way to get an array from getdatasetbyquery than using getcolumn/row as array for each field, ie get an array from the entire query, not row by by row or colmun by column. shall i do my own function, or did I miss the built in way to do it?
q2:
what is the fastest way to import data from a sql db (this db can’t be used directly, it has no primary key in its tables (db2))
- sql query to get data, then controller.newrecord, then field1=… field2=…
or
- sql query to get data > array and another sql query (insert) to internal table to enter all fields at once?
or
combined sql insert in internal table from select (external table)
Q1: I guess you want to generate a multidimensional Array? like:
[
[row1value1, row1value2],
[row2value1, row2value2]
]
?
You can create a simple method for that yourself: create an empty array, loop over all rows in the dataset and get the the row as an array and insert it into the array you created.
Q2: If the table has a combination of columns that make the row uniquely identifiable, you can still use it directly: just mark the column(s) in the TableEditor as “row_ident” in the “Row Ident” column of the TableEditor.
If that doesn’t work for you, the quickest way would be to do a getDataSetbyQuery and convert it to a DataSource. Then Servoy will handle all the inserts for you.
Paul
Q1:
if you have a dataset then for example in servoy 5 you can do this to get the value:
var value = ds[rowindex][columnindex]
this only works for getting values, its not a write through value to the dataset so this wont update the dataset:
ds[rowindex][columnindex] = value
thanks for the quick reply.
1
so I did not miss anything, I must do it on my own.
2
I’ll try but I am read only on this db (db2, the dream, only works in odbc mode, direct jdbc not accepted for an unknown reason, seems that the support people of this part only know the 5250 terminal to query this db, hopeless…), so I can’t alter field definitions.
and about jcompagner comment, I wanted a bi-directional array with field names, not indexes, as the dataset contains field names when redirected to output, but obviously there is no way around (actually, I was looking for a way similar to what you can do in php, get an array from the query resultà.
thanks for your help anyway.
I’ll try but I am read only on this db (db2, the dream, only works in odbc mode, direct jdbc not accepted for an unknown reason, seems that the support people of this part only know the 5250 terminal to query this db, hopeless…), so I can’t alter field definitions.
You don’t have to alter the database for this, you can set the row_ident value in the Table Editor in Servoy Developer. It’s meta data stored only in Servoy, it’s not updating anything in the database.
Paul
lesouef:
and about jcompagner comment, I wanted a bi-directional array with field names, not indexes, as the dataset contains field names when redirected to output, but obviously there is no way around (actually, I was looking for a way similar to what you can do in php, get an array from the query resultà.
thanks for your help anyway.
var value = ds[rowindex][fieldname];
also works fine.
Only not for writing to the array only reading.
paul,
did the job perfect, I can use it now as any sql table.
if you ever meet a jdbc to db2 expert, I am interested on what to tell the as400 guys to let me in!
so far jdbc is reporting the db connection is refused though the odbc connection works on it.
I have posted a question in the db2 area of experts-exchange.com where I have an account, but no answer yet.
lesouef:
paul,
did the job perfect, I can use it now as any sql table.
if you ever meet a jdbc to db2 expert, I am interested on what to tell the as400 guys to let me in!
so far jdbc is reporting the db connection is refused though the odbc connection works on it.
I have posted a question in the db2 area of experts-exchange.com where I have an account, but no answer yet.
I’m connecting to DB2-AS/400 without problems using jdbc, here’s how the connections are configured:
URL: jdbc:as400:lf;prompt=false
Driver: com.ibm.as400.access.AS400JDBCDriver
where “as400” is the dns name of the server and “lf” is the name of the file. Remember to select the correct schema as well.
Hope this helps.
which driver is this? IBM? universal? from Client Access? I am using db2jcc.jar from the IBM site…
But are you trying to access DB2 on an AS/400 machine or DB2 on a different OS?
I’m going to check the driver name and let you know.
The driver we are using is JT400.
db2 on an as400, os=os400 V5r3
will try your driver and let you know
jt400, no good. I just wonder how you can get connected to a machine without the ip address in the URL? is “lf” a machine name declared in your DNS?
also, is this supposed to work on any as400 or are there options to be enabled/installed ? all I can get OK is the ODBC connection using the client access driver. but this behaves slowly and I get strange a behaviour on search operations
Hi Lesouef,
Have you tried the following URL ?
jdbc::;prompt=false
“lf” is the db name (they call them files), “as400” is the dns name registered in the internal dns server, just replace it with the ip address. As far as I know all we had to do was to put the driver in servoy drivers folder and connect. What error do you exactly receive? Is there anything logged on the AS/400?
Note: edited to fix a mistake
yes, url is:
jdbc:192.168.2.61:ERAVF;prompt=false
returns “driver does not recognize the URL”
it seems that the correct URL for jt400 is:
jdbc:as400:ip_address:database;prompt=false
at least this is how I have been able to connect, but I can’t get the tables list and it is incredibly slow.