Updating Query Column value in CF

Hi,


I faced a  problem while updating a Date value in in Query Object.

I had one task to change date of one column by checking Date value of other column of same query object.

For updating Query Column value most of people use javacast(type,variable) function.

Accepting types in javaCast are
bigdecimal,boolean,byte,char,double,byte,char,double,float,int,long,null,short and string

These are all from java.lang class. But there is no Type for date or datetime

I found there are three date classes used in CF (that can be more).

In This is code snippet , By making Query object adding cell and values and updating values.

 <cfset VARIABLES.user = queryNew('SENDDATE,AGE','date,Integer')>  
 <cfset VARIABLES.userDate1 = createDate(year(now()),month(now()),day(now())-2)>  
 <cfset VARIABLES.userDate2 = createDate(year(now()),month(now()),day(now())-3)>  
 <cfset VARIABLES.userDate3 = createDate(year(now()),month(now()),day(now())-4)>  
 <cfset VARIABLES.t = queryAddRow(VARIABLES.user,1)>  
 <cfset QuerySetCell(VARIABLES.user,'SENDDATE',VARIABLES.userDate1)>  
 <cfset QuerySetCell(VARIABLES.user,'AGE',21)>  
 <cfset VARIABLES.t = queryAddRow(VARIABLES.user,1)>  
 <cfset QuerySetCell(VARIABLES.user,'SENDDATE',VARIABLES.userDate3)>  
 <cfset QuerySetCell(VARIABLES.user,'AGE',22)>  
 <cfdump var="#VARIABLES.user#" >  
 <cfloop query="VARIABLES.user">  
      <!---Sample of updating a column value --->  
      <cfset VARIABLES.user['age'][VARIABLES.user.currentRow] = VARIABLES.user.age +1>  
      <!---We can do javacast also for this --->  
      <cfset VARIABLES.user['age'][VARIABLES.user.currentRow] = javacast("int",VARIABLES.user.age +1)>  
      <!---We can update date by normal assigning --->  
      <cfset VARIABLES.user [ 'SENDDATE' ][ VARIABLES.user.currentRow ] = createDate(year(now()),month(now()),day(now())-10) >  
 </cfloop>  
 <cfoutput >  
           #getMetadata(VARIABLES.user.SENDDATE).getName()#</br>  
           #getMetadata(VARIABLES.user.AGE).getName()#</br>  
 </cfoutput>  
 <cfdump var="#VARIABLES.user#" >  

Here is the second one where getting result by Executing Query.

 <cfquery name="VARIABLES.activity" datasource="rsa">  
 SELECT top 1 DATEADD,LISTIDS,EMAIL_ID FROM emaillist WHERE DATEADD IS NOT NULL       
 </cfquery>  
 <cfdump var="#VARIABLES.activity#" >  
 <!---Sample of updating a column value of Query Object we can use javaCast(type,variable) only for Email_ID or ListIDS here  --->  
 <cfset VARIABLES.activity['EMAIL_ID'][VARIABLES.activity.currentRow] = VARIABLES.activity.EMAIL_ID +5>  
 <cfset VARIABLES.activity['DATEADD'][VARIABLES.activity.currentRow] = createDate(year(now()),month(now()),day(now())-10)>  
 <cfdump var="#VARIABLES.activity#" >  

The normal CF date

 <cfset VARIABLES.now = now()>  
 <cfoutput >  
 In Normal CF:<br/>   
      #getMetadata(VARIABLES.now).getName()#<br/>  
 </cfoutput>  

As you can see there are 3 Date class in above.

1) The Query Object Created and added Date from CF is java.sql.Date.
2) The Query Object got from SQL server is  java.sql.Timestamp
3) The normal Date in CF is coldfusion.runtime.OleDateTime

We can update using just by assigning value. If we use javaCast it will make us confuse.

Note: 
QOQ-Query of Query(DBTYPE='QUERY') can't be used on the updated Query object

