changes in sql table driver

[Andrew Makhorin]

Hi Xypron,

Is it possible to make changes in the sql table driver in order to
concatenate some arguments (marked in some way) to construct the
resulting sql statement? This would allow processing sql statements
which are longer than 100 chars without changes in the language and
in the translator. Thanks.


Andrew Makhorin



_______________________________________________
Help-glpk mailing list
Help-glpk@...
http://lists.gnu.org/mailman/listinfo/help-glpk

[Andrew Makhorin]

> Is it possible to make changes in the sql table driver in order to
> concatenate some arguments (marked in some way) to construct the
> resulting sql statement?

Something like this, where a trailing backslash (or any other
appropriate character) in the symbolic value means concatenation:

table investmentTable {(fe, oe, ift, it) in investmentEntity,
   p in periodFlow, scen in scenario} OUT odbcDriver odbcConnectString
      "UPDATE tModInvestmentPeriodSet SET \"
      "tModInvestmentPeriodSet.openingBalanceVar = ?, \"
      "tModInvestmentPeriodSet.investmentFromToVar = ?, \"
      "tModInvestmentPeriodSet.investmentCapitalGainVar = ?, \"
      "tModInvestmentPeriodSet.investmentDividendsVar = ?, \"
      "tModInvestmentPeriodSet.investmentInterestVar = ?, \"
      "tModInvestmentPeriodSet.investmentGrowthVar = ?, \"
      "tModInvestmentPeriodSet.closingBalanceVar = ?  \"
      "WHERE  tModInvestmentPeriodSet.scenarioId = ? AND \"
             "tModInvestmentPeriodSet.fundEntityId = ? AND \"
             "tModInvestmentPeriodSet.investmentTypeId = ? AND \"
             "tModInvestmentPeriodSet.periodId = ?" :
      investmentEntityBalance[fe, oe, ift, it, p],
      invFromToAmount[fe, oe, ift, it, p],
      investmentCapitalGain[fe, oe, ift, it, p],
      investmentReturn[fe, oe, ift, it, "DividendEligible", p],
      investmentReturn[fe, oe, ift, it, "Interest", p],
      investmentReturn[fe, oe, ift, it, "Growth", p],
      investmentEntityBalance[fe, oe, ift, it, p+1],
      pValue[scen], fe, ift, p;





_______________________________________________
Help-glpk mailing list
Help-glpk@...
http://lists.gnu.org/mailman/listinfo/help-glpk

[xypron]

Hello Andrew,

I will provide a patch within the next week.

Best regards

Xypron
-------- Original-Nachricht --------
> Datum: Thu, 5 Nov 2009 23:17:25 +0300
> Von: Andrew Makhorin
> An: help-glpk@...
> Betreff: [Help-glpk] Re: changes in sql table driver

> > Is it possible to make changes in the sql table driver in order to
> > concatenate some arguments (marked in some way) to construct the
> > resulting sql statement?
>
> Something like this, where a trailing backslash (or any other
> appropriate character) in the symbolic value means concatenation:
>
> table investmentTable {(fe, oe, ift, it) in investmentEntity,
>    p in periodFlow, scen in scenario} OUT odbcDriver odbcConnectString
>       "UPDATE tModInvestmentPeriodSet SET \"
>       "tModInvestmentPeriodSet.openingBalanceVar = ?, \"
>       "tModInvestmentPeriodSet.investmentFromToVar = ?, \"
>       "tModInvestmentPeriodSet.investmentCapitalGainVar = ?, \"
>       "tModInvestmentPeriodSet.investmentDividendsVar = ?, \"
>       "tModInvestmentPeriodSet.investmentInterestVar = ?, \"
>       "tModInvestmentPeriodSet.investmentGrowthVar = ?, \"
>       "tModInvestmentPeriodSet.closingBalanceVar = ?  \"
>       "WHERE  tModInvestmentPeriodSet.scenarioId = ? AND \"
>              "tModInvestmentPeriodSet.fundEntityId = ? AND \"
>              "tModInvestmentPeriodSet.investmentTypeId = ? AND \"
>              "tModInvestmentPeriodSet.periodId = ?" :
>       investmentEntityBalance[fe, oe, ift, it, p],
>       invFromToAmount[fe, oe, ift, it, p],
>       investmentCapitalGain[fe, oe, ift, it, p],
>       investmentReturn[fe, oe, ift, it, "DividendEligible", p],
>       investmentReturn[fe, oe, ift, it, "Interest", p],
>       investmentReturn[fe, oe, ift, it, "Growth", p],
>       investmentEntityBalance[fe, oe, ift, it, p+1],
>       pValue[scen], fe, ift, p;
>
>
>
>
>
> _______________________________________________
> Help-glpk mailing list
> Help-glpk@...
> http://lists.gnu.org/mailman/listinfo/help-glpk

--
Jetzt kostenlos herunterladen: Internet Explorer 8 und Mozilla Firefox 3.5 -
sicherer, schneller und einfacher! http://portal.gmx.net/de/go/atbrowser


_______________________________________________
Help-glpk mailing list
Help-glpk@...
http://lists.gnu.org/mailman/listinfo/help-glpk

[xypron]

GLPK does not support strings exceeding 100 characters. This is too short
for many SQL statements.

I have made the necessary changes to support table instructions with SQL
statements spread over multiple strings.

The following syntax is used:

"The second argument and all following are considered to be SQL
statements

SQL statements may be spread over multiple arguments.  If the last
character of an argument is a semicolon this indicates the end of
a SQL statement.

The arguments of a SQL statement are concatenated separated by space.
The eventual trailing semicolon will be removed.

All but the last SQL statement will be executed directly.

For IN-table the last SQL statement can be a SELECT command starting with
the capitalized letters 'SELECT '. If the string does not start
with 'SELECT ' it is considered to be a table name and a SELECT
statement is automatically generated.

For OUT-table the last SQL statement can contain one or multiple question
marks. If it contains a question mark it is considered a template for
the write routine. Otherwise the string is considered a table name and
an INSERT template is automatically generated."

I have chosen semicolon as separator because semicolon is a standard
way to end an SQL statement (and to separate different SQL statements)
in systems that allow more than one SQL statement to be executed in the
same call to a database server.

Semicolon is only considered a separator if it is the last character of an
argument to avoid parsing strings passed in SQL statements.

This patch will require changes to existing models using table instructions
with multiple SQL statements.

The changed files are appended.

The coding change can be viewed at
http://glpk.dyndns.org/viewvc/svn/glpk/glpk/branches/glpk-4.40-sql/src/glpsql.c?view=diff&r1=506&r2=504&diff_format=h

Best regards

Xypron

Andrew Makhorin wrote:

Hi Xypron,

Is it possible to make changes in the sql table driver in order to
concatenate some arguments (marked in some way) to construct the
resulting sql statement? This would allow processing sql statements
which are longer than 100 chars without changes in the language and
in the translator. Thanks.


Andrew Makhorin

  

_______________________________________________
Help-glpk mailing list
Help-glpk@...
http://lists.gnu.org/mailman/listinfo/help-glpk

[Andrew Makhorin]

Hi Xypron,

> GLPK does not support strings exceeding 100 characters. This is too
> short for many SQL statements.

> I have made the necessary changes to support table instructions with
> SQL statements spread over multiple strings.

Thank you very much.

I included your changes in the glpk codebase, so they will appear in
the next release.


Andrew Makhorin



_______________________________________________
Help-glpk mailing list
Help-glpk@...
http://lists.gnu.org/mailman/listinfo/help-glpk