PDA

Vollständige Version anzeigen : Funktionen für dynamisches SQL


TommyK
29.09.2004, 14:43
Hallo,

Frank Wilde hat uns freundlicher Weise einige Funktionen bereitgestellt die Fehler
beim dynamischen Absetzen von Sql-Befehlen vermeiden sollen, sei es aufgrund der Sprachversion oder aufgrund von Hochkommata in einem String.

Hier die Funktionen (In der Anlage ist eine bas-Datei mit dem gesamten Code enthalten)
<div><link href="http://www.ms-office-forum.net/forum/externals/codeconv.css" rel="stylesheet"><pre><span class="TOKEN">Public Function</span> SqlBool(<span class="TOKEN">ByVal</span> varBool <span class="TOKEN">As</span> Variant) <span class="TOKEN">As</span> Variant
<span class="TOKEN">If</span> IsBlank(varBool) <span class="TOKEN">Then</span>
SqlBool = &quot;NULL&quot;
<span class="TOKEN">ElseIf</span> varBool <span class="TOKEN">Then</span>
SqlBool = -1
<span class="TOKEN">Else</span>
SqlBool = 0
<span class="TOKEN">End</span> <span class="TOKEN">If</span>
<span class="TOKEN">End</span> <span class="TOKEN">Function</span>
&nbsp;
<span class="TOKEN">Public Function</span> SqlDate(<span class="TOKEN">ByVal</span> varDate <span class="TOKEN">As</span> Variant) <span class="TOKEN">As</span> Variant
<span class="TOKEN">If</span> IsBlank(varDate) <span class="TOKEN">Then</span>
varDate = &quot;NULL&quot;
<span class="TOKEN">ElseIf</span> IsNumeric(varDate) <span class="TOKEN">Or</span> IsDate(varDate) <span class="TOKEN">Then</span>
varDate = <span class="TOKEN">CDate</span>(varDate)
<span class="TOKEN">If</span> Abs(varDate) &lt; 1 <span class="TOKEN">Then</span>
varDate = &quot;#&quot; &amp; Hour(varDate) &amp; &quot;:&quot; &amp; Minute(varDate) &amp; &quot;:&quot; &amp; Second(varDate) &amp; &quot;#&quot;
<span class="TOKEN">ElseIf</span> Frac(varDate) = 0 <span class="TOKEN">Then</span>
varDate = &quot;#&quot; &amp; Month(varDate) &amp; &quot;/&quot; &amp; Day(varDate) &amp; &quot;/&quot; &amp; Year(varDate) &amp; &quot;#&quot;
<span class="TOKEN">Else</span>
varDate = &quot;#&quot; &amp; Month(varDate) &amp; &quot;/&quot; &amp; Day(varDate) &amp; &quot;/&quot; &amp; Year(varDate) &amp; &quot; &quot; &amp; Hour(varDate) &amp; &quot;:&quot; &amp; Minute(varDate) &amp; &quot;:&quot; &amp; Second(varDate) &amp; &quot;#&quot;
<span class="TOKEN">End</span> <span class="TOKEN">If</span>
<span class="TOKEN">End</span> <span class="TOKEN">If</span>
SqlDate = varDate
<span class="TOKEN">End</span> <span class="TOKEN">Function</span>
&nbsp;
&nbsp;
<span class="TOKEN">Public Function</span> SqlNumber(<span class="TOKEN">ByVal</span> varNumber <span class="TOKEN">As</span> Variant) <span class="TOKEN">As</span> Variant
<span class="TOKEN">If</span> IsDate(varNumber) <span class="TOKEN">Then</span> varNumber = <span class="TOKEN">CDbl</span>(varNumber)
<span class="TOKEN">If</span> IsBlank(varNumber) <span class="TOKEN">Then</span>
varNumber = &quot;NULL&quot;
<span class="TOKEN">ElseIf</span> IsNumeric(varNumber) <span class="TOKEN">Then</span>
varNumber = RplDecSep(varNumber)
<span class="TOKEN">End</span> <span class="TOKEN">If</span>
SqlNumber = varNumber
<span class="TOKEN">End</span> <span class="TOKEN">Function</span>
&nbsp;
<span class="TOKEN">Public Function</span> SqlText(<span class="TOKEN">ByVal</span> varText <span class="TOKEN">As</span> Variant) <span class="TOKEN">As</span> Variant
<span class="TOKEN">If</span> IsBlank(varText) <span class="TOKEN">Then</span>
SqlText = &quot;NULL&quot;
<span class="TOKEN">Else</span>
SqlText = Chr$(34) &amp; DoubleChr34(Trim(varText)) &amp; Chr$(34)
<span class="TOKEN">End</span> <span class="TOKEN">If</span>
<span class="TOKEN">End</span> <span class="TOKEN">Function</span>
&nbsp;
<span class="REM">'Hierf&uuml;r werden folgende Hilfsfunktionen ben&ouml;tigt:</span>
<span class="TOKEN">Private Function</span> DoubleChr34(<span class="TOKEN">ByVal</span> varVar <span class="TOKEN">As</span> Variant) <span class="TOKEN">As</span> Variant
<span class="TOKEN">Dim</span> lngPos <span class="TOKEN">As</span> <span class="TOKEN">Long</span>
&nbsp;
<span class="TOKEN">If</span> <span class="TOKEN">Not</span> IsNull(varVar) <span class="TOKEN">Then</span>
lngPos = InStr(varVar, Chr$(34))
<span class="TOKEN">Do</span> <span class="TOKEN">While</span> lngPos
varVar = Left(varVar, lngPos) &amp; Chr$(34) &amp; Mid(varVar, lngPos + 1)
lngPos = InStr(lngPos + 2, varVar, Chr$(34))
<span class="TOKEN">Loop</span>
<span class="TOKEN">End</span> <span class="TOKEN">If</span>
DoubleChr34 = varVar
<span class="TOKEN">End</span> <span class="TOKEN">Function</span>
&nbsp;
<span class="TOKEN">Private Function</span> Frac(<span class="TOKEN">ByVal</span> varVal <span class="TOKEN">As</span> Variant) <span class="TOKEN">As</span> <span class="TOKEN">Double</span>
Frac = varVal - Fix(varVal)
<span class="TOKEN">End</span> <span class="TOKEN">Function</span>
&nbsp;
<span class="TOKEN">Private Function</span> IsBlank(<span class="TOKEN">ByVal</span> varVar <span class="TOKEN">As</span> Variant) <span class="TOKEN">As</span> <span class="TOKEN">Boolean</span>
<span class="TOKEN">Dim</span> lngPos <span class="TOKEN">As</span> Long, lngVar <span class="TOKEN">As</span> <span class="TOKEN">Long</span>
<span class="TOKEN">Dim</span> strWhiteSpaces <span class="TOKEN">As</span> <span class="TOKEN">String</span>
&nbsp;
<span class="TOKEN">If</span> IsMissing(varVar) <span class="TOKEN">Then</span> <span class="REM">' Bug: tritt auf, wenn ISBLANK als Makro benutzt wird ?!?</span>
IsBlank = <span class="TOKEN">True</span>
<span class="TOKEN">ElseIf</span> IsEmpty(varVar) <span class="TOKEN">Then</span>
IsBlank = <span class="TOKEN">True</span>
<span class="TOKEN">ElseIf</span> IsNull(varVar) <span class="TOKEN">Then</span>
IsBlank = <span class="TOKEN">True</span>
<span class="TOKEN">Else</span>
<span class="REM"> 'IsBlank = (Trim(varVar) = &quot;&quot;) '</span>
strWhiteSpaces = Chr$(0) &amp; Chr$(9) &amp; Chr$(10) &amp; Chr$(13) &amp; Chr$(26) &amp; Chr$(32) &amp; Chr$(160)
<span class="TOKEN">For</span> lngVar = 1 <span class="TOKEN">To</span> Len(strWhiteSpaces)
lngPos = InStr(varVar, Mid$(strWhiteSpaces, lngVar, 1))
<span class="TOKEN">Do</span> <span class="TOKEN">While</span> lngPos
varVar = Left(varVar, lngPos - 1) &amp; Mid(varVar, lngPos + 1)
lngPos = InStr(varVar, Mid$(strWhiteSpaces, lngVar, 1))
<span class="TOKEN">Loop</span>
<span class="TOKEN">Next</span> lngVar
IsBlank = (varVar = &quot;&quot;)
<span class="TOKEN">End</span> <span class="TOKEN">If</span>
<span class="TOKEN">End</span> <span class="TOKEN">Function</span>
&nbsp;
<span class="TOKEN">Private Function</span> RplDecSep(<span class="TOKEN">ByVal</span> varVar <span class="TOKEN">As</span> Variant) <span class="TOKEN">As</span> Variant
<span class="TOKEN">Dim</span> varPos <span class="TOKEN">As</span> Variant
&nbsp;
<span class="TOKEN">If</span> IsNull(varVar) <span class="TOKEN">Then</span> <span class="TOKEN">Exit Function</span>
varPos = InStr(varVar, &quot;,&quot;)
<span class="TOKEN">If</span> <span class="TOKEN">Not</span> IsNull(varPos) <span class="TOKEN">And</span> varPos <span class="TOKEN">Then</span> varVar = Left(varVar, varPos - 1) &amp; &quot;.&quot; &amp; Mid(varVar, varPos + 1)
RplDecSep = varVar
<span class="TOKEN">End</span> <span class="TOKEN">Function</span></pre></div>
Code eingefügt mit dem MOF Code Converter (http://www.ms-office-forum.net/forum/codeconverter.php)

Der Aufruf sähe z.B. so aus:
Statt also ".... SET datenfeld = " & variable sollte nun ".... SET datenfeld = " & Sql...variablentyp(variable) für ein fehlerfreies Arbeiten mit dynamischen Sql-Befehlen sorgen

Danke Frank

Hier noch eine Erweiterung:
Vielleicht ist in diesem Zusammenhang auch die Besonderheit der SQL-Operatoren NOT, AND und OR interessant, die im Gegensatz zu ihren VB-Pendants nicht die bitweise Verknüpfung zurückliefern, sondern immer nur 0 oder -1!
WIll man/frau also via SQL bitweise abfragen, so helfen folgende einfache Funktionen:
<div><link href="http://www.ms-office-forum.net/forum/externals/codeconv.css" rel="stylesheet"><pre><span class="TOKEN">Public Function</span> SqlNot(<span class="TOKEN">ByVal</span> varVar <span class="TOKEN">As</span> Variant) <span class="TOKEN">As</span> Variant
SqlNot = <span class="TOKEN">Not</span> varVar
<span class="TOKEN">End</span> <span class="TOKEN">Function</span>
&nbsp;
<span class="TOKEN">Public Function</span> SqlAnd(<span class="TOKEN">ByVal</span> varVar1 <span class="TOKEN">As</span> Variant, <span class="TOKEN">ByVal</span> varVar2 <span class="TOKEN">As</span> Variant) <span class="TOKEN">As</span> Variant
SqlAnd = varVar1 <span class="TOKEN">And</span> varVar2
<span class="TOKEN">End</span> <span class="TOKEN">Function</span>
&nbsp;
<span class="TOKEN">Public Function</span> SqlOr(<span class="TOKEN">ByVal</span> varVar1 <span class="TOKEN">As</span> Variant, <span class="TOKEN">ByVal</span> varVar2 <span class="TOKEN">As</span> Variant) <span class="TOKEN">As</span> Variant
SqlOr = varVar1 <span class="TOKEN">Or</span> varVar2
<span class="TOKEN">End</span> <span class="TOKEN">Function</span></pre></div>
Code eingefügt mit dem MOF Code Converter (http://www.ms-office-forum.net/forum/codeconverter.php)