CFSELECT usage

Hi,

By using <CFSELECT> we can save time. With normal <select> with CF code will be time taking and we have to write lines of code to achieve little things also.

Some points to be noted while using CFSELECT

1.  CFSELECT should be inside FORM/CFFORM tag. Else it gives Context Validation error.
2. When 'query' attribute used we should only have query column in 'value' attribute
3. When 'Bind' attribute is used. Send CFC method result as JSON format or as 2 dimensional array. Have securejson="false" returnformat="json" attributes in method.
4. Have 'value' or 'display' attribute while using ''bind'.
5. You can't add <CFSELECT> through javascript/jQuery inside form. It will give context validation error.
6. Use #(condition check)# instead of  <CFIF> in attributes.

Displaying query:

 <cfquery name="clients" datasource="bsa">  
           SELECT cCompanyName,clientID FROM CLIENTS  
 </cfquery>  
 <cfselect name="clientCF1" multiple="true" query="CLIENTS" display="cCompanyName" message="Please select one value" group="cCompanyName" value="clientID" selected=#v#>  
 </cfselect>  

In normal Select we have to use <CFLOOP> and go through and display in <option>.Group will form group of options under that label. In normal select we have to use

 <optgroup label=''></optgroup>  

Bind CFC:

 <cfselect name="clientCF2" multiple="true" message="One of the value should be selected" required="true"   
           bind="url:bindFc.cfc?method=getClients" bindonload="true" display="cCompanyName" group="cCompanyName" value='clientID'>    
 </cfselect>       

In CFC

 <cfcomponent>   
  <cffunction name="getClients" access="remote" securejson="false" returnformat="json">  
     <cfquery name="clients" datasource="bsa">  
                SELECT cCompanyName,clientID FROM CLIENTS  
           </cfquery>  
           <cfreturn clients>  
   </cffunction>   
 </cfcomponent>  

We can bind the value in realtime without using ajax.

 <cfselect name="clientCF3" multiple="true" message="One of the value should be selected" required="true"   
           bind="url:bindFc1.cfc?method=getClientsByID&id={clientCF2}" display="cCompanyName" group="cCompanyName" value='clientID'>  
      </cfselect>       

In CFC

 <cfcomponent>  
 <cffunction name="getClientsByID" access="remote" securejson="false" returnformat="json">  
           <cfargument name="id">  
     <cfquery name="clients" datasource="bsa">  
                SELECT cCompanyName,clientID FROM CLIENTS WHERE clientID=<cfqueryparam value="#arguments.id#" cfsqltype="cf_sql_bigint" >  
           </cfquery>  
           <cfreturn clients>  
   </cffunction>   
 </cfcomponent>  

Try to use CFSELECT instead of normal SELECT where ever possible.

Thanks,

ODBC Driver Creation and CSV Import

ODBC Drivers which provides a universal data access interface. With ODBC, application developers can allow an application to concurrently access, view, and modify data from multiple, diverse databases.

There are many ODBC drivers in MS Acess, they will come up with new drivers for others shortly. I will explain basic (*.txt,*csv) Drivers.

We need schema.ini file which tells the column names, character set and Header reqired.

schema.ini

It usually contains

 CharacterSet=OEM  
 Col1=Group Char Width 30  
 Col2=ID Integer 

More info you can get in : https://msdn.microsoft.com/en-us/library/ms709353(v=vs.85).aspx

To set Up ODBC DSN:

Control Panel->Administrative Tools->Data Source

Click On 'Finish' give name for ODBC drive and select directory where you have 'schema.ini'.

To Set Up ODBC DSN in CF

Data & Services-> Data Sources->Add New Data Source

Give name for DSN and select ODBC socket from Drop down.

After adding DSN.

You will see the ODBC name which is set in the MS Acess before. Submit and DSN is ready to use.

