DB Resourceless SQL statement

Post any questions you have about using the Verj.io Studio, including client and server-side programming with Javascript or FPL, and integration with databases, web services etc.

Moderators: Ian, Dave, jcoulson, Jon, Wai

Segi
Ebase User
Posts: 527
Joined: Mon Dec 09, 2013 6:37 pm

DB Resourceless SQL statement

#1

Postby Segi » Mon May 14, 2018 5:58 pm

I am using this JS code to run a select without using a DB resource like this:

Code: Select all

services.database.executeSelectStatement("DB","SELECT * FROM Users",     
     function (columnData) {
     
     });
Most objects in Verj have a getter that accepts a string parameter like tables has tables.getTable("SomeTableName") or for columns you can do tables.MyTable.getColumn("SomeColumnName") or for fields you can do fields.getField("FieldName");

Is there anything like this for columnData when you don't know the column names ahead of time and want to do columnData.getColumn("SomeColumnName"); ?

Obviously, I tried columnData.getColumn("columnName") but there doesn't appear to be a method like this

columnData is of the type java.util.Map but I haven't figured out how to get the column names from it. I'm looking at https://docs.oracle.com/javase/8/docs/a ... l/Map.html and it seems that some of the Map methods from Map aren't available in Verj

I know that I can get the column names using this

Code: Select all

for (var colName in columnData) {
     alert("column name is " + colName);
}
But I don't know how to get the value based on colName
0 x

Jon
Moderator
Moderator
Posts: 1192
Joined: Wed Sep 12, 2007 12:49 pm

Re: DB Resourceless SQL statement

#2

Postby Jon » Tue May 15, 2018 7:42 am

You can get the value with columnData[colName].

Code: Select all

for (var colName in columnData) {
     log("column " + colName + " value " + columnData[colName] );
}
All Java Maps are converted to Javascript objects {} as they are transferred into the Javascript world. Syntax above is standard JS for obtaining a property value of a JS object using a variable that contains the property name.
0 x

Segi
Ebase User
Posts: 527
Joined: Mon Dec 09, 2013 6:37 pm

Re: DB Resourceless SQL statement

#3

Postby Segi » Wed May 23, 2018 11:30 pm

Jon,

Is there a way to get the data type for the column ?
0 x

Jon
Moderator
Moderator
Posts: 1192
Joined: Wed Sep 12, 2007 12:49 pm

Re: DB Resourceless SQL statement

#4

Postby Jon » Thu May 24, 2018 10:55 am

Not using services.database.executeSelectStatement().

To get the column type you need to access the ResultSetMetadata. Something like this..

Code: Select all

var con = system.getDatabaseConnection("XXX);
var stmt;
var rs;
try
{
  stmt = con.prepareStatement("SELECT * FROM Users");
  rs = stmt.executeQuery();
  
  // get ResultSetMetaData
  var meta= rs.getMetaData();

   // go through the result set 
  while (rs.next())
  {
     for (var i = 1; i <= meta.getColumnCount(); i++)
      {      
         var colName = meta.getColumnName(i);
         var colType = meta.getColumnType(i) ;
         var value = rs.getObject(i);
    }
}
finally
{
  if (rs) rs.close();
  if (stmt) stmt.close();
  if (con) con.close();
}
0 x


Who is online

Users browsing this forum: No registered users and 1 guest