sp_helptext, briefly

Case sensitive

No quote needed

verify SingleConnectionDataSource - sybase

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.SQLException;

 

import javax.sql.DataSource;

 

import org.apache.commons.logging.Log;

import org.apache.commons.logging.LogFactory;

import org.springframework.beans.BeansException;

import org.springframework.context.support.AbstractApplicationContext;

import org.springframework.context.support.ClassPathXmlApplicationContext;

import org.springframework.jdbc.core.JdbcTemplate;

import org.springframework.jdbc.core.RowMapper;

import org.springframework.jdbc.datasource.SingleConnectionDataSource;

 

/**

*

 * @author btan

*

 */

public class SybaseUtil {

      private static final Log log = LogFactory

                  .getLog(SybaseUtil.class.getName());

 

      static private JdbcTemplate singleConnectionHandle;

      static private JdbcTemplate handle;

 

      /**

      * @param args

      */

      public static void main(String[] args) {

            showServer();

            log("abc 'd' ");

      }

 

      static public JdbcTemplate getHandle() {

            if (singleConnectionHandle != null && handle != null)

                  return handle;

            AbstractApplicationContext context = TradeEngineMain.getContext();

            if (context == null) {

                  try {

                        context = new ClassPathXmlApplicationContext(

                                    "applicationContext.xml");

                  } catch (BeansException e) {

                        e.printStackTrace();

                        context = new ClassPathXmlApplicationContext("dataSources.xml");

                  }

            }

            DataSource dataSource = (DataSource) context.getBean("mtsDataSource");

            handle = new GenericProcedureCaller(dataSource);

 

            try {

                  dataSource = new SingleConnectionDataSource(

                              dataSource.getConnection(), true);

            } catch (SQLException e) {

                  e.printStackTrace();

                  throw new RuntimeException(e);

            }

            JdbcTemplate jt = new JdbcTemplate(dataSource);

            jt = new GenericProcedureCaller(jt);

            singleConnectionHandle = jt;

            return handle;

      }

 

      static public JdbcTemplate getSingleHandle() {

            getHandle();

            return singleConnectionHandle;

      }

 

      static public String showServer() {

            JdbcTemplate handle = getHandle();

            StringBuilder ret = new StringBuilder("\n\n\t == SybaseSybase Details ==");

            ret.append("\n\t " + handle.queryForObject("select @@servername + ' <-- ' + @@version", String.class));

            ret.append("\n\t " + getConnectionDetails());

            ret.append("\n\t " + getSpidDetails());

            ret.append("\n\n");

            log.debug(ret);

            return ret.toString();

      }

      static private String getSpidDetails() {

            int spid = getSingleHandle().queryForInt("sel" +

                        "ect @@spid");

            String sql = String.format("sp_who '%s'", spid);

            String ret ="spid=" + spid + " ";

            ret += getSingleHandle().queryForObject(sql, new RowMapper<String>() {;

                  public String mapRow(ResultSet rs, int rowNum) throws SQLException {

                        return rs.getString("loginame") + " " + rs.getString("hostname");

                  }

            });

            System.err.println(ret);

            return ret;

      }

 

      static private String getConnectionDetails() {

            Connection conn3 = null;

            try {

                  conn3 = getSingleHandle().getDataSource().getConnection();

            } catch (SQLException e) {

                  throw new RuntimeException(e);

            }

            String ret = System.identityHashCode(conn3) +"=identityHash " + conn3;

            System.err.println(ret);

            return ret;

      }

 

      static public void log(String s) {

            if (s == null || s.trim().isEmpty())

                  return;

            s = s.replaceAll("'", "");

            String insert = String.format("insert snoop (charp1) select '%s'", s);

            getSingleHandle().update(insert);

            showServer();

      }

}

---

This message w/attachments (message) is intended solely for the use of the intended recipient(s) and may contain information that is privileged, confidential or proprietary. If you are not an intended recipient, please notify the sender, and then please delete and destroy all copies and attachments, and be advised that any review or dissemination of, or the taking of any action in reliance on, the information contained in or attached to this message is prohibited.
Unless specifically indicated, this message is not an offer to sell or a solicitation of any investment products or other financial product or service, an official confirmation of any transaction, or an official statement of Sender. Subject to applicable law, Sender may intercept, monitor, review and retain e-communications (EC) traveling through its networks/systems and may produce any such EC to regulators, law enforcement, in litigation and as required by law.
The laws of the country of each sender/recipient may impact the handling of EC, and EC may be archived, supervised and produced in countries other than the country in which you are located. This message cannot be guaranteed to be secure or free of errors or viruses.