To Read CSV from ODBC Driver

 <cfscript>  
      queryService = new Query();  
                queryService.setDataSource('csv');  
                queryService.setName("GetData");  
                queryService.setSql("SELECT * FROM sample.csv");  
                GetData = queryService.execute().getResult();  
 </cfscript>  
 <cfdump var="#GetData#" >  

You can get contents of CSV file into a Query object by using this.

File Header in column name and values under it as contents.

You can read text files also First Line will be column name. Subsequent lines will be values under it as contents.

In CF file processing is taken care by ColdFusion10/cfusion/CustomTags/com/adobe/coldfusion/base.cfc

We can easily read CSV files and make it into Query object.

0x8007052e error In IIS

Hi,

 I got to resolve '0x8007052e' error. This error was coming only to virtual directories in IIS.

Normally reason for this type of error will be changing of Admin password of your system.

So how to resolve this error.

IIS Manger->Default Website->[Directory Name]

Change password with newly changed Admin password

Now you can run your file with out getting any error.

Note: If Admin passwords are used in CF admin data source and SQL. You have to change that also to use Datasource.

SMPP & CF

SMPP(Short Message Peer-to-Peer) protocol. The primary use of SMPP is to send and receive medium-to-high volumes of SMS texts.

The protocol is based on pairs of request/response PDUs (protocol data units, or packets) exchanged over OSI layer 4 (TCP session or X.25 SVC3) connections.

SMSC(SMS Centre) providers use both HTTP and SMPP for sending and receiving SMS texts.

• Advantages of SMPP over HTTP
• High-volume SMS messaging with a high speed. 
• Delivery report with much info.
• Network related info.
• Efficient use of PDU over HTTP post.

Setting up SMPP event gateway in CF admin

For setting up Event Gateway we need a cfc file to receive acknowledgements/messages and a cfg file having configuration of port and shortcode of SMSC.

Go to 

CF ADMIN>Event Gateways > Gateway Instances

The major function of the cfc are:

onIncomingMessage:Every incoming message from SMSC and Delivery ack is handled by this function.Signature : public  function onIncomingMessage(required struct CFEvent)
Note :We can have other functionalities in CFC for logging and fetching the information.  

The CFG file should contain :

# Type of binding.Value can be either transciever,transmitter or receiverbinding=transciever
# This is the IP address of SMSCip-address=102.208.328.100
# Port to bind toport=80001
# Your system idsystem-id= demo_99647878
# Your passwordpassword= MinDFire6824

Note : Port differs depending on Shortcode.

Sending and Recieving Messages in SMPP

We need to buy a ShortCode  from SMSC, and get configuration details for ShortCode. CFC and CFG files are configured in cfadmin as above. We will use 'SUBMIT' and 'SUBMITMULTI' commands for individual and group broadcasts respectively.

Demo  for Sending and receiving messages

send.cfm

 <cftry>  
   <cfscript>  
      VARIABLES.cellNumList ='1234567890'; //CellNumber list  
      VARIABLES.msg = structNew();  
      VARIABLES.msg.command = "submit";  
      VARIABLES.msg.destAddress = VARIABLES.cellNumList;  
      VARIABLES.msg.shortMessage = 'Test Message from SMPP';  
      VARIABLES.msg.alertOnMsgDelivery = "1"; //To get delivey ack  
      VARIABLES.msg.sourceAddress = '1234'; //Shortcode Registered  
      VARIABLES.msg.registeredDelivery = "1";  
      VARIABLES.result = sendGatewayMessage("SMS Menu App", VARIABLES.msg);  
   </cfscript>  
   <cfoutput>  
    <!---We will get orderno, if success else empty response --->  
    Result - #InputBaseN( VARIABLES.result , 16 )#  
   </cfoutput>  
 <cfcatch type="any" >  
   <cfoutput>Oops!! Something went wrong #CFCATCH.message#</cfoutput>  
 </cfcatch>  
 </cftry>  

recieve.cfc

component  hint="SMS Event Gateway CFC" output="false"