References to "Sender" are references to any subsidiary of Bank of America Corporation. Securities and Insurance Products: * Are Not FDIC Insured * Are Not Bank Guaranteed * May Lose Value * Are Not a Bank Deposit * Are Not a Condition to Any Banking Service or Activity * Are Not Insured by Any Federal Government Agency. Attachments that are part of this EC may have additional important disclosures and disclaimers, which you should read. This message is subject to terms available at the following link:
http://www.bankofamerica.com/emaildisclaimer. By messaging with Sender you consent to the foregoing.

sybase open client, open server, DB-library

http://manuals.sybase.com/onlinebooks/group-cnarc/cng1110e/ctref/@Generic__BookTextView/292;pt=294#X explains

The Sybase product line includes 1) servers and 2) tools for building servers:

• SQL Server is a database server. SQL Servers manage information stored in one or more databases.
• Open Server provides the tools and interfaces needed to create a custom server application.

SQL Server and Open Server applications are similar in some ways ...But they also differ:

• An application programmer must create an Open Server application, using Server-Library's building blocks and supplying custom code. SQL Server is complete and does not require custom code.

Open Client provides two core programming interfaces: Client-Library and DB-Library

• Open Client Client-Library supports server-managed cursors and other new features that were added to the System 10 product line.
• Open Client DB-Library provides support for older Open Client applications, and is a completely separate programming interface from Client-Library. DB-Library is documented in the Open Client DB-Library/C Reference Manual.

Client-Library programs also depend on CS-Library, which provides routines that are used in both Client-Library and Server-Library applications.

sybase - number of user connection

1> sp_configure 'number of user connection'

2> go

 Parameter Name                 Default     Memory Used Config Value Run Value   Unit                 Type      

 ------------------------------ ----------- ----------- ------------ ----------- -------------------- ----------

 number of user connections              25     1990886        1700         1700 number               dynamic   

 

 

1> sp_monitorconfig 'number of user connection'

2> go

Usage information at date and time: Dec  2 2010 10:35AM.

 

 Name                      Num_free    Num_active  Pct_act Max_Used    Num_Reuse  

 ------------------------- ----------- ----------- ------- ----------- -----------

 number of user connection         376        1324  77.88         1363           0

 

sybase table size

sp__rowcount yourTable  -- not always there

sp_spaceused yourTable

sybase c++ driver used on wall street

http://manuals.sybase.com/onlinebooks/group-cnarc/cng1110e/dblib/@Generic__BookView

• An application can call a stored procedure in two ways: by executing a command buffer containing a Transact-SQL execute statement or by making a remote procedure call (RPC).
• Remote procedure calls have a few advantages over execute statements:
  • An RPC passes the stored procedure's parameters in their native datatypes, in contrast to the execute statement, which passes parameters as ASCII characters. Therefore, the RPC method is faster and usually more compact than the execute statement, because it does not require either the application program or the server to convert between native datatypes and their ASCII equivalents.
  • It is simpler and faster to accommodate stored procedure return parameters with an RPC, instead of an execute statement. With an RPC, the return parameters are automatically available to the application. (Note, however, that a return parameter must be specified as such when it is originally added to the RPC via the dbrpcparam routine.) If, on the other hand, a stored procedure is called with an execute statement, the return parameter values are available only if the command batch containing the execute statement uses local variables, not constants, as the return parameters. This involves additional parsing each time the command batch is executed.
• To make a remote procedure call, first call dbrpcinit to specify the stored procedure that is to be invoked. Then call dbrpcparam once for each of the stored procedure's parameters. Finally, call dbrpcsend to signify the end of the parameter list. This causes the server to begin executing the specified procedure. You can then call dbsqlok, dbresults, and dbnextrow to process the stored procedure's results. (Note that you will need to call dbresults multiple times if the stored procedure contains more than one select statement.) After all of the stored procedure's results have been processed, you can call the routines that process return parameters and status numbers, such as dbretdata and dbretstatus.
• If the procedure being executed resides on a server other than the one to which the application is directly connected, commands executed within the procedure cannot be rolled back.
• For an example of a remote procedure call, see Example 8 in the online sample programs.