{
 /**
 @hint Standard Message From CF server
 @Displayname onIncomingMessage
 @output false
 */
 public  function onIncomingMessage(required struct CFEvent)
 {
   try
   {
  var data = ARGUMENTS.CFEvent.DATA;
 /* If the message came from a handset and Operator,Network info is there */
  if(isDefined("data.data.optionalParameters")){
    writeLog(text='Incoming handset message from #data.data.sourceAddress# message is #data.data.Message#',file='incomingMessageLog'); 
    }
 /* When message doesnt contains optional parameter, only delivery notification*/
  else{
    writeLog(text='Delivary ack from #data.data.sourceAddress# and status is #data.data.message#',file='incomingMessageLog');
   }
  }
  catch(any ex)
  {
   /* Log the return Value into the Log file*/
    writeLog(text='Oops!! Something went wrong #CFCATCH.message#',file='incomingMessageLog');
  }
  
 } 
}

PDU(Protocol Data Unit)'s of SMPP

SUBMIT_SM:
This is used in individual broadcast of SMS.

SUBMIT_MULTI:
This is used in Group broadcast of SMS.

PDU of Received Acknowledgement:

This is the PDU we get in recieve.cfc after SMS delivered to user's cell number. We can get Info about the delivered time, status and error.

PDU of Received Message:

This is the PDU we get when the Shortcode assigned receives a message from User. We will have 'Message' which is texted. We can decrypt 'optionalParameters' for getting vendor info and other info related to Wireless Network .

Resource:

http://docs.nimta.com/SMPP_v3_4_Issue1_2.pdf
http://www.activexperts.com/sms-component/smpp-specifications/introduction/
http://help.adobe.com/livedocs/coldfusion/8/htmldocs/help.html?content=UseSMSGateway_06.html
http://help.adobe.com/livedocs/coldfusion/8/htmldocs/help.html?content=gateways_79.html#1172422

Sending List to Stored Procedure in CF

In CF we can acess stored procedures through <cfstoredproc> and we give param values in <cfprocparam> tags. We get some situations of passing list in <cfprocparam> as we don't have 'list' attribute in it. For sending list and acessing list in Stored procedures we have to use functions of SQL which is quite complex and time consuming.

We can make work around to achieve this using CF and Dynamic query in Stored procedures.
For passing Integer list value:
In CFM side

   <cfset VARIABLES.listValue = "1,2,3,4,5,6,7,8,9,10">  
 <cfstoredproc procedure="getClients" datasource="practise" >   
 <cfprocparam variable="ids" cfsqltype="cf_sql_VARCHAR" value=#VARIABLES.listValue# > <cfprocresult name="VARIABLES.getClients"></cfstoredproc>  
 <cfdump var="#VARIABLES.getClients#" > 
  

In SQL side

CREATE PROCEDURE [dbo].[getClients]  
 @ids varchar(255)  
 AS  
 BEGIN  
 declare @sql varchar(max)  
   set @sql = 'select * FROM Clients where id IN ('+@ids+')'  
   EXEC(@sql)  
 END  

For passing String list value:

In CFM side

 <cfset VARIABLES.listValue = 'ram,krishna,ramki'>  
 <cfset VARIABLES.listValue = replace(VARIABLES.listValue, ",", "','", 'all')>  
 <cfstoredproc procedure="getClients" datasource="practise">  
  <cfprocparam variable="ids" cfsqltype="cf_sql_VARCHAR" value=#VARIABLES.listValue#>  
  <cfprocresult name="VARIABLES.getClients">  
 </cfstoredproc>  
 <cfdump var="#VARIABLES.getClients#">  

In SQL side

 CREATE PROCEDURE [dbo].[getClients]  
 -- Add the parameters for the stored procedure here  
 @names varchar(255)  
 AS  
 BEGIN  
 declare @sql varchar(max)  
   set @sql = 'select * FROM Clients where companyName IN ('''+ @names +''')'  
 EXEC(@sql)  
